From: Patrick FICHE <Patrick.Fiche@aqsacom.com>
To: Jan Behrens <jbe-mlist@magnetkern.de>,
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: RE: (When) can a single SQL statement return multiple result sets?
Thread-Topic: (When) can a single SQL statement return multiple result sets?
Thread-Index: AQHai41BUJdFDY/YcEmbUOeSnse5xrFir5lQ
Date: Thu, 11 Apr 2024 07:41:56 +0000
Message-ID:
 <DUZPR05MB1102143F9F232C1B3B668F690EF052@DUZPR05MB11021.eurprd05.prod.outlook.com>
References: <20240410232247.b48cdf2677f87d37b167e140@magnetkern.de>
In-Reply-To: <20240410232247.b48cdf2677f87d37b167e140@magnetkern.de>
Accept-Language: en-US, fr-FR
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?us-ascii?Q?vfZHQKwDCPCEI2VlTBti9TGOFA+NJcgDBtv8mfDaVcJKzKTFVH38TghRup4K?=
 =?us-ascii?Q?OnbyxWTwNIoWXT1rOuHC4YpoXH7jpywuq5/bbXbf+K1+dFjAYTXgXBBBhvyb?=
 =?us-ascii?Q?SET1eFclWBOFrRPg8P3PdPMwLNljixj/dKNWfNQRdmoSr84uK7mV5eLpIlT2?=
 =?us-ascii?Q?v0jwyjAUMQE4EflfnGcFDEafj/Ib/pqsp4nFHfDv55GGs9IxnJh9tf9LzGwh?=
 =?us-ascii?Q?9DTq6HmsU66LLChUGDWbMj4vyMcP13TZxSG/MC7YG0vWqrAQAPaAtXlETubi?=
 =?us-ascii?Q?NyKRaoW4x6TZ5PKGZ9VPSDNpSLRWQjh4+OpAMont7xq0owvpNriCL2cMMFO6?=
 =?us-ascii?Q?1URwBfCDyLov/n16AXSCeLyEFdEVX54buTFZZQ8xOYpBYLMgxUrYJi/28cMQ?=
 =?us-ascii?Q?OjqFvJ1Upbs8JTvNC9V+8qMo5eXY5QxzQWQodkszVwbi/wQGcHgLrwp55xfN?=
 =?us-ascii?Q?5VXKDV67+1qTusT0Kq9zLiUzIe3SfyYdIe7e+4aXmT3t1rpVUfXdY3SnfFqB?=
 =?us-ascii?Q?03vCcit1XiCbhKc61uVTRNvnDlWF6e0/5NnuAk6LhYaiYjyf4J0yOkeX9kb8?=
 =?us-ascii?Q?vAEjeLwoYGfi4yF69ZxZyP31mwN8oCZwwEnBHqVhvegToY+6+qR0nvr37nEo?=
 =?us-ascii?Q?pPTdjTQEP3Kno8rzDnbSbZj6ToZMI5jXZE1zbqMZ0MMChAZiuHye/9nc8BYk?=
 =?us-ascii?Q?yJ06pouiFzzdiYjUi0s7WtjJD7Vrz7VtOXvk0Xk2Qwq4x5X5ttrT9m/lKwH5?=
 =?us-ascii?Q?pj1YczMkJSalGvc4K7SjfffgraBm59sjm7cvFEuQZemeRePe5uZKh+itYD0O?=
 =?us-ascii?Q?fNPGRTHUCFg10b/UP/HhRM6ROTBWRndRDcZZjXL5/Cnt1uisgk+pAbOFppE2?=
 =?us-ascii?Q?i2MrlByMSrCUzMisq1vFweYUgblP99pe/35zN/tDIOdB9XzDrwKiZa9+jJeG?=
 =?us-ascii?Q?oDQELFklWIBEYlXBSKhAKg1/DIOipims1jEcxDcueh3V14oYdSl/Z3g8lmwk?=
 =?us-ascii?Q?7EUi5xGhZiV+R8XyhKACwN+fELkjMaXThh6IKc0pBuFBbn6YIjt0z8whPvos?=
 =?us-ascii?Q?uXnmRjV7UZGh2VCQTxYUCDlXvMeDqY/7FQwn3bTbriMbOe7i6YOCv/5AtkXq?=
 =?us-ascii?Q?zABC5p6BSMGZHrYqszKL0sDz/WrUNU6YGVZ8nPv1n52f6FgSg2m+qvJvy0Qk?=
 =?us-ascii?Q?2dAX0Gf60Ttf2Cvp6n9+gDqz4uaKI9UfcKbjHxMofu9JPfWbJ3B0l77QLqdw?=
 =?us-ascii?Q?I/it8u3mnj83sP39h3mokM9Y/zD7uEMozL5pOobZFFQzJhcXG49+BynB+8l1?=
 =?us-ascii?Q?1wDvYkxA9bn5hItFqKBPn2NbAqfTwwDfUqKc6O0ZQPD4PUUY6Z+pNy/4siD7?=
 =?us-ascii?Q?w+CDz1cYV6GUYurC69GWLI9sSNwpVad46G73M8VbYWV70SfKmxRY3jHd0CmV?=
 =?us-ascii?Q?/I5DPBrnmz1+LHfQWHEs3Fk9/FwvJHNd7SbknYmFkNbLRzH8vv1LBELCKk72?=
 =?us-ascii?Q?n+O4OIo+sBU3Q0csLOa0pU0w3IRMlExuPLTo8+GIy70D5W7bVTv5cw3ixLa1?=
 =?us-ascii?Q?UkwGfD4D3h5cf0fCZrOuQD+Rav3dAUdwm7WCS52i?=
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: aqsacom.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: DUZPR05MB11021.eurprd05.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 2d80ce41-f4e0-4274-14b0-08dc59fadd35
X-MS-Exchange-CrossTenant-originalarrivaltime: 11 Apr 2024 07:41:56.7657
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 91f6be4c-f21e-43f0-9149-3a24dbb5c188
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 5ywhwfv4iGND6IIU1Oebjum9erXOeR4U8bcXyubBKdmH6HiPfZVEJ61p4dsfFtqcTF1zMq1kH/8PDnFPiAIbHg0+cX2rkNeRPdgNfK3pJyA=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: DU0PR05MB9986
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/DUZPR05MB1102143F9F232C1B3B668F690EF052%40DUZPR05MB11021.eurprd05.prod.outlook.com>
Precedence: bulk

