public inbox for [email protected]
help / color / mirror / Atom feed(When) can a single SQL statement return multiple result sets?
8+ messages / 6 participants
[nested] [flat]
* (When) can a single SQL statement return multiple result sets?
@ 2024-04-10 21:22 Jan Behrens <[email protected]>
2024-04-10 22:47 ` Re: (When) can a single SQL statement return multiple result sets? Merlin Moncure <[email protected]>
2024-04-10 23:02 ` Re: (When) can a single SQL statement return multiple result sets? Tom Lane <[email protected]>
2024-04-11 07:41 ` RE: (When) can a single SQL statement return multiple result sets? Patrick FICHE <[email protected]>
0 siblings, 3 replies; 8+ messages in thread
From: Jan Behrens @ 2024-04-10 21:22 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: (When) can a single SQL statement return multiple result sets?
2024-04-10 21:22 (When) can a single SQL statement return multiple result sets? Jan Behrens <[email protected]>
@ 2024-04-10 22:47 ` Merlin Moncure <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: Merlin Moncure @ 2024-04-10 22:47 UTC (permalink / raw)
To: Jan Behrens <[email protected]>; +Cc: [email protected]
On Wed, Apr 10, 2024 at 4:22 PM Jan Behrens <[email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: (When) can a single SQL statement return multiple result sets?
2024-04-10 21:22 (When) can a single SQL statement return multiple result sets? Jan Behrens <[email protected]>
@ 2024-04-10 23:02 ` Tom Lane <[email protected]>
2024-04-11 05:25 ` Re: (When) can a single SQL statement return multiple result sets? Thomas Kellerer <[email protected]>
2024-04-15 14:55 ` Re: (When) can a single SQL statement return multiple result sets? Peter Eisentraut <[email protected]>
2024-04-15 15:24 ` Re: (When) can a single SQL statement return multiple result sets? Jan Behrens <[email protected]>
2 siblings, 3 replies; 8+ messages in thread
From: Tom Lane @ 2024-04-10 23:02 UTC (permalink / raw)
To: Jan Behrens <[email protected]>; +Cc: [email protected]
Jan Behrens <[email protected]> writes:
> 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.
Right now, I don't think so. I believe the current protocol design
intends to support that, and I think this may trace back to some
ancient idea at Berkeley that if you select from an inheritance
hierarchy where the child tables aren't all alike, you should be
able to see all the child data, which'd require changing tuple
descriptors midstream. But our current interpretation of SQL
SELECT forbids that.
> Here, "DELETE FROM magic" returns multiple result sets, even though it
> is only a single SQL statement.
Right, so it's kind of a case that you have to support. We're not
likely to rip out rules anytime soon, even if they're a bit
deprecated.
> 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?
No. For one thing, there's too much overlap between what you're
suggesting and pipelined queries.
> And if not, why can't I write a stored procedure
> or function that returns multiple result sets?
[ shrug... ] Lack of round tuits, perhaps. We don't have any
mechanism today whereby a stored procedure could say "please ship
this resultset off to the client, but I want to continue afterwards".
But you can do that in other RDBMSes and probably somebody will be
motivated to make it possible in Postgres.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: (When) can a single SQL statement return multiple result sets?
2024-04-10 21:22 (When) can a single SQL statement return multiple result sets? Jan Behrens <[email protected]>
2024-04-10 23:02 ` Re: (When) can a single SQL statement return multiple result sets? Tom Lane <[email protected]>
@ 2024-04-11 05:25 ` Thomas Kellerer <[email protected]>
2024-04-11 14:07 ` Re: (When) can a single SQL statement return multiple result sets? Tom Lane <[email protected]>
2 siblings, 1 reply; 8+ messages in thread
From: Thomas Kellerer @ 2024-04-11 05:25 UTC (permalink / raw)
To: [email protected]
Tom Lane schrieb am 11.04.2024 um 01:02:
> Jan Behrens <[email protected]> writes:
>> 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.
>
> Right now, I don't think so.
Hmm, what about functions returning multiple refcursors?
From a client library point of view, I think that would qualify as
"multiple result sets"
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: (When) can a single SQL statement return multiple result sets?
2024-04-10 21:22 (When) can a single SQL statement return multiple result sets? Jan Behrens <[email protected]>
2024-04-10 23:02 ` Re: (When) can a single SQL statement return multiple result sets? Tom Lane <[email protected]>
2024-04-11 05:25 ` Re: (When) can a single SQL statement return multiple result sets? Thomas Kellerer <[email protected]>
@ 2024-04-11 14:07 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2024-04-11 14:07 UTC (permalink / raw)
To: Thomas Kellerer <[email protected]>; +Cc: [email protected]
Thomas Kellerer <[email protected]> writes:
> Tom Lane schrieb am 11.04.2024 um 01:02:
>> Jan Behrens <[email protected]> writes:
>>> 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.
>> Right now, I don't think so.
> Hmm, what about functions returning multiple refcursors?
Sure, but let's distinguish between "here's an ugly workaround"
and "it just works". Aside from being tedious, the refcursor
approach is restrictive: I don't think you can readily make
a refcursor on the result of INSERT/UPDATE/DELETE RETURNING,
nor on utility statements such as EXPLAIN. (There might be
a way around the former restriction with WITH, but I'm
certain that won't work for EXPLAIN.)
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: (When) can a single SQL statement return multiple result sets?
2024-04-10 21:22 (When) can a single SQL statement return multiple result sets? Jan Behrens <[email protected]>
2024-04-10 23:02 ` Re: (When) can a single SQL statement return multiple result sets? Tom Lane <[email protected]>
@ 2024-04-15 14:55 ` Peter Eisentraut <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: Peter Eisentraut @ 2024-04-15 14:55 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Jan Behrens <[email protected]>; +Cc: [email protected]
On 11.04.24 01:02, Tom Lane wrote:
>> And if not, why can't I write a stored procedure
>> or function that returns multiple result sets?
>
> [ shrug... ] Lack of round tuits, perhaps. We don't have any
> mechanism today whereby a stored procedure could say "please ship
> this resultset off to the client, but I want to continue afterwards".
> But you can do that in other RDBMSes and probably somebody will be
> motivated to make it possible in Postgres.
The development of this feature was the subject of this thread:
https://www.postgresql.org/message-id/flat/[email protected]
But it has not concluded successfully yet.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: (When) can a single SQL statement return multiple result sets?
2024-04-10 21:22 (When) can a single SQL statement return multiple result sets? Jan Behrens <[email protected]>
2024-04-10 23:02 ` Re: (When) can a single SQL statement return multiple result sets? Tom Lane <[email protected]>
@ 2024-04-15 15:24 ` Jan Behrens <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: Jan Behrens @ 2024-04-15 15:24 UTC (permalink / raw)
To: [email protected]; +Cc: Tom Lane <[email protected]>
On Wed, 10 Apr 2024 19:02:48 -0400
Tom Lane <[email protected]> wrote:
> Jan Behrens <[email protected]> writes:
> > 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.
>
> Right now, I don't think so. I believe the current protocol design
> intends to support that, and I think this may trace back to some
> ancient idea at Berkeley that if you select from an inheritance
> hierarchy where the child tables aren't all alike, you should be
> able to see all the child data, which'd require changing tuple
> descriptors midstream. But our current interpretation of SQL
> SELECT forbids that.
I thought multiple result sets are supported for commands like PQexec,
where "Multiple queries sent in a single PQexec call" are explictly
supported, and which then return multiple result set. This, however,
doesn't apply to pipelining because PQexec is not available in
pipelining mode.
>
> > Here, "DELETE FROM magic" returns multiple result sets, even though it
> > is only a single SQL statement.
>
> Right, so it's kind of a case that you have to support. We're not
> likely to rip out rules anytime soon, even if they're a bit
> deprecated.
As it seems to be a corner case that rarely occurs in practice, I was
considering to simply not support this case in my client library. I
don't know which SQL error code I could return in that case though.
Maybe "0A000" (feature_not_supported) or
"21000" (cardinality_violation). Not sure if either of those is a good
choice. Any better idea?
>
> > 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?
>
> No. For one thing, there's too much overlap between what you're
> suggesting and pipelined queries.
To which question was "no" the answer to. I'm not sure if I understand.
>
> regards, tom lane
>
Regards,
Jan Behrens
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: (When) can a single SQL statement return multiple result sets?
2024-04-10 21:22 (When) can a single SQL statement return multiple result sets? Jan Behrens <[email protected]>
@ 2024-04-11 07:41 ` Patrick FICHE <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: Patrick FICHE @ 2024-04-11 07:41 UTC (permalink / raw)
To: Jan Behrens <[email protected]>; [email protected] <[email protected]>
-----Original Message-----
From: Jan Behrens <[email protected]>
Sent: Wednesday, April 10, 2024 11:23 PM
To: [email protected]
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,
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-04-15 15:24 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-10 21:22 (When) can a single SQL statement return multiple result sets? Jan Behrens <[email protected]>
2024-04-10 22:47 ` Merlin Moncure <[email protected]>
2024-04-10 23:02 ` Tom Lane <[email protected]>
2024-04-11 05:25 ` Thomas Kellerer <[email protected]>
2024-04-11 14:07 ` Tom Lane <[email protected]>
2024-04-15 14:55 ` Peter Eisentraut <[email protected]>
2024-04-15 15:24 ` Jan Behrens <[email protected]>
2024-04-11 07:41 ` Patrick FICHE <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox