Retrieving Data from PostgreSQL Functions and Procedures

Different mechanisms for returning result sets in PostgreSQL require different approaches for retrieving the returned data.

In the article Returning Result Sets in PostgreSQL, some mechanisms that PostgreSQL provides for returning result sets are described:

• RETURNS TABLE
• RETURNS SETOF
• INOUT REFCURSOR

Let's focus on how to retrieve data from functions and procedures that use these mechanisms.

Functions defined with RETURNS TABLE can be queried directly using SQL.

The definition and characteristics of such functions are described in Returning Result Sets in PostgreSQL.

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

SELECT *
FROM get_empl_by_id(10);

Since the function returns a row set, it can be used in many SQL constructs.

WITH emp_data AS (
SELECT *
FROM get_empl_by_id(10)
)
SELECT *
FROM emp_data;

SELECT e.*, d.department_name
FROM get_empl_by_id(10) e
JOIN departments d
ON e.emp_id = d.manager_id;

This works because the function behaves like a table expression.

Functions returning SETOF also produce a set of rows, but the structure is defined by a composite type.

More details about this approach and when it is used are described in Returning Result Sets in PostgreSQL,

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

Data can be retrieved in the same way as with RETURNS TABLE.

SELECT *
FROM get_high_sal(5000)

SELECT e.name, d.department_name
FROM get_high_sal(5000) e
JOIN departments d
ON e.id = d.manager_id;

SELECT name
FROM get_high_sal(5000)
WHERE salary > 10000;

Procedures that return REFCURSOR parameters behave differently.

Instead of returning result sets directly, they return references to open cursors that must be explicitly read using FETCH.

The design and implementation of such procedures are explained in Returning Result Sets in PostgreSQL.

In this approach, the procedure is called from a PL/pgSQL block, and the returned cursors are stored in local variables.

DO $$
DECLARE
   v_cur1 refcursor;
   v_cur2 refcursor;
   v_rec record;
   v_sal DECIMAL(10,2);
BEGIN
   CALL fromora.get_empl_dept(1, v_sal, v_cur1, v_cur2);

   RAISE NOTICE 'Salary: %', v_sal;

   LOOP
      FETCH v_cur1 INTO v_rec;
      EXIT WHEN NOT FOUND;
      RAISE NOTICE 'Employee: %', v_rec;
   END LOOP;

   LOOP
      FETCH v_cur2 INTO v_rec;
      EXIT WHEN NOT FOUND;
      RAISE NOTICE 'Departments: %', v_rec;
   END LOOP;
END;
$$;

As you can see, the data from the result sets is being processed row-by-row.

Another option is to pass cursor names as literals when calling the procedure.

CALL fromora.get_empl_dept (1, NULL, 'rf1'::refcursor, 'rf2'::refcursor);

FETCH ALL FROM rf1;

--FETCH ALL FROM rf2;

In this case:

• The procedure opens cursors with the specified names.
• The caller retrieves the results using FETCH.

This method is commonly used in database clients or scripts.

Retrieving data depends on the mechanism used to return the result set.

Functions returning RETURNS TABLE and RETURNS SETOF integrate directly with SQL queries, while REFCURSOR requires a procedural approach to retrieve the data.

For details on how these mechanisms are implemented and when to use them, see Returning Result Sets in PostgreSQL.

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