Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tmvwd-000LDH-8f for pgsql-general@arkaria.postgresql.org; Tue, 25 Feb 2025 14:29:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tmvwc-002tRt-7i for pgsql-general@arkaria.postgresql.org; Tue, 25 Feb 2025 14:29:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tmvwb-002tRl-Q6 for pgsql-general@lists.postgresql.org; Tue, 25 Feb 2025 14:29:57 +0000 Received: from mail-oa1-x33.google.com ([2001:4860:4864:20::33]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tmvwZ-0001bV-0F for pgsql-general@lists.postgresql.org; Tue, 25 Feb 2025 14:29:57 +0000 Received: by mail-oa1-x33.google.com with SMTP id 586e51a60fabf-2bc607b33d5so3419520fac.3 for ; Tue, 25 Feb 2025 06:29:55 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1740493793; x=1741098593; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=mTwR2zVSuvuPvN8kkvT0Wk3GyvyfoaRZeXgZVZaglAc=; b=mZS3dTWWQ0Vu4KGrXq14bFAx+F/TPsGMVaqxW+0/NbsrzLlB6iuewKWOZRmRXVVGa2 i1L3XtUFZ0BUqYM9No8VaPp+UC3NXskOREjVuWfUNa8AmprlPtgEKdfOERWBruc4cEtM RqSOWzPsKU1TtYsuS9O1N+i/UC58Xo5DZOwSQdVayhG1mUqAroHZ+ZakfbIYVSVm8o7f uHbdxX44lvHZBAEWrDlFEEeaXdx3yigN1/tMNimFqqDgzlRVeLxkPZM7kW8/hWutFZ63 tEueMtyWbz5RaunX+xz1JIAdhQu7MBX4/Pnq6O3uGiUEPNc92KzJoO7NDr7pHdqtXZY8 O+8A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1740493793; x=1741098593; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=mTwR2zVSuvuPvN8kkvT0Wk3GyvyfoaRZeXgZVZaglAc=; b=u4Nu3DY2Gl0SWAJahXHwonPYp55G5aWLgu2bKB/ur67itkhCdLep8oVENKmsTq4J5E Gimb7n+4obY1ZlSiYLESvi82G/VHSZ71jBfzIpgzQ6oVVmfeCPSp4llKDW1ZmpPL38mT xtMUiWI9sk+mKSDQ3ObTC2fG6ppmvEDD3uNq0iCwvE8/5BLMbOOC3peG6uv7RNBoeCnH Kz0Yhaijig2nnXCVWj1Yyf3CHpyEwIqlZFhhcnQzj8w8AmIwV8HvBFBu2xgTgEi1ELyr oq+O5kzSe7YpyhAf66Z77TjxPCioDQZCgcDXcIvKPDAWUEAd18WzKMj3iLi8Qf62BY5e 2fcg== X-Gm-Message-State: AOJu0Yw+upHbUFpiNqNhIaPmwLF3TaKhGzx19KdUjfKdLLH9KQuDeGf3 FLuhuQsNJax+ofbtKU5j/Z4OEXU6YhVvhO9+3tuGZOyy8Gsvcwe4tALMUmCPGpr8YW6v0+PANRa vbyl8mquIRwrT3Uhgp75XDVvYUqY= X-Gm-Gg: ASbGncu5fMbylPU0n+obLyBex2Nt5xkq0Q8kwop7JHDSzKfgAQqgc2hV5VUPOyjB16q IoI1hiiMd1vu2ZvuszUs+wbNubuahIBxPoyMgOfIRtvvhwT5dcdNot87MAhlRj5bN1gk3XXxHGW tgYpbpIA== X-Google-Smtp-Source: AGHT+IHGz3i61V1nH+PeR/JFVNDJ8/k23p2T0ig6XCYUpz6i5FevDEACcWE2OOZRuIIjzaaRReulbxeSzQX1/IxwzSQ= X-Received: by 2002:a05:6870:63a1:b0:2ba:11dd:249d with SMTP id 586e51a60fabf-2bd50f3acabmr12528367fac.24.1740493793406; Tue, 25 Feb 2025 06:29:53 -0800 (PST) MIME-Version: 1.0 Received: by 2002:a05:6802:328f:b0:589:13f9:e937 with HTTP; Tue, 25 Feb 2025 06:29:52 -0800 (PST) In-Reply-To: <56861740492680@mail.yandex.ru> References: <56861740492680@mail.yandex.ru> From: "David G. Johnston" Date: Tue, 25 Feb 2025 07:29:52 -0700 X-Gm-Features: AWEUYZl4CovBP5XCS7rYEKdbT24vEumuty967fPPoHx9SjlmW2Ib6BdnerhY1MY Message-ID: Subject: Re: How to return seto records from seof record function? To: =?UTF-8?B?0J7Qu9C10LMg0KHQsNC80L7QudC70L7Qsg==?= Cc: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000004a37ac062ef84a98" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004a37ac062ef84a98 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tuesday, February 25, 2025, =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 > > 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 :=3D pg_background_launch(p_script); > > *RETURN* QUERY *SELECT* * *FROM* pg_background_result(l_id) *AS* (r recor= d > ); > > *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 wit= h > 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=E2=80=A6not 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. --0000000000004a37ac062ef84a98 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tuesday, February 25, 2025, =D0=9E=D0=BB=D0=B5=D0=B3 =D0=A1=D0=B0=D0=BC= =D0=BE=D0=B9=D0=BB=D0=BE=D0=B2 <splarv@y= a.ru> wrote:
Postgresql 17.2<= br>
How to return seto records from seof record function? I tried pg_bac= kground extension:
=C2=A0

CREATE OR<= /strong> REPLACE = FUNCTION public.autonomou= s (p_script text)

RETURNS SETOF record

LANGUAGE plpgsql<= /span>

VOLATILE ST= RICT PARALLEL UNSAFE

= AS $autonomous$

DECLARE

l_id integer;

BEGIN

l_id :=3D pg_background_= launch(p_script);

RETURN Q= UERY SELECT * FROM pg_background_result(l_id) AS (r r= ecord);

END;

$autonomous$;

=C2=A0

* FROM autonomous<= u style=3D"text-decoration:underline #ff0080 wavy">('SELECT= now()') AS (a timestamptz);

=C2=A0

SQL Error [42804]: ERROR: structure of query does not match= function result type
=C2=A0 Detail: Returned type record does no= t match expected type timestamp with time zone in column 1.
=C2= =A0 Where: SQL statement "SELECT * FROM pg_background_result(l_id) AS = (r record)"=C2= =A0
PL/pgSQL function auto= nomous(text) line 6 at RETURN QUERY

Interesting=E2=80=A6not 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.=C2=A0 Maybe you can con= vert the record result to jsonb and return that?

D= avid J.
=C2=A0
--0000000000004a37ac062ef84a98--