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

[Олег Самойлов <splarv@xxxxx>]

I'll be happy to drop result (the procedure created to insert logs). But this does not work too.
 

CREATE OR REPLACE PROCEDURE public.autonomous (p_script IN text)

LANGUAGE plpgsql

AS $autonomous$

DECLARE

l_id integer;

BEGIN

l_id := pg_background_launch(p_script);

PERFORM pg_background_result(l_id);

END;

$autonomous$;

----------------

Кому: Олег Самойлов (splarv@xxxxx);

Копия: pgsql-general@xxxxxxxxxxxxxxxxxxxx;

Тема: How to return seto records from seof record function?;

25.02.2025, 17:29, "David G. Johnston" <david.g.johnston@xxxxxxxxx>:

On Tuesday, February 25, 2025, Олег Самойлов <splarv@xxxxx> 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.