Multiple result sets alternatives in Postgres

While Postgres’s type system is second to none, Postgres still lacks some features here and there.
Stored procedures with their own transaction management are high on that list.
But, beside transaction management, SP usually come with a cool feature which can cut down chatting with the database server to the minimum.
Those familiar with MS SQL server have seen multiple selects coming from SPs:

CREATE PROCEDURE GetInvoiceAndDetails @id INT
AS
SELECT * FROM Invoice WHERE ID = @id
SELECT * FROM LineItem WHERE InvoiceID = @id

with which you can collect the whole Invoice aggregate at once.
Postgres has a better way of solving this particular example, but what about when you want to select two unrelated aggregates in a single query. For example, if your web page has a dozen of queries, you can combine them in a single SP and fetch all data with a single call to the database.
In Postgres you can use refcursors to implement such a feature:

CREATE FUNCTION load_page(_session INT) RETURNS setof refcursor AS
$$
DECLARE c_top_items refcursor;
DECLARE c_shopping_cart refcursor;
BEGIN
    OPEN c_top_items FOR
        SELECT t.name, t.description
        FROM top_item t
        ORDER BY t.popularity DESC
        LIMIT 10;
    RETURN NEXT c_top_items;
    OPEN c_shopping_cart FOR
        SELECT c.product_id, c.product_name, c.quantity
        FROM shopping_cart c
        WHERE c.session_id = _session
        ORDER BY c.id;
    RETURN NEXT c_shopping_cart;
END;
$$ LANGUAGE plpgsql;

Then you can call it with something like:

BEGIN;
SELECT load_page(mySession);
FETCH ALL IN "<server cursor 1>";
FETCH ALL IN "<server cursor 2>";
COMMIT;

Since this works in Hot Standby mode, an explicit transaction is not really an issue.

What are the alternatives in doing this kind of query and how far can we take it?

Utilizing Postgres type system and some boilerplate we can end up with something like this:

CREATE VIEW sorted_top_items AS
SELECT t.name, t.description
FROM top_item t
ORDER BY t.popularity DESC;
 
CREATE TYPE shopping_cart_session AS
(
    id INT,
    name VARCHAR,
    quantity NUMERIC
);
 
CREATE FUNCTION load_page_types(
    IN _session INT,
    OUT top_items sorted_top_items[],
    OUT cart_items shopping_cart_session[]
) AS $$
BEGIN
    SELECT array_agg(ti.*) INTO top_items
    FROM sorted_top_items ti
    LIMIT 10;
    SELECT array_agg(sq.*::shopping_cart_session) INTO cart_items
    FROM (SELECT c.product_id, c.product_name, c.quantity 
          FROM shopping_cart c
	  WHERE c.session_id = _session
	  ORDER BY c.id) sq;
END;
$$ LANGUAGE plpgsql;

Since Postgres supports arrays we can just shove results in array columns and have a more OOP-like result.

Let’s take it a step further and see if we can remove some of the boilerplate?

Maintaining this function becomes cumbersome if sorted_top_items view needs to be modified. Postgres dependency tracking will complain that load_page_types depends on sorted_top_items and needs to be dropped to alter the view.
While this is a good thing, if you don’t have a setup which will automate object rebuilds, it’s very annoying to do it by hand.
Let’s use less restrictive type, but maintain all the features of that function:

CREATE OR REPLACE FUNCTION load_page_record(IN _session INT) RETURNS RECORD AS
$$
DECLARE r1 RECORD;
DECLARE r2 RECORD;
DECLARE RESULT RECORD;
BEGIN
    SELECT array_agg(sq.*) AS arr INTO r1
    FROM (SELECT t.name, t.description 
          FROM top_item t
          ORDER BY t.popularity DESC 
          LIMIT 10) sq;
    SELECT array_agg(sq.*) AS arr INTO r2
    FROM (SELECT c.product_id, c.product_name, c.quantity 
          FROM shopping_cart c 
          WHERE c.session_id = _session 
          ORDER BY c.id) sq;
    --RETURN ROW(r1,r2) -- only in 9.3
    SELECT r1.arr, r2.arr INTO RESULT;
    RETURN RESULT;
END;
$$ LANGUAGE plpgsql;

All of this is nice, but what to do when we don’t want to use server side functions?

For example, how can we gather results for

SELECT t.name, t.description
FROM top_item t
ORDER BY t.popularity DESC
LIMIT 10;
SELECT c.product_id, c.product_name, c.quantity
FROM shopping_cart c
WHERE c.session_id = @sessionID
ORDER BY c.id

using single call?

By combining multiple selects into a single one:

SELECT
    (SELECT array_agg(sq.*)
     FROM (SELECT t.name, t.description
           FROM top_item t
           ORDER BY t.popularity DESC
           LIMIT 10) sq
    ) AS top_items,
    (SELECT array_agg(sq.*)
     FROM (SELECT c.product_id, c.product_name, c.quantity 
           FROM shopping_cart c
           WHERE c.session_id = @sessionID
           ORDER BY c.id) sq
     ) AS shopping_cart

One thought on “Multiple result sets alternatives in Postgres

  1. Andra

    thanks for this very good article. it helped me a lot to mange problems with multiple resultsets using jpa to access stored procedures. as i am not a sql-expert, the array function was new to me and calling your load_page_record-function worked well.

Leave a Reply

Your email address will not be published. Required fields are marked *