Returning multiple result sets from postgres: refcursors, functions or new features?

Premise:

An entity has many children and grandchildren (gists, versions and files respectively, in this instance). When it (gist) is requested, we want its children (versions) and grandchildren (files for versions) as well. Here I am really pushing to do as much in SQL as possible, instead of composing several queries together and munging them together in a scripting language. Can it be done? Does the tooling that supports sending queries to a PostgresQL db support the results returned?

Prior art:

Schema:

Schema (and SQL to insert mock data)

My attempt via stored function (Note, I have simplified to just parent/child return sets, skipping grandchild for now):

CREATE OR REPLACE FUNCTION get_gist_with_children(gist_ref refcursor, 

version_ref refcursor, gist_id_in UUID)
RETURNS SETOF refcursor AS $$
BEGIN
 OPEN gist_ref FOR SELECT * from gist where gist_id = gist_id_in;
 RETURN NEXT gist_ref;

 OPEN version_ref FOR SELECT * from version where gist_id = gist_id_in;
 RETURN NEXT version_ref;
END;
$$ LANGUAGE plpgsql;

​x
 
CREATE OR REPLACE FUNCTION get_gist_with_children(gist_ref refcursor, ​version_ref refcursor, gist_id_in UUID)RETURNS SETOF refcursor AS $$BEGIN OPEN gist_ref FOR SELECT * from gist where gist_id = gist_id_in; RETURN NEXT gist_ref;​ OPEN version_ref FOR SELECT * from version where gist_id = gist_id_in; RETURN NEXT version_ref;END;$$ LANGUAGE plpgsql;​

The query to access that function:

BEGIN;

SELECT get_gist_with_children('gist_ref', 'version_ref', <replace with known UUID of gist record>);

FETCH ALL IN "gist_ref";
FETCH ALL IN "version_ref";
COMMIT;

 
BEGIN;​SELECT get_gist_with_children('gist_ref', 'version_ref', <replace with known UUID of gist record>);​FETCH ALL IN "gist_ref";FETCH ALL IN "version_ref";COMMIT;​

returns in POSTICO Postico, a PostgresQL GUI, just returns the refcursor symbol strings in two rows.

returns in psql psql returns the first set of results and then… hangs?

note: I can successfully query both tables on that gist_id and records returned in separate queries.

Are there new features of Postgres that could achieve this outcome? Is there a better way to use cursors to do so? Is the real problem that the tooling (psql, Postico) does not handle the returning of multiple data sets? Curious about ways forward beyond just falling back on composition via scripting languages.

Answer

The PostgreSQL database engine is perfectly capable of handling multiple open result sets per connections, even per call. It can keep multiple cursors that are returned from a single call using multiple “result set”s.

Don’t be afraid of using them. Just try multiple SELECTs inside your procedure and perform a SINGLE call. Then, it’s easy to verify you can get read from all the result sets, even in parallel.

If you asked this same question on MySQL, then answer would categorically a “No”. MySQL is far more limited. But with PostgreSQL this is perfectly possible.

Advertisement

Answer