Re: Script generation through psql - Mailing list pgsql-admin

Hi Laurenz, thanks a lot for the tips..

Using -Atq and PAGER="" the desired output was as expected

export PAGER=""
psql -Atq -U moodle -d lms4x -f ./kk.sql > $UPD_FILE 2>&1

But I'm still having problem with the other issue:

[postgres@maihue1 bin]$ ./actualiza_pg_ora_fdw.sh
21
psql:scr.sql:5: ERROR:  column "x_id" does not exist
LINE 1: ...lect script_drop from proceso_actualiza_fdw where id = x_id;
                                                                  ^
HINT:  Perhaps you meant to reference the column "proceso_actualiza_fdw.id".
psql:scr.sql:7: ERROR:  column "x_id" does not exist
LINE 1: ...ct script_import from proceso_actualiza_fdw where id = x_id;
                                                                  ^
HINT:  Perhaps you meant to reference the column "proceso_actualiza_fdw.id".

[postgres@maihue1 bin]$cat src.sql

\gset x_id
call actualiza_fdw(null);

select script_drop from proceso_actualiza_fdw where id = x_id;
select script_import from proceso_actualiza_fdw where id = x_id;
\q

I certainly know the variable value has been correctly assigned, 21 in this case, but I don't know how to use it in the rest of the script.

I've tried with :x_id and doesn't  works eather

kind regards

Mauricio Fernández

El mié, 22 oct 2025 a las 0:57, Laurenz Albe (<laurenz.albe@cybertec.at>) escribió:

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