Subject: (When) can a single SQL statement return multiple result sets?

From: Jan Behrens <[email-masked]>
Date: Wed, 10 Apr 2024 23:22:47 +0200

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

---

From: Merlin Moncure <[email-masked]>
Date: Wed, 10 Apr 2024 17:47:09 -0500

> db=> DELETE FROM magic; -- 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

---

From: Tom Lane <[email-masked]>
Date: Wed, 10 Apr 2024 19:02:48 -0400

Jan Behrens <[email-masked]> writes:
> 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.

> 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.

> 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

[ 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

---

From: Thomas Kellerer <[email-masked]>
Date: Thu, 11 Apr 2024 07:25:59 +0200

Tom Lane schrieb am 11.04.2024 um 01:02:
> 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"

---

From: Patrick FICHE <[email-masked]>
Date: Thu, 11 Apr 2024 07:41:56 +0000

-----Original Message-----
From: Jan Behrens <[email-masked]> 
Sent: Wednesday, April 10, 2024 11:23 PM
To: [email-masked]
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:

 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: Tom Lane <[email-masked]>
Date: Thu, 11 Apr 2024 10:07:34 -0400

> 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

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 15 Apr 2024 16:55:43 +0200

> 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-masked]

But it has not concluded successfully yet.

---

From: Jan Behrens <[email-masked]>
Date: Mon, 15 Apr 2024 17:24:31 +0200

On Wed, 10 Apr 2024 19:02:48 -0400

> descriptors midstream.  But our current interpretation of SQL

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.

> likely to rip out rules anytime soon, even if they're a bit

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?

> suggesting and pipelined queries.

To which question was "no" the answer to. I'm not sure if I understand.

Regards,
Jan Behrens