In traditional SQL-based systems like MySQL or SQL Server, stored procedures can return multiple table views in a single call. However, PostgreSQL does not support returning multiple result sets directly in the same way.

Instead, PostgreSQL provides an alternative approach using refcursors. Refcursors allow stored procedures to return multiple query results efficiently.

Understanding Refcursors in PostgreSQL

A refcursor is a cursor variable that points to a result set. Unlike standard cursors, refcursors can be returned from functions or procedures, making them ideal for handling multiple result sets.

Advantages of Refcursors

• Allows stored procedures to return multiple table views.

• Efficiently handles large result sets.

• Provides flexibility in retrieving and processing data.

Example: Using Refcursors to Return Multiple Table Sets

Let’s walk through an example where we create a stored procedure that returns multiple result sets using refcursors.

Step 1: Create Sample Tables

We will create two sample tables: solarsystem and galaxies.

CREATE TABLE solarsystem (
    planet_id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE galaxies (
    galaxy_id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

INSERT INTO solarsystem (name) 
VALUES ('Mercury'), ('Venus'), ('Earth'), ('Mars');

INSERT INTO galaxies (name) 
VALUES ('Milky Way'), ('Andromeda'),('Sombrero');

Step 2: Create a Stored Procedure with Refcursors

We will now create a stored procedure that returns both solarsystem and galaxies data using refcursors.

CREATE OR REPLACE PROCEDURE fetch_multiple_views(
    OUT solar_cursor REFCURSOR,
    OUT galaxy_cursor REFCURSOR
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Open the first cursor for the solarsystem table
    OPEN solar_cursor FOR SELECT * FROM solarsystem;

    -- Open the second cursor for the galaxies table
    OPEN galaxy_cursor FOR SELECT * FROM galaxies;
END;
$$;

Step 3: Call the Procedure and Fetch Data

Since PostgreSQL does not allow procedures to return result sets directly, we use CALL along with explicit refcursor handling.

BEGIN;
    -- Declare cursors
    CALL fetch_multiple_views('solar_cursor', 'galaxy_cursor');

    -- Fetch data from the first cursor
    FETCH ALL FROM solar_cursor;

    -- Fetch data from the second cursor
    FETCH ALL FROM galaxy_cursor;

    -- Close the cursors
    CLOSE solar_cursor;
    CLOSE galaxy_cursor;

COMMIT;

Explanation:

1. We begin a transaction (BEGIN;) since refcursors exist only within a transaction block.

2. The stored procedure fetch_multiple_views is called, passing the cursor names.

3. We use FETCH ALL FROM to retrieve data from each cursor.

4. Finally, we close the cursors and commit the transaction.

Conclusion

While PostgreSQL does not support returning multiple result sets directly from stored procedures like some other SQL databases, refcursors provide a powerful and flexible alternative. By using refcursors, we can:

• Open multiple result sets inside a procedure.

• Fetch and process them individually.

• Efficiently manage large query results within transactions.