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.
