Returning Result Sets in PostgreSQL

Unlike databases such as Microsoft SQL Server or Sybase Adaptive Server Enterprise, PostgreSQL does not allow procedures to directly return result sets in the same way as a plain SELECT statement.

However, PostgreSQL provides several mechanisms to return sets of rows.

SQLWays currently supports the following approaches:

• RETURNS TABLE (default option)
• RETURNS SETOF
• INOUT REFCURSOR

The last two options require additional configuration of our tool.
More details about this configuration can be found in the article: Configuring Result Sets Conversion option for PostgreSQL

Lets focus on the mechanisms used to return result sets in PostgreSQL.
For detailed examples of how to retrieve data from such functions and procedures, see Retrieving Data from PostgreSQL Functions and Procedures.

CREATE OR REPLACE FUNCTION get_empl_by_id(v_EmpId INTEGER)
RETURNS TABLE
(
   emp_id VARCHAR,
   first_name VARCHAR,
   last_name VARCHAR,
   salary VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
   RETURN QUERY
   SELECT
        empl.emp_id,
        empl.first_name,
        empl.last_name,
        empl.salary
   FROM empl
   WHERE empl.emp_id = v_EmpId;
END;
$$;

This option is convenient when a function should return a result similar to a regular SELECT query.

Advantages:

• provides good code readability
• returns a set of rows that behaves like a regular table
• the function can be used directly in:
  

1. SELECT
2. CTE (WITH)
3. subqueries
4. other functions

• there is no need to declare separate OUT parameters, since the output columns are defined directly in the RETURNS TABLE clause

Functions defined this way integrate naturally with SQL queries.
Examples of how to call such functions and retrieve their results are described in Retrieving Data from PostgreSQL Functions and Procedures.

CREATE TYPE employee_info AS (
   emp_id INT,
   name TEXT,
   salary NUMERIC
);

CREATE FUNCTION get_high_sal (min_salary NUMERIC)
RETURNS SETOF employee_info
LANGUAGE plpgsql
AS $$
BEGIN
   RETURN QUERY
   SELECT
        id,
        name,
        salary
   FROM empl
   WHERE salary >= min_salary;
END;
$$;

In our tool, this approach is implemented using a composite type, which is created before the function itself.

Such functions return a set of rows and can also be used directly in:

• SELECT
• CTE (WITH)
• subqueries
• other functions

However, this approach is generally less readable and requires first creating a composite type.

Examples of how to query functions that return SETOF values are provided in Retrieving Data from PostgreSQL Functions and Procedures.

Limitations of RETURNS TABLE and RETURNS SETOF

Both approaches share several limitations:

• the structure of the returned result set must be predefined, so dynamically changing the set of returned columns is not supported
• the entire result set is generated and returned at once
• for large result sets, this may negatively affect performance

Note:

It is not allowed to use explicit OUT or INOUT parameters together with the RETURNS TABLE notation. All output columns must be defined inside the TABLE clause.

If there is a need to return multiple result sets or to retrieve results incrementally, REFCURSOR parameters can be used.

Our conversion tool supports converting such logic into a procedure that uses REFCURSOR parameters as INOUT arguments.

CREATE OR REPLACE PROCEDURE fromora.get_empl_dept(
    v_EmpId INTEGER,
    INOUT v_MinSalary DECIMAL(10,2),
    INOUT SWV_RefCur refcursor DEFAULT NULL,
    INOUT SWV_RefCur2 refcursor DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- get information about a specific employee
    OPEN SWV_RefCur FOR
    SELECT emp_id, name, salary
    FROM fromora.empl
    WHERE emp_id = v_EmpId;

    -- fill the output parameter
    SELECT salary INTO v_MinSalary
    FROM empl
    WHERE emp_id = v_EmpId
    UNION ALL
    SELECT v_MinSalary;

    -- get the list of departments
    OPEN SWV_RefCur2 FOR
    SELECT dept_id, location, region
    FROM fromora.dept;
END;
$$;

Advantages of using REFCURSOR:

• multiple cursors can be opened within a single procedure
• allows returning multiple independent result sets
• the structure of the result set can be determined dynamically
• supports incremental result retrieval using FETCH

Limitations:

• more complex syntax
• cursors cannot be used directly in SQL queries
• multiple large cursors may consume significant memory
• row-by-row processing (FETCH) is usually slower than a set-based SELECT

A REFCURSOR is a reference to an open cursor that points to a query result stored on the server. To access the data, it must first be explicitly retrieved using FETCH.

Detailed examples of how to call such procedures and retrieve the data from returned cursors are provided in Retrieving Data from PostgreSQL Functions and Procedures.

If you have any questions or face any difficulties, please contact our support team at support@ispirer.com