MIME-Version: 1.0
References: <56861740492680@mail.yandex.ru> <de2f8859f72a93a6bc1f8347e503691cc03c7c37.camel@cybertec.at>
In-Reply-To: <de2f8859f72a93a6bc1f8347e503691cc03c7c37.camel@cybertec.at>
From: Merlin Moncure <mmoncure@gmail.com>
Date: Wed, 26 Feb 2025 17:31:56 -0600
Message-ID: <CAHyXU0x6tQAqXm71LuSg+pK13L_9QCUcVgEEdCjuJrumJfa-PQ@mail.gmail.com>
Subject: Re: How to return seto records from seof record function?
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: =?UTF-8?B?0J7Qu9C10LMg0KHQsNC80L7QudC70L7Qsg==?= <splarv@ya.ru>, 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000006fb311062f13fb4d"
Archived-At: <https://www.postgresql.org/message-id/CAHyXU0x6tQAqXm71LuSg%2BpK13L_9QCUcVgEEdCjuJrumJfa-PQ%40mail.gmail.com>
Precedence: bulk

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

On Tue, Feb 25, 2025 at 8:47=E2=80=AFAM Laurenz Albe <laurenz.albe@cybertec=
.at>
wrote:

>
> I don't think there is a way to get a generic "record" as result.
> And even if you could, you would still have to specify a column list
> when you call autonomous().
>
> Attempts to write functions with polymorphic return type are usually
> futile.
>
> Perhaps you can return a "SETOF jsonb"...


There is only one non-jsonb method I'm aware of to convert string query to
result without specifying result structure, and that's via refcursors,
something like:
begin;
BEGIN;

CREATE FUNCTION f() RETURNS TEXT AS
$$
DECLARE
  r REFCURSOR DEFAULT 'test';
BEGIN
  OPEN r FOR EXECUTE $z$SELECT 'a' AS a, 1 AS b$z$;
  RETURN r;
END;
$$ LANGUAGE PLPGSQL;

SELECT f();

FETCH test;
...

..I doubt it works in OP's case though as this only works to push all the
way back to the client app. but it's a neat artifact from yore.

In modern postgres, I think jsonb is the way to go.  Function output syntax
is one of the clunkiest parts of the language, you are on a freight train
to deep dynamic SQL; it sure would be nice if we could somehow pass an
output definition somehow in a way the calling function or query could
use.  This mostly comes up in my experience with analytics, where the
column needs are very dynamic and layered.

merlin

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

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Feb 25, 2025 at 8:47=E2=80=AFAM L=
aurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cy=
bertec.at</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_contain=
er"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex"><br>
I don&#39;t think there is a way to get a generic &quot;record&quot; as res=
ult.<br>
And even if you could, you would still have to specify a column list<br>
when you call autonomous().<br>
<br>
Attempts to write functions with polymorphic return type are usually futile=
.<br>
<br>
Perhaps you can return a &quot;SETOF jsonb&quot;...</blockquote><div><br></=
div><div>There is only one non-jsonb method I&#39;m aware of to convert str=
ing query to result without specifying result structure, and that&#39;s via=
 refcursors, something=C2=A0like:</div><div>begin;</div><div>BEGIN;</div><d=
iv><br>CREATE FUNCTION f() RETURNS TEXT AS<br>$$<br>DECLARE<br>=C2=A0 r REF=
CURSOR DEFAULT &#39;test&#39;;<br>BEGIN<br>=C2=A0 OPEN r FOR EXECUTE $z$SEL=
ECT &#39;a&#39; AS a, 1 AS b$z$;<br>=C2=A0 RETURN r;<br>END;<br>$$ LANGUAGE=
 PLPGSQL;<br>=C2=A0<br>SELECT f();<br>=C2=A0<br>FETCH test;<br>...</div><di=
v><br></div><div>..I doubt it works in OP&#39;s case though as this only wo=
rks to push all the way back to the client app. but it&#39;s a neat artifac=
t from yore.=C2=A0 =C2=A0</div><div><br></div><div>In modern postgres, I th=
ink=C2=A0jsonb is the way to go.=C2=A0 Function output syntax is one of the=
 clunkiest parts of the language, you are on a freight train to deep dynami=
c SQL; it sure would be nice if we could somehow pass an output definition =
somehow in a way the calling function or query could use.=C2=A0 This mostly=
 comes up in my experience=C2=A0with analytics, where the column needs are =
very dynamic and layered.</div><div><br></div><div>merlin</div><div><br></d=
iv><div>=C2=A0</div></div></div>

--0000000000006fb311062f13fb4d--