From: | Patrick FICHE <Patrick(dot)Fiche(at)aqsacom(dot)com> |
---|---|
To: | Jan Behrens <jbe-mlist(at)magnetkern(dot)de>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | RE: (When) can a single SQL statement return multiple result sets? |
Date: | 2024-04-11 07:41:56 |
Message-ID: | DUZPR05MB1102143F9F232C1B3B668F690EF052@DUZPR05MB11021.eurprd05.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
-----Original Message-----
From: Jan Behrens <jbe-mlist(at)magnetkern(dot)de>
Sent: Wednesday, April 10, 2024 11:23 PM
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: (When) can a single SQL statement return multiple result sets?
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.
(Note that this isn't possible with rules ON SELECT because it is only allowed to create a single SELECT rule on a view.)
The case outlined above seems to be a somewhat special case. I haven't found any other way to return multiple results (other than sending several semicolon-separated statements, which is not supported by PQsendQueryParams). So is there any (other) case where I reasonably should expect several result sets returned by PQgetResult (before PQgetResult returns NULL)? Wouldn't it make sense to disallow such behavior altogether? And if not, why can't I write a stored procedure or function that returns multiple result sets?
These questions are relevant to me because it may have an effect on the API design if a statement can return several result sets.
Kind regards,
Jan Behrens
-----Original Message-----
Hi, you can declare a function which returns multiple CURSORS...
RETURNS SETOF REFCURSOR
Then, in your function, you have to write something like this
DECLARE
rc_1 refcursor;
rc_2 refcursor;
rc_3 refcursor;
...
OPEN rc_1 FOR SELECT ...
OPEN rc_2 FOR SELECT ...
OPEN rc_3 FOR SELECT ...
RETURN NEXT rc_1;
RETURN NEXT rc_2;
RETURN NEXT rc_3;
Regards,
From | Date | Subject | |
---|---|---|---|
Next Message | ilya Basin | 2024-04-11 12:57:34 | subquery plan rows = 1, but it's merge joined instead of index lookup |
Previous Message | Andreas Wagner | 2024-04-11 07:07:08 | Re: Two server instances on one server |