Message-ID: <de2f8859f72a93a6bc1f8347e503691cc03c7c37.camel@cybertec.at>
Subject: Re: How to return seto records from seof record function?
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: =?UTF-8?Q?=D0=9E=D0=BB=D0=B5=D0=B3_?=
 =?UTF-8?Q?=D0=A1=D0=B0=D0=BC=D0=BE=D0=B9=D0=BB=D0=BE=D0=B2?=
	 <splarv@ya.ru>, "pgsql-general@lists.postgresql.org"
	 <pgsql-general@lists.postgresql.org>
Date: Tue, 25 Feb 2025 15:47:23 +0100
In-Reply-To: <56861740492680@mail.yandex.ru>
References: <56861740492680@mail.yandex.ru>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/de2f8859f72a93a6bc1f8347e503691cc03c7c37.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-02-25 at 17:15 +0300, =D0=9E=D0=BB=D0=B5=D0=B3 =D0=A1=D0=B0=D0=
=BC=D0=BE=D0=B9=D0=BB=D0=BE=D0=B2 wrote:
> Postgresql 17.2
>=20
> How to return seto records from seof record function? I tried pg_backgrou=
nd extension:
> =C2=A0
> 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 :=3D pg_background_launch(p_script);
> RETURN QUERY SELECT * FROM pg_background_result(l_id) AS (r record);
> END;
> $autonomous$;
> =C2=A0
> SELECT * FROM autonomous('SELECT now()') AS (a timestamptz);
> =C2=A0
> SQL Error [42804]: ERROR: structure of query does not match function resu=
lt type
> =C2=A0 Detail: Returned type record does not match expected type timestam=
p with time zone in column 1.
> =C2=A0 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