| 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
| 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 |