PostgreSQL：无需refcursor返回多表及函数返回双异构结果集咨询

Hey there! Let's break down how to recreate your SQL Server stored procedure behavior in PostgreSQL, returning two separate result sets without relying on refcursors.

The Direct Equivalent Function #

PostgreSQL makes this straightforward—you can simply execute multiple SELECT statements directly in a PL/pgSQL function, and each will be returned as an independent result set to the client. Here's the code:

CREATE OR REPLACE FUNCTION sp_fetchresult()
RETURNS void AS $$
BEGIN
    -- Return first result set from tbl_users
    SELECT * FROM tbl_users;
    -- Return second result set from tbl_log
    SELECT * FROM tbl_log;
END;
$$ LANGUAGE plpgsql;

How to Use It #

To retrieve the two result sets, just call the function like this:

SELECT sp_fetchresult();

Your PostgreSQL client (like psql, pgAdmin, or application drivers) will receive two distinct result sets—one for tbl_users and one for tbl_log, matching the behavior of your original SQL Server stored procedure.

Why This Works #

In PostgreSQL's PL/pgSQL, any SELECT statement that isn't captured with an INTO clause (to store results in variables) will automatically send its output to the client. Executing multiple SELECTs in sequence generates multiple separate result sets, no refcursor required.

Quick Notes for Edge Cases #

• If you need to enforce explicit result set structures for stricter client compatibility, you could define the function to return SETOF record tailored to each table, but the RETURNS void approach is simpler for your scenario since the tables have different structures.
• Ensure your client driver supports multiple result sets (most modern PostgreSQL drivers do, like psycopg2 for Python, Npgsql for .NET, etc.).

内容的提问来源于stack exchange，提问作者souvik sardar