On Wed, Apr 10, 2024 at 4:22 PM Jan Behrens <jbe-mlist@xxxxxxxxxxxxx> 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
