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)
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?
