public inbox for [email protected]  

help / color / mirror / Atom feed

From: Mauricio Fernandez <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: Re: Script generation through psql
Date: Wed, 22 Oct 2025 08:59:26 -0300
Message-ID: <CAMdfv4UL7_GWC8rN2--KSY0HWzhk8fUJnFSdrbnn1dp5CzzvEw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAMdfv4XRZXZmZm3NWmjPe69kaAnkRTX_8Jq1qp+Pet9mD4eCaw@mail.gmail.com>
	<[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
>

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Script generation through psql
  In-Reply-To: <CAMdfv4UL7_GWC8rN2--KSY0HWzhk8fUJnFSdrbnn1dp5CzzvEw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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