public inbox for [email protected]  

help / color / mirror / Atom feed

Script generation through psql
8+ messages / 4 participants
[nested] [flat]

* Script generation through psql
@ 2025-10-21 20:51  Mauricio Fernandez <[email protected]>
  0 siblings, 3 replies; 8+ messages in thread

From: Mauricio Fernandez @ 2025-10-21 20:51 UTC (permalink / raw)
  To: Pgsql-admin <[email protected]>

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


^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Script generation through psql
@ 2025-10-22 03:57  Laurenz Albe <[email protected]>
  parent: Mauricio Fernandez <[email protected]>
  2 siblings, 1 reply; 8+ messages in thread

From: Laurenz Albe @ 2025-10-22 03:57 UTC (permalink / raw)
  To: Mauricio Fernandez <[email protected]>; Pgsql-admin <[email protected]>

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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Script generation through psql
@ 2025-10-22 04:23  Roland Müller <[email protected]>
  parent: Mauricio Fernandez <[email protected]>
  2 siblings, 1 reply; 8+ messages in thread

From: Roland Müller @ 2025-10-22 04:23 UTC (permalink / raw)
  To: Mauricio Fernandez <[email protected]>; Pgsql-admin <[email protected]>

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 <[email protected]> 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
>


^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Script generation through psql
@ 2025-10-22 10:54  David G. Johnston <[email protected]>
  parent: Mauricio Fernandez <[email protected]>
  2 siblings, 1 reply; 8+ messages in thread

From: David G. Johnston @ 2025-10-22 10:54 UTC (permalink / raw)
  To: Mauricio Fernandez <[email protected]>; +Cc: Pgsql-admin <[email protected]>

On Tuesday, October 21, 2025, Mauricio Fernandez <
[email protected]> 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.


^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Script generation through psql
@ 2025-10-22 11:59  Mauricio Fernandez <[email protected]>
  parent: Laurenz Albe <[email protected]>
  0 siblings, 1 reply; 8+ messages in thread

From: Mauricio Fernandez @ 2025-10-22 11:59 UTC (permalink / raw)
  To: Laurenz Albe <[email protected]>; +Cc: Pgsql-admin <[email protected]>

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 (<[email protected]>)
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
>


^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Script generation through psql
@ 2025-10-22 12:03  Mauricio Fernandez <[email protected]>
  parent: Roland Müller <[email protected]>
  0 siblings, 0 replies; 8+ messages in thread

From: Mauricio Fernandez @ 2025-10-22 12:03 UTC (permalink / raw)
  To: Roland Müller <[email protected]>; +Cc: Pgsql-admin <[email protected]>

Hi Roland, thanks for the answer, I will investigates de \copy command. For
know, I could resolve with the following call in the bash script:

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

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

regards Mauricio Fernández

El mié, 22 oct 2025 a las 1:23, Roland Müller (<[email protected]>) escribió:

> 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 <[email protected]> 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
>>
>


^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Script generation through psql
@ 2025-10-22 12:43  Mauricio Fernandez <[email protected]>
  parent: Mauricio Fernandez <[email protected]>
  0 siblings, 0 replies; 8+ messages in thread

From: Mauricio Fernandez @ 2025-10-22 12:43 UTC (permalink / raw)
  To: Laurenz Albe <[email protected]>; +Cc: Pgsql-admin <[email protected]>

Laurenz, forget my later mail....Know it works fine

call actualiza_fdw(null) ;\gset

select script_drop from proceso_actualiza_fdw where id = :x_id;

select script_import from proceso_actualiza_fdw where id = :x_id;

\q

thank you very much

kid regards

Mauricio Fernández
~

El mié, 22 oct 2025 a las 8:59, Mauricio Fernandez (<
[email protected]>) escribió:

> 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 (<[email protected]>)
> 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
>>
>


^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Script generation through psql
@ 2025-10-22 12:45  Mauricio Fernandez <[email protected]>
  parent: David G. Johnston <[email protected]>
  0 siblings, 0 replies; 8+ messages in thread

From: Mauricio Fernandez @ 2025-10-22 12:45 UTC (permalink / raw)
  To: David G. Johnston <[email protected]>; +Cc: Pgsql-admin <[email protected]>

Hi David, thanks for answer..I could resolve my issue doing the following
in the script

call actualiza_fdw(null) ;\gset

select script_drop from proceso_actualiza_fdw where id = :x_id;

select script_import from proceso_actualiza_fdw where id = :x_id;

\q

The procedure declaration is: actualiza_fdw(OUT x_id integer)

regards

Mauricio Fernández

El mié, 22 oct 2025 a las 7:54, David G. Johnston (<
[email protected]>) escribió:

> On Tuesday, October 21, 2025, Mauricio Fernandez <
> [email protected]> 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.
>
>


^ permalink  raw  reply  [nested|flat] 8+ messages in thread

end of thread, other threads:[~2025-10-22 12:45 UTC | newest]

Thread overview: 8+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-21 20:51 Script generation through psql Mauricio Fernandez <[email protected]>
2025-10-22 03:57 ` Laurenz Albe <[email protected]>
2025-10-22 11:59   ` Mauricio Fernandez <[email protected]>
2025-10-22 12:43     ` Mauricio Fernandez <[email protected]>
2025-10-22 04:23 ` Roland Müller <[email protected]>
2025-10-22 12:03   ` Mauricio Fernandez <[email protected]>
2025-10-22 10:54 ` David G. Johnston <[email protected]>
2025-10-22 12:45   ` Mauricio Fernandez <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox