public inbox for [email protected]  

help / color / mirror / Atom feed

From: Merlin Moncure <[email protected]>
To: Jan Behrens <[email protected]>
Cc: [email protected]
Subject: Re: (When) can a single SQL statement return multiple result sets?
Date: Wed, 10 Apr 2024 17:47:09 -0500
Message-ID: <CAHyXU0w-d1qd7wikfEJ6TcP_3qGyZn4PKOY2VyhALdXfd=g-BA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

On Wed, Apr 10, 2024 at 4:22 PM Jan Behrens <[email protected]> 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

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: <CAHyXU0w-d1qd7wikfEJ6TcP_3qGyZn4PKOY2VyhALdXfd=g-BA@mail.gmail.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