Message-ID: <bee5fc2e37c50a36abf795766754809e9592881b.camel@cybertec.at>
Subject: Re: Script generation through psql
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Mauricio Fernandez <mmauricio.fernandez@gmail.com>, Pgsql-admin
	 <pgsql-admin@lists.postgresql.org>
Date: Wed, 22 Oct 2025 06:57:51 +0300
In-Reply-To: 
 <CAMdfv4XRZXZmZm3NWmjPe69kaAnkRTX_8Jq1qp+Pet9mD4eCaw@mail.gmail.com>
References: 
	<CAMdfv4XRZXZmZm3NWmjPe69kaAnkRTX_8Jq1qp+Pet9mD4eCaw@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/bee5fc2e37c50a36abf795766754809e9592881b.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-10-21 at 17:51 -0300, Mauricio Fernandez wrote:
> I'm trying to generate an script file as the output from psql script been=
 called from linux bash, and I've two issues:
>=20
> The bash call is like:
>=20
> psql -U myusr-d mydb -f ./scr.sql > $UPD_FILE 2>&1
>=20
> $ cat scr.sql
> \set vl_id 19
> \set ECHO none
> \pset tuples_only on
> \pset pager off
>=20
> select script_drop from proceso_actualiza_fdw where id =3D :vl_id;
>=20
> select script_import from proceso_actualiza_fdw where id =3D :vl_id;
>=20
> \q
>=20
> 1.- The output=C2=A0file is like :
> Pager usage is off.
> =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+
> =C2=A0-- BORRADO DE TABLAS 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+
>=20
> How can I eliminate "Pager usage is off." and the "+"=C2=A0 at the end of=
 each row?

Rather than disabling the pager with a \pset command, set the PAGER environ=
ment
variable to an empty string before calling "psql".

To get rid of the "+" at the end of the line, use unaligned output.
I use the options -A, -t and -q when calling "psql" from a shell script.

So your shell script could look like

  PAGER=3D''
  psql -Atq -U ... -d ... -f ...

> 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
>=20
> DO $$
> DECLARE
> =C2=A0 vl_id integer;
> BEGIN
> =C2=A0-- vl_id is set inside the procedure
> =C2=A0 call my_procedure(vl_id);
> END $$;
>=20
> select script_drop from proceso_actualiza_fdw where id =3D :vl_id;
>=20
> select script_import from proceso_actualiza_fdw where id =3D :vl_id;
>=20
> The script output file is the result set=C2=A0from the queries.
>=20
> I've tried=C2=A0with \set myvar but this doesn't works

You cannot grab any output from a DO statement.  I recommend that you don't=
 use it.

Try something like the following in your "psql" script:

  -- without a DO statement
  CALL my_procedure(NULL) \gset

That will define a variable that has the same name as the parameter of the =
procedure
and set its value to the return value of the procedure.

Yours,
Laurenz Albe