MIME-Version: 1.0
From: Mauricio Fernandez <mmauricio.fernandez@gmail.com>
Date: Tue, 21 Oct 2025 17:51:46 -0300
Message-ID: 
 <CAMdfv4XRZXZmZm3NWmjPe69kaAnkRTX_8Jq1qp+Pet9mD4eCaw@mail.gmail.com>
Subject: Script generation through psql
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e88f160641b15e7c"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMdfv4XRZXZmZm3NWmjPe69kaAnkRTX_8Jq1qp%2BPet9mD4eCaw%40mail.gmail.com>
Precedence: bulk

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

Hi community

I'm trying to generate an script file as the output from psql script been
called from linux bash, and I've two issues:

The bash call is like:

psql -U myusr-d mydb -f ./scr.sql > $UPD_FILE 2>&1

$ cat scr.sql
\set vl_id 19
\set ECHO none
\pset tuples_only on
\pset pager off

select script_drop from proceso_actualiza_fdw where id =3D :vl_id;

select script_import from proceso_actualiza_fdw where id =3D :vl_id;

\q

1.- The output file is like :
*Pager usage is off.*
 --                                                                  *+*
 -- BORRADO DE TABLAS MODIFICADAS                                    +

How can I eliminate "*Pager usage is off." *and the *"+"  *at the end of
each row?

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 $$;

select script_drop from proceso_actualiza_fdw where id =3D :*vl_id*;

select script_import from proceso_actualiza_fdw where id =3D :*vl_id*;

The script output file is the result set from the queries.

I've tried with \set myvar but this doesn't works

Thank you very much in advance

kind regards

Mauricio Fern=C3=A1ndez

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

<div dir=3D"ltr">Hi community=C2=A0<div><br></div><div>I&#39;m trying to ge=
nerate an script file as the output from psql script been called from linux=
 bash, and I&#39;ve two issues:<div><br></div><div>The bash call is like:</=
div><div><br></div><div>psql -U myusr-d mydb -f ./scr.sql &gt; $UPD_FILE 2&=
gt;&amp;1<br></div><div><br></div><div>$ cat scr.sql</div><div>\set vl_id 1=
9<br>\set ECHO none<br>\pset tuples_only on<br>\pset pager off<br><br>selec=
t script_drop from proceso_actualiza_fdw where id =3D :vl_id;<br><br>select=
 script_import from proceso_actualiza_fdw where id =3D :vl_id;<br><br>\q</d=
iv><div><br></div><div>1.- The output=C2=A0file is like :</div><div><b>Page=
r usage is off.</b><br>=C2=A0-- =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0<b>+</b><br>=C2=A0-- BORRADO DE TA=
BLAS MODIFICADAS =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0+</div=
><div><br></div><div>How can I eliminate &quot;<b>Pager usage is off.&quot;=
 </b>and the <b>&quot;+&quot;=C2=A0 </b>at the end of each row?</div><div><=
br></div><div>2. If in the psql script I want to pass variable values from =
an anonymous block to the rest of the script,=C2=A0how can=C2=A0I proceed?.=
 For example</div><div><br></div><div>DO $$<br>DECLARE<br>=C2=A0 vl_id inte=
ger;<br>BEGIN<br>=C2=A0-- vl_id is set inside the procedure<br>=C2=A0 call =
my_procedure(<b>vl_id</b>);<br>END $$;</div><div><br></div><div>select scri=
pt_drop from proceso_actualiza_fdw where id =3D :<b>vl_id</b>;<br><br>selec=
t script_import from proceso_actualiza_fdw where id =3D :<b>vl_id</b>;</div=
><div><br></div><div>The script output file is the result set=C2=A0from the=
 queries.</div><div><br></div><div>I&#39;ve tried=C2=A0with \set myvar but =
this doesn&#39;t works</div><div><br></div><div>Thank you very much in adva=
nce</div><div><br></div><div>kind regards</div><div><br></div><div>Mauricio=
 Fern=C3=A1ndez</div></div></div>

--000000000000e88f160641b15e7c--