How to Create a Postgres Function Returning a Virtual Table with Multiple Rows?

Returning a Virtual Table from a Postgres Function

Problem

You seek a Postgres function that returns a virtual table with custom content, boasting an unknown number of rows and three columns. You encounter difficulties locating the appropriate syntax and require assistance in crafting the function.

Solution

Utilize the following syntax to construct the desired function:

CREATE OR REPLACE FUNCTION f_foo() -- (open_id numeric) -- parameter not used
  RETURNS TABLE (a int, b int, c int) AS
$func$
BEGIN
RETURN QUERY VALUES
  (1,2,3)
, (3,4,5)
, (3,4,5)
;
END
$func$  LANGUAGE plpgsql IMMUTABLE ROWS 3;

Explanation

Key Points:

• RETURNS TABLE: Defines the return type as an ad-hoc row type.
• RETURN QUERY: Returns multiple rows in a single command.
• VALUES: Enters multiple rows manually using a standard SQL expression.
• PARAMETERS: An unused parameter (open_id numeric) is included as an example, but is optional.
• IDENTIFIERS: Double-quoting is not necessary for legal identifiers.
• VOLATILITY: IMMUTABLE indicates that the result is constant.
• ROWS: Declaring the number of returned rows (ROWS 3) can aid the query planner.

Alternatives:

• Simple SQL: For straightforward cases, the following SQL statement suffices:

VALUES (1,2,3), (3,4,5), (3,4,5)

• SQL Function: Wrap the statement in an SQL function:

CREATE OR REPLACE FUNCTION f_foo()
   RETURNS TABLE (a int, b int, c int) AS
$func$
   VALUES (1, 2, 3)
        , (3, 4, 5)
        , (3, 4, 5);
$func$  LANGUAGE sql IMMUTABLE ROWS 3;

The above is the detailed content of How to Create a Postgres Function Returning a Virtual Table with Multiple Rows?. For more information, please follow other related articles on the PHP Chinese website!