(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
On Wed, Apr 10, 2024 at 4:22 PM Jan Behrens <jbe-mlist@magnetkern.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
Jan Behrens <jbe-mlist@magnetkern.de> 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
Tom Lane schrieb am 11.04.2024 um 01:02:
Jan Behrens <jbe-mlist@magnetkern.de> 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"
-----Original Message-----
From: Jan Behrens <jbe-mlist@magnetkern.de>
Sent: Wednesday, April 10, 2024 11:23 PM
To: pgsql-general@lists.postgresql.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,
Thomas Kellerer <shammat@gmx.net> writes:
Tom Lane schrieb am 11.04.2024 um 01:02:
Jan Behrens <jbe-mlist@magnetkern.de> 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
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:
/messages/by-id/6e747f98-835f-2e05-cde5-86ee444a7140@2ndquadrant.com
But it has not concluded successfully yet.
On Wed, 10 Apr 2024 19:02:48 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jan Behrens <jbe-mlist@magnetkern.de> 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
On Mon, Apr 15, 2024 at 10:24 AM Jan Behrens <jbe-mlist@magnetkern.de>
wrote:
On Wed, 10 Apr 2024 19:02:48 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote: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?
If you are asking if "rules" can be ignored or error-walled in terms of
your library design, I'd say yes. 100% yes.
The main caveat would then be the proposed multi-resultset stored procedure
feature, which might break the 'one result per semicolon' assumption you
might be chasing as it has some basis in the standard, so I'd be balancing
risk/reward against that feature IMO if I were you.
merlin