How to return seto records from seof record function?

On Tuesday, February 25, 2025, Олег Самойлов <splarv@ya.ru> wrote:

Postgresql 17.2

How to return seto records from seof record function? I tried
pg_background extension:

*CREATE* *OR* *REPLACE* *FUNCTION* public.autonomous (p_script *text*)

*RETURNS* *SETOF* record

*LANGUAGE* plpgsql

*VOLATILE* *STRICT* *PARALLEL* UNSAFE

*AS* *$autonomous$*

*DECLARE*

l_id *integer*;

*BEGIN*

l_id := pg_background_launch(p_script);

*RETURN* QUERY *SELECT* * *FROM* pg_background_result(l_id) *AS* (r record
);

*END*;

*$autonomous$*;

*SELECT** * **FROM* *autonomous**(**'SELECT now()'**) **AS** (**a*
*timestamptz**)**;*

SQL Error [42804]: ERROR: structure of query does not match function
result type
Detail: Returned type record does not match expected type timestamp with
time zone in column 1.
Where: SQL statement "SELECT * FROM pg_background_result(l_id) AS (r
record)"

PL/pgSQL function autonomous(text) line 6 at RETURN QUERY

Interesting…not sure this can work as you have no way to know what the
caller has specified as the return data type in order to write the inner
generic function call correctly. Maybe you can convert the record result
to jsonb and return that?

David J.

