pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to return seto records from seof record function?
6+ messages / 5 participants
[nested] [flat]
* How to return seto records from seof record function?
@ 2025-02-25 14:15 Олег Самойлов <splarv@ya.ru>
2025-02-25 14:29 ` Re: How to return seto records from seof record function? David G. Johnston <david.g.johnston@gmail.com>
2025-02-25 14:47 ` Re: How to return seto records from seof record function? Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 6+ messages in thread
From: Олег Самойлов @ 2025-02-25 14:15 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
<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"><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">;</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)"</div><div>PL/pgSQL function autonomous(text) line 6 at RETURN QUERY</div></div></div></div>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to return seto records from seof record function?
2025-02-25 14:15 How to return seto records from seof record function? Олег Самойлов <splarv@ya.ru>
@ 2025-02-25 14:29 ` David G. Johnston <david.g.johnston@gmail.com>
2025-02-25 14:42 ` Re: How to return seto records from seof record function? Олег Самойлов <splarv@ya.ru>
1 sibling, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2025-02-25 14:29 UTC (permalink / raw)
To: Олег Самойлов <splarv@ya.ru>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to return seto records from seof record function?
2025-02-25 14:15 How to return seto records from seof record function? Олег Самойлов <splarv@ya.ru>
2025-02-25 14:29 ` Re: How to return seto records from seof record function? David G. Johnston <david.g.johnston@gmail.com>
@ 2025-02-25 14:42 ` Олег Самойлов <splarv@ya.ru>
0 siblings, 0 replies; 6+ messages in thread
From: Олег Самойлов @ 2025-02-25 14:42 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
<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" <david.g.johnston@gmail.com>:<br /></div>
<blockquote>On Tuesday, February 25, 2025, Олег Самойлов <<a href="mailto:splarv@ya.ru">splarv@ya.ru</a>> 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>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to return seto records from seof record function?
2025-02-25 14:15 How to return seto records from seof record function? Олег Самойлов <splarv@ya.ru>
@ 2025-02-25 14:47 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-25 15:43 ` Re: How to return seto records from seof record function? Tom Lane <tgl@sss.pgh.pa.us>
2025-02-26 23:31 ` Re: How to return seto records from seof record function? Merlin Moncure <mmoncure@gmail.com>
1 sibling, 2 replies; 6+ messages in thread
From: Laurenz Albe @ 2025-02-25 14:47 UTC (permalink / raw)
To: Олег Самойлов <splarv@ya.ru>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to return seto records from seof record function?
2025-02-25 14:15 How to return seto records from seof record function? Олег Самойлов <splarv@ya.ru>
2025-02-25 14:47 ` Re: How to return seto records from seof record function? Laurenz Albe <laurenz.albe@cybertec.at>
@ 2025-02-25 15:43 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2025-02-25 15:43 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Олег Самойлов <splarv@ya.ru>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to return seto records from seof record function?
2025-02-25 14:15 How to return seto records from seof record function? Олег Самойлов <splarv@ya.ru>
2025-02-25 14:47 ` Re: How to return seto records from seof record function? Laurenz Albe <laurenz.albe@cybertec.at>
@ 2025-02-26 23:31 ` Merlin Moncure <mmoncure@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Merlin Moncure @ 2025-02-26 23:31 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Олег Самойлов <splarv@ya.ru>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-02-26 23:31 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-25 14:15 How to return seto records from seof record function? Олег Самойлов <splarv@ya.ru>
2025-02-25 14:29 ` David G. Johnston <david.g.johnston@gmail.com>
2025-02-25 14:42 ` Олег Самойлов <splarv@ya.ru>
2025-02-25 14:47 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-25 15:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-02-26 23:31 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox