MIME-Version: 1.0
References: <20240410232247.b48cdf2677f87d37b167e140@magnetkern.de>
In-Reply-To: <20240410232247.b48cdf2677f87d37b167e140@magnetkern.de>
From: Merlin Moncure <mmoncure@gmail.com>
Date: Wed, 10 Apr 2024 17:47:09 -0500
Message-ID: <CAHyXU0w-d1qd7wikfEJ6TcP_3qGyZn4PKOY2VyhALdXfd=g-BA@mail.gmail.com>
Subject: Re: (When) can a single SQL statement return multiple result sets?
To: Jan Behrens <jbe-mlist@magnetkern.de>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000065c8b50615c5d2ae"
Archived-At: <https://www.postgresql.org/message-id/CAHyXU0w-d1qd7wikfEJ6TcP_3qGyZn4PKOY2VyhALdXfd%3Dg-BA%40mail.gmail.com>
Precedence: bulk

--00000000000065c8b50615c5d2ae
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Apr 10, 2024 at 4:22=E2=80=AFPM Jan Behrens <jbe-mlist@magnetkern.d=
e> 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=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
> -------+--------
>  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

--00000000000065c8b50615c5d2ae
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Apr 10, 2024 at 4:22=E2=80=AFPM J=
an Behrens &lt;<a href=3D"mailto:jbe-mlist@magnetkern.de">jbe-mlist@magnetk=
ern.de</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex">Hello,<br>
<br>
While writing a PostgreSQL client library for Lua supporting<br>
Pipelining (using PQsendQueryParams), I have been wondering if there<br>
are any single SQL commands that return multiple result sets. It is<br>
indeed possible to create such a case by using the RULE system:<br>
<br>
db=3D&gt; CREATE VIEW magic AS SELECT;<br>
CREATE VIEW<br>
db=3D&gt; CREATE RULE r1 AS ON DELETE TO magic<br>
db-&gt; DO INSTEAD SELECT 42 AS &quot;answer&quot;;<br>
CREATE RULE<br>
db=3D&gt; CREATE RULE r2 AS ON DELETE TO magic<br>
db-&gt; DO ALSO SELECT &#39;Hello&#39; AS &quot;col1&quot;, &#39;World!&#39=
; AS &quot;col2&quot;;<br>
CREATE RULE<br>
db=3D&gt; DELETE FROM magic; -- single SQL statement!<br>
=C2=A0answer <br>
--------<br>
=C2=A0 =C2=A0 =C2=A042<br>
(1 row)<br>
<br>
=C2=A0col1=C2=A0 |=C2=A0 col2=C2=A0 <br>
-------+--------<br>
=C2=A0Hello | World!<br>
(1 row)<br>
<br>
DELETE 0<br>
<br>
Here, &quot;DELETE FROM magic&quot; returns multiple result sets, even thou=
gh it<br>
is only a single SQL statement.<br></blockquote><div><br></div><div><br></d=
iv><div>=C2=A0I guess you should have named your table, &quot;sorcery&quot;=
, because that&#39;s what=C2=A0this is.=C2=A0 In the corporate=C2=A0world, =
we might regard the &#39;CREATE RULE&#39; feature as a &#39;solution opport=
unity&#39;=C2=A0=C2=A0:-).=C2=A0 You might be able to overlook this on your=
 end IMO as the view triggers feature=C2=A0has standardized and fixed the f=
eature.</div><div><br></div><div>&gt; why can&#39;t I write a stored proced=
ure or function that returns multiple result sets?</div><br></div><div clas=
s=3D"gmail_quote">Functions arguably should not be able to do this, doesn&#=
39;t the=C2=A0standard allow for procedures (top level statements invoked w=
ith CALL) to return multiple=C2=A0results?</div><div class=3D"gmail_quote">=
<br></div><div class=3D"gmail_quote">merlin</div><div class=3D"gmail_quote"=
><br></div><div class=3D"gmail_quote"><br><div><br></div></div></div>

--00000000000065c8b50615c5d2ae--