How Can I Create a Postgres Function That Returns a Virtual Table?

Returning Virtual Tables from Functions in Postgres

One can create a Postgres function that returns a virtual table with a custom schema. This type of table structure consists of a distinct number of columns and rows.

PL/pgSQL Function Syntax

For modern PL/pgSQL versions (PostgreSQL 8.4 and above), here's the syntax:

CREATE OR REPLACE FUNCTION function_name()
  RETURNS TABLE (column1 data_type, column2 data_type, column3 data_type) AS
$func$
BEGIN
  RETURN QUERY VALUES
  (value1, value2, value3),
  (value4, value5, value6);

END;
$func$  LANGUAGE plpgsql IMMUTABLE ROWS number_of_rows;

Important Points:

• Use RETURNS TABLE to specify the custom row type.
• Use RETURN QUERY VALUES to populate the table.
• Use VALUES to insert multiple rows manually.
• Adjust number_of_rows according to the known table size.
• Remove double quotes from legal identifiers, except for special cases.
• Set volatility to IMMUTABLE if the result never changes.

SQL Function Syntax

For simple cases, use this SQL function:

CREATE OR REPLACE FUNCTION function_name()
RETURNS TABLE (column1 data_type, column2 data_type, column3 data_type) AS
$func$
SELECT *
FROM (
  VALUES
    (value1, value2, value3),
    (value4, value5, value6)
) AS table_name(column1, column2, column3);

$func$  LANGUAGE sql IMMUTABLE ROWS number_of_rows;

The above is the detailed content of How Can I Create a Postgres Function That Returns a Virtual Table?. For more information, please follow other related articles on the PHP Chinese website!