Re: How to return seto records from seof record function?

On Tuesday, February 25, 2025, Олег Самойлов <splarv(at)ya(dot)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.