public inbox for [email protected]
help / color / mirror / Atom feedFrom: 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 09:43:22 -0300
Message-ID: <CAMdfv4WD43rDffDp9-gz+DPg0q-SjmnTbihiwZ2BXwewF_XBOw@mail.gmail.com> (raw)
In-Reply-To: <CAMdfv4UL7_GWC8rN2--KSY0HWzhk8fUJnFSdrbnn1dp5CzzvEw@mail.gmail.com>
References: <CAMdfv4XRZXZmZm3NWmjPe69kaAnkRTX_8Jq1qp+Pet9mD4eCaw@mail.gmail.com>
<[email protected]>
<CAMdfv4UL7_GWC8rN2--KSY0HWzhk8fUJnFSdrbnn1dp5CzzvEw@mail.gmail.com>
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
>>
>
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: <CAMdfv4WD43rDffDp9-gz+DPg0q-SjmnTbihiwZ2BXwewF_XBOw@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