How to Return a Virtual Table from a Postgres Function?

Returning Virtual Table from Function in Postgres

In Postgres, creating a custom virtual table with multiple rows and columns using a function requires a specific syntax. This article demonstrates how to correctly write a function that returns a setof record resembling a virtual table.

Correct Syntax

To define a function that returns a virtual table with three integer columns, use the following PL/pgSQL code:

CREATE OR REPLACE FUNCTION f_foo()
  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

• RETURNS TABLE: Indicates that the function will return an ad-hoc row type.
• RETURN QUERY VALUES: Used to return multiple rows using VALUES expression.
• ROWS 3: Declares the number of rows returned, assisting the query planner in optimizing execution.
• IMMUTABLE: Specifies that the result never changes, allowing for performance optimization.

Alternative Options

Simple SQL: For simple scenarios, a plain SQL statement can suffice:

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

SQL Function: You can also wrap the above 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;

Usage

To retrieve the virtual table:

SELECT * FROM f_foo();

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