Date: Wed, 10 Apr 2024 23:22:47 +0200
From: Jan Behrens <jbe-mlist@magnetkern.de>
To: pgsql-general@lists.postgresql.org
Subject: (When) can a single SQL statement return multiple result sets?
Message-Id: <20240410232247.b48cdf2677f87d37b167e140@magnetkern.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/20240410232247.b48cdf2677f87d37b167e140%40magnetkern.de>
Precedence: bulk

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