<div>I'll be happy to drop result (the procedure created to insert logs). But this does not work too.<br /> <div style="background-color:#2f2f2f;padding:0px 0px 0px 2px"><div style="background-color:#2f2f2f;color:#cccccc;font-family:'cascadia code';font-size:11pt;white-space:pre"><p style="margin:0"><strong style="color:#739eca;font-weight:bold">CREATE</strong> <strong style="color:#739eca;font-weight:bold">OR</strong> <strong style="color:#739eca;font-weight:bold">REPLACE</strong> <strong style="color:#739eca;font-weight:bold">PROCEDURE</strong> <span style="color:#b788d3">public</span>.<span style="color:#b788d3">autonomous</span> (<span style="color:#00b8b8">p_script</span> <strong style="color:#739eca;font-weight:bold">IN</strong> <strong style="color:#c1aa6c;font-weight:bold">text</strong>)</p><p style="margin:0"><strong style="color:#739eca;font-weight:bold">LANGUAGE</strong> <span style="color:#9e9e9e">plpgsql</span></p><p style="margin:0"><strong style="color:#739eca;font-weight:bold">AS</strong> <strong style="color:#eecc64;font-weight:bold">$autonomous$</strong></p><p style="margin:0"><strong style="color:#739eca;font-weight:bold">DECLARE</strong></p><p style="margin:0"><span style="color:#9e9e9e">l_id</span> <strong style="color:#c1aa6c;font-weight:bold">integer</strong><span style="color:#eecc64">;</span></p><p style="margin:0"><strong style="color:#739eca;font-weight:bold">BEGIN</strong></p><p style="margin:0"><span style="color:#9e9e9e">l_id</span> := <span style="color:#9e9e9e">pg_background_launch</span>(<span style="color:#9e9e9e">p_script</span>)<span style="color:#eecc64">;</span></p><p style="margin:0"><strong style="color:#739eca;font-weight:bold">PERFORM</strong> <span style="color:#9e9e9e">pg_background_result</span>(<span style="color:#9e9e9e">l_id</span>)<span style="color:#eecc64">;</span></p><p style="margin:0"><strong style="color:#739eca;font-weight:bold">END</strong><span style="color:#eecc64">;</span></p><p style="margin:0"><strong style="color:#eecc64;font-weight:bold">$autonomous$</strong><span style="color:#eecc64">;</span></p></div></div></div><div><br /></div><div><br /></div><div>----------------</div>
<div>Кому: Олег Самойлов (splarv@ya.ru);<br /></div>
<div>Копия: pgsql-general@lists.postgresql.org;<br /></div>
<div>Тема: How to return seto records from seof record function?;<br /></div>
<div>25.02.2025, 17:29, "David G. Johnston" &lt;david.g.johnston@gmail.com&gt;:<br /></div>
<blockquote>On Tuesday, February 25, 2025, Олег Самойлов &lt;<a href="mailto:splarv@ya.ru">splarv@ya.ru</a>&gt; wrote:<br /><blockquote class="f13ca48719c8a60033905b23b39675agmail_quote" style="border-left-color:#ccc;border-left-style:solid;border-left-width:1px;margin:0 0 0 0.8ex;padding-left:1ex"><div>Postgresql 17.2<br /><br />How to return seto records from seof record function? I tried pg_background extension:</div><div> </div><div><div style="background-color:#2f2f2f;padding:0px 0px 0px 2px"><div style="background-color:#2f2f2f;color:#cccccc;font-family:'cascadia code';font-size:11pt;white-space:pre-wrap"><p style="margin:0"><strong style="color:#739eca;font-weight:bold">CREATE</strong> <strong style="color:#739eca;font-weight:bold">OR</strong> <strong style="color:#739eca;font-weight:bold">REPLACE</strong> <strong style="color:#739eca;font-weight:bold">FUNCTION</strong> <span style="color:#b788d3">public</span>.<span style="color:#b788d3">autonomous</span> (<span style="color:#00b8b8">p_script</span> <strong style="color:#c1aa6c;font-weight:bold">text</strong>)</p><p style="margin:0"><strong style="color:#739eca;font-weight:bold">RETURNS</strong> <strong style="color:#739eca;font-weight:bold">SETOF</strong> <span style="color:#9e9e9e">record</span></p><p style="margin:0"><strong style="color:#739eca;font-weight:bold">LANGUAGE</strong> <span style="color:#9e9e9e">plpgsql</span></p><p style="margin:0"><strong style="color:#739eca;font-weight:bold">VOLATILE</strong> <strong style="color:#739eca;font-weight:bold">STRICT</strong> <strong style="color:#739eca;font-weight:bold">PARALLEL</strong> <span style="color:#9e9e9e">UNSAFE</span></p><p style="margin:0"><strong style="color:#739eca;font-weight:bold">AS</strong> <strong style="color:#eecc64;font-weight:bold">$autonomous$</strong></p><p style="margin:0"><strong style="color:#739eca;font-weight:bold">DECLARE</strong></p><p style="margin:0"><span style="color:#9e9e9e">l_id</span> <strong style="color:#c1aa6c;font-weight:bold">integer</strong><span style="color:#eecc64">;</span></p><p style="margin:0"><strong style="color:#739eca;font-weight:bold">BEGIN</strong></p><p style="margin:0"><span style="color:#9e9e9e">l_id</span> := <span style="color:#9e9e9e">pg_background_launch</span>(<span style="color:#9e9e9e">p_script</span>)<span style="color:#eecc64"><wbr />;</span></p><p style="margin:0"><strong style="color:#739eca;font-weight:bold">RETURN</strong> <span style="color:#9e9e9e">QUERY</span> <strong style="color:#739eca;font-weight:bold">SELECT</strong> * <strong style="color:#739eca;font-weight:bold">FROM</strong> <span style="color:#9e9e9e">pg_background_result</span>(<span style="color:#9e9e9e">l_id</span>) <strong style="color:#739eca;font-weight:bold">AS</strong> (<span style="color:#9e9e9e">r</span> <span style="color:#9e9e9e">record</span>)<span style="color:#eecc64">;</span></p><p style="margin:0"><strong style="color:#739eca;font-weight:bold">END</strong><span style="color:#eecc64">;</span></p><p style="margin:0"><strong style="color:#eecc64;font-weight:bold">$autonomous$</strong><span style="color:#eecc64">;</span></p><p style="margin:0"> </p><p style="margin:0"><u style="color:#739eca;font-weight:bold;text-decoration:underline #ff0080 wavy">SELECT</u><u style="text-decoration:underline #ff0080 wavy"> * </u><u style="color:#739eca;font-weight:bold;text-decoration:underline #ff0080 wavy">FROM</u><u style="text-decoration:underline #ff0080 wavy"> </u><u style="color:#9e9e9e;text-decoration:underline #ff0080 wavy">autonomous</u><u style="text-decoration:underline #ff0080 wavy">(</u><u style="color:#cac580;font-weight:bold;text-decoration:underline #ff0080 wavy">'SELECT now()'</u><u style="text-decoration:underline #ff0080 wavy">) </u><u style="color:#739eca;font-weight:bold;text-decoration:underline #ff0080 wavy">AS</u><u style="text-decoration:underline #ff0080 wavy"> (</u><u style="color:#9e9e9e;text-decoration:underline #ff0080 wavy">a</u><u style="text-decoration:underline #ff0080 wavy"> </u><u style="color:#c1aa6c;font-weight:bold;text-decoration:underline #ff0080 wavy">timestamptz</u><u style="text-decoration:underline #ff0080 wavy">)</u><u style="color:#eecc64;text-decoration:underline #ff0080 wavy">;</u></p><p style="margin:0"> </p><div>SQL Error [42804]: ERROR: structure of query does not match function result type</div><div>  Detail: Returned type record does not match expected type timestamp with time zone in column 1.</div><div>  Where: SQL statement "SELECT * FROM pg_background_result(l_id) AS (r record)"<span style="background-color:rgb( 255 , 255 , 255 );font-family:'helvetica neue' , 'helvetica' , 'arial' , sans-serif;font-size:15px"> </span></div></div></div></div></blockquote><blockquote class="f13ca48719c8a60033905b23b39675agmail_quote" style="border-left-color:#ccc;border-left-style:solid;border-left-width:1px;margin:0 0 0 0.8ex;padding-left:1ex"><div><div style="background-color:#2f2f2f;padding:0px 0px 0px 2px"><div style="background-color:#2f2f2f;color:#cccccc;font-family:'cascadia code';font-size:11pt;white-space:pre-wrap"><div>PL/pgSQL function autonomous(text) line 6 at RETURN QUERY</div></div></div></div></blockquote><div><br /></div><div>Interesting…not sure this can work as you have no way to know what the caller has specified as the return data type in order to write the inner generic function call correctly.  Maybe you can convert the record result to jsonb and return that?</div><div><br /></div><div>David J.</div><div> </div>
</blockquote>

