Обсуждение: Script generation through psql

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 = :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?

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

Thank you very much in advance

kind regards

Mauricio Fernández

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

Hello,

the \copy command should be able to output only the results of some  query. This command is  client side. Thus \copy runs in psql and uses the server side COPY .

https://www.postgresql.org/docs/17/app-psql.html

BR

Roland 

Mauricio Fernandez <mmauricio.fernandez@gmail.com> ezt írta (időpont: 2025. okt. 21., K 23:52):

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 = :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?

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

Thank you very much in advance

kind regards

Mauricio Fernández

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

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’d have to do something like:

Execute format(‘set script.varname=%L’, value)

Within the DO block then you can do:

Select current_value(‘script.varname’) as psql_var \gexec

Outside of it.

A temporary table works too.

David J.