Re: (When) can a single SQL statement return multiple result sets?

On Wed, Apr 10, 2024 at 4:22 PM Jan Behrens <jbe-mlist(at)magnetkern(dot)de> wrote:

> Hello,
>
> While writing a PostgreSQL client library for Lua supporting
> Pipelining (using PQsendQueryParams), I have been wondering if there
> are any single SQL commands that return multiple result sets. It is
> indeed possible to create such a case by using the RULE system:
>
> db=> CREATE VIEW magic AS SELECT;
> CREATE VIEW
> db=> CREATE RULE r1 AS ON DELETE TO magic
> db-> DO INSTEAD SELECT 42 AS "answer";
> CREATE RULE
> db=> CREATE RULE r2 AS ON DELETE TO magic
> db-> DO ALSO SELECT 'Hello' AS "col1", 'World!' AS "col2";
> CREATE RULE
> db=> DELETE FROM magic; -- single SQL statement!
> answer
> --------
> 42
> (1 row)
>
> col1 | col2
> -------+--------
> Hello | World!
> (1 row)
>
> DELETE 0
>
> Here, "DELETE FROM magic" returns multiple result sets, even though it
> is only a single SQL statement.
>

I guess you should have named your table, "sorcery", because that's
what this is. In the corporate world, we might regard the 'CREATE RULE'
feature as a 'solution opportunity' :-). You might be able to overlook
this on your end IMO as the view triggers feature has standardized and
fixed the feature.

> why can't I write a stored procedure or function that returns multiple
result sets?

Functions arguably should not be able to do this, doesn't the standard
allow for procedures (top level statements invoked with CALL) to return
multiple results?

merlin