On Tue, 2025-02-25 at 17:15 +0300, Олег Самойлов wrote:

Postgresql 17.2

How to return seto records from seof record function? I tried pg_background extension:
 
CREATE OR REPLACE FUNCTION public.autonomous (p_script text)
RETURNS SETOF record
LANGUAGE plpgsql
VOLATILE STRICT PARALLEL UNSAFE
AS $autonomous$
DECLARE
l_id integer;
BEGIN
l_id := pg_background_launch(p_script);
RETURN QUERY SELECT * FROM pg_background_result(l_id) AS (r record);
END;
$autonomous$;
 
SELECT * FROM autonomous('SELECT now()') AS (a timestamptz);
 
SQL Error [42804]: ERROR: structure of query does not match function result type
  Detail: Returned type record does not match expected type timestamp with time zone in column 1.
  Where: SQL statement "SELECT * FROM pg_background_result(l_id) AS (r record)"
PL/pgSQL function autonomous(text) line 6 at RETURN QUERY

You need to be specific:

SELECT * FROM pg_background_result(l_id) AS (col1 integer, col2 text, ...);

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"...

Yours,
Laurenz Albe

Laurenz Albe <laurenz.albe@cybertec.at> writes:

On Tue, 2025-02-25 at 17:15 +0300, Олег Самойлов wrote:

How to return seto records from seof record function? I tried pg_background extension:

You need to be specific:
SELECT * FROM pg_background_result(l_id) AS (col1 integer, col2 text, ...);
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().

plpgsql is indeed not too friendly to this, but perhaps a SQL-language
function would serve. That infrastructure seems to be okay with
wrapping a generic setof-record result:

regression=# \sf array_to_set
CREATE OR REPLACE FUNCTION public.array_to_set(anyarray)
RETURNS SETOF record
LANGUAGE sql
IMMUTABLE
AS $function$
select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i
$function$

regression=# create or replace function wrapper(anyarray)
RETURNS SETOF record LANGUAGE sql as
$$ select 1; select array_to_set($1); $$;
CREATE FUNCTION

regression=# select wrapper(array[44,55,66]);
wrapper
---------
(1,44)
(2,55)
(3,66)
(3 rows)

regards, tom lane

On Tue, Feb 25, 2025 at 8:47 AM 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