public inbox for [email protected]  

help / color / mirror / Atom feed

From: Patrick FICHE <[email protected]>
To: Jan Behrens <[email protected]>
To: [email protected] <[email protected]>
Subject: RE: (When) can a single SQL statement return multiple result sets?
Date: Thu, 11 Apr 2024 07:41:56 +0000
Message-ID: <DUZPR05MB1102143F9F232C1B3B668F690EF052@DUZPR05MB11021.eurprd05.prod.outlook.com> (raw)
In-Reply-To: <[email protected]>
References: <[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,

view thread (8+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: RE: (When) can a single SQL statement return multiple result sets?
  In-Reply-To: <DUZPR05MB1102143F9F232C1B3B668F690EF052@DUZPR05MB11021.eurprd05.prod.outlook.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox