From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
cc: =?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>
Subject: Re: How to return seto records from seof record function?
In-reply-to: <de2f8859f72a93a6bc1f8347e503691cc03c7c37.camel@cybertec.at>
References: <56861740492680@mail.yandex.ru> <de2f8859f72a93a6bc1f8347e503691cc03c7c37.camel@cybertec.at>
Comments: In-reply-to Laurenz Albe <laurenz.albe@cybertec.at>
	message dated "Tue, 25 Feb 2025 15:47:23 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1597770.1740498180.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 25 Feb 2025 10:43:00 -0500
Message-ID: <1597771.1740498180@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1597771.1740498180%40sss.pgh.pa.us>
Precedence: bulk

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> 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:
>> How to return seto records from seof record function? I tried pg_backgr=
ound 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=3D# \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=3D# create or replace function wrapper(anyarray)
RETURNS SETOF record LANGUAGE sql as
$$ select 1; select array_to_set($1); $$;
CREATE FUNCTION

regression=3D# select wrapper(array[44,55,66]);
 wrapper =

---------
 (1,44)
 (2,55)
 (3,66)
(3 rows)

			regards, tom lane