Re: Script generation through psql

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: Mauricio Fernandez <mmauricio(dot)fernandez(at)gmail(dot)com>, Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Re: Script generation through psql
Date: 2025-10-22 03:57:51
Message-ID: bee5fc2e37c50a36abf795766754809e9592881b.camel@cybertec.at
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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:
>
> 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 = :vl_id;
>
> select script_import from proceso_actualiza_fdw where id = :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?

Rather than disabling the pager with a \pset command, set the PAGER environment
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=''
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, 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 = :vl_id;
>
> select script_import from proceso_actualiza_fdw where id = :vl_id;
>
> The script output file is the result set from the queries.
>
> I've tried with \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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Roland Müller 2025-10-22 04:23:45 Re: Script generation through psql
Previous Message Mauricio Fernandez 2025-10-21 20:51:46 Script generation through psql