-----Original Message-----
From: Jan Behrens <jbe-mlist@magnetkern.de>=20
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 (us=
ing PQsendQueryParams), I have been wondering if there are any single SQL c=
ommands that return multiple result sets. It is indeed possible to create s=
uch a case by using the RULE system:

db=3D> CREATE VIEW magic AS SELECT;
CREATE VIEW
db=3D> CREATE RULE r1 AS ON DELETE TO magic
db-> DO INSTEAD SELECT 42 AS "answer";
CREATE RULE
db=3D> CREATE RULE r2 AS ON DELETE TO magic
db-> DO ALSO SELECT 'Hello' AS "col1", 'World!' AS "col2";
CREATE RULE
db=3D> DELETE FROM magic; -- single SQL statement!
 answer
--------
     42
(1 row)

 col1  |  col2 =20
-------+--------
 Hello | World!
(1 row)

DELETE 0

Here, "DELETE FROM magic" returns multiple result sets, even though it is o=
nly a single SQL statement.

(Note that this isn't possible with rules ON SELECT because it is only allo=
wed to create a single SELECT rule on a view.)

The case outlined above seems to be a somewhat special case. I haven't foun=
d any other way to return multiple results (other than sending several semi=
colon-separated statements, which is not supported by PQsendQueryParams). S=
o is there any (other) case where I reasonably should expect several result=
 sets returned by PQgetResult (before PQgetResult returns NULL)? Wouldn't i=
t 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,