MIME-Version: 1.0
In-Reply-To: 
 <CAMdfv4XRZXZmZm3NWmjPe69kaAnkRTX_8Jq1qp+Pet9mD4eCaw@mail.gmail.com>
References: 
 <CAMdfv4XRZXZmZm3NWmjPe69kaAnkRTX_8Jq1qp+Pet9mD4eCaw@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Wed, 22 Oct 2025 06:54:13 -0400
Message-ID: 
 <CAKFQuwbL+S0kQiSPt_SzXbOYf8FtrPLLTnUUHq_y1ooyXOffDQ@mail.gmail.com>
Subject: Re: Script generation through psql
To: Mauricio Fernandez <mmauricio.fernandez@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001a11d30641bd23fc"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwbL%2BS0kQiSPt_SzXbOYf8FtrPLLTnUUHq_y1ooyXOffDQ%40mail.gmail.com>
Precedence: bulk

--0000000000001a11d30641bd23fc
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tuesday, October 21, 2025, Mauricio Fernandez <
mmauricio.fernandez@gmail.com> wrote:
>
>
> 2. If in the psql script I want to pass variable values from an anonymous
> block to the rest of the script, how can I proceed?. For example
>
> DO $$
> DECLARE
>   vl_id integer;
> BEGIN
>  -- vl_id is set inside the procedure
>   call my_procedure(*vl_id*);
> END $$;
>

You=E2=80=99d have to do something like:

Execute format(=E2=80=98set script.varname=3D%L=E2=80=99, value)

Within the DO block then you can do:

Select current_value(=E2=80=98script.varname=E2=80=99) as psql_var \gexec

Outside of it.

A temporary table works too.

David J.

--0000000000001a11d30641bd23fc
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tuesday, October 21, 2025, Mauricio Fernandez &lt;<a href=3D"mailto:mmau=
ricio.fernandez@gmail.com">mmauricio.fernandez@gmail.com</a>&gt; wrote:<blo=
ckquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #c=
cc solid;padding-left:1ex"><div dir=3D"ltr"><div><div><br></div><div>2. If =
in the psql script I want to pass variable values from an anonymous block t=
o the rest of the script,=C2=A0how can=C2=A0I proceed?. For example</div><d=
iv><br></div><div>DO $$<br>DECLARE<br>=C2=A0 vl_id integer;<br>BEGIN<br>=C2=
=A0-- vl_id is set inside the procedure<br>=C2=A0 call my_procedure(<b>vl_i=
d</b>);<br>END $$;</div></div></div></blockquote><div><br></div><div>You=E2=
=80=99d have to do something like:</div><div><br></div><div>Execute format(=
=E2=80=98set script.varname=3D%L=E2=80=99, value)</div><div><br></div><div>=
Within the DO block then you can do:</div><div><br></div><div>Select curren=
t_value(=E2=80=98script.varname=E2=80=99) as psql_var \gexec</div><div><br>=
</div><div>Outside of it.</div><div><br></div><div>A temporary table works =
too.</div><div><br></div><div>David J.</div><div><br></div>

--0000000000001a11d30641bd23fc--