Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vBXVH-000ImF-IP for pgsql-admin@arkaria.postgresql.org; Wed, 22 Oct 2025 11:59:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1vBXVG-00FkEC-1x for pgsql-admin@arkaria.postgresql.org; Wed, 22 Oct 2025 11:59:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vBXVF-00FkE2-LN for pgsql-admin@lists.postgresql.org; Wed, 22 Oct 2025 11:59:40 +0000 Received: from mail-vs1-xe31.google.com ([2607:f8b0:4864:20::e31]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vBXVC-003br1-0H for pgsql-admin@lists.postgresql.org; Wed, 22 Oct 2025 11:59:40 +0000 Received: by mail-vs1-xe31.google.com with SMTP id ada2fe7eead31-5a2b3bb803bso3977622137.0 for ; Wed, 22 Oct 2025 04:59:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1761134376; x=1761739176; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=uV2pFd8VCleCIUwgUovsqCnZQhOIJl7GxrseqEXFkro=; b=FMR423AZ9L6pj9gD02noO+KUE7oWhQ9o7aMioJQFauLUA7HxF2oslvjVPO4f8HoNQN LPFrguoSdHeuac+/izVppRxriVeIQYaTWqrcZvcP3qaWdiNfvLRhFOxit4/2JO5X42+M Z+v5UVdc0qZ6n+47c2w7EC5OFYf6ipCNI4AsV+657pGnXoHXfs0Wq2oNvy3/D589PzoZ sCJI2l6VusuNsCmafM83q9XkLWgeSKiX9PV3CyxkotsZS/C9uZerG3pmNDxL2Op9Af+A YyAgLSKKjlWgvFQySjW6ZRHzfmShGduQpO80jP3ScOOMRF/uGPvQKim4nsvjBRye+yVb 7b+Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761134376; x=1761739176; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=uV2pFd8VCleCIUwgUovsqCnZQhOIJl7GxrseqEXFkro=; b=KkRwuDglrCKE2RzSr1o/jjVtV8WNeea1sz5azDxlm51p10N1bIn1mXxwUG2vhj50K6 e1KOTW9EjYwAro1WRyYoGQ2QqVwfV1ZS/KtcEVJdFJWaloU3SB6WF/piQCXG4czDvaO+ wSmJRX3qdJzSqyeQesg0q6lotX9lNDW9+PSTa38oYWLx5Uu1dxilNW5JVtwngza35/4i TWL/KIP9/N2vLRFeZM7s8KBz5SEdKpo0LBPhkMkoTYngMhp4VEYOg9YsAAAhLEOvTSKq vcZmFUbIUofPFwgOCD3wIDNk80U2t7UzrVWulPlQtb0XfcrkZhumZM19SNTwijsDkQHc pHNA== X-Gm-Message-State: AOJu0YxSZ0/D2J7AMtOOJYixUmxMpAerYD9+voxqjDHkGMCP2ru1+p+R VJmdSeR8W2YuseZzpZpHBaOr+fTmKC5bqkO0ag6KBe++qDph1GmWc36QL0nomVgyd2ZtAwULtIH e1PKRn04qGZRnoemhcviqgZakoXQnDNc= X-Gm-Gg: ASbGnctrEV+EcWufk32aIiI25YnSdDCFWF2cHJSFZmsPMLnietnjVsFf84Tcycu81pT LoctJXtSIOdxBDnnlWXSQtiJL0qIzLTXPuxbuUutONEfZ/DwXe02fsw7OPGl9n1i5IO8X4zrsHa A2acK/ARXSh/OOpSO/4T0SU1gZNzbn7qoStc2DsAcLdCKDFVI2Up6EzVfPly2n/C0EHk/4p2CtT +8NcapoTmioR98fhe4rM8hQyneS6NeqR/hFf9owlGK+Z2+WAfNm5qXZBqpKXpGRr9kUR70RioG7 Uy5MFX80Q+3RL08esaZAL8lY/wamAe7K/JnXog== X-Google-Smtp-Source: AGHT+IGr4jd4QQqIvoUUKBn3ViraMSgvDq6OmeYQRZfReBX/QysWWSyg+3PrhJu7qwdpr9MI2CPdHYWsqoYvmjJmt0w= X-Received: by 2002:a05:6102:6209:10b0:5db:23dd:c85c with SMTP id ada2fe7eead31-5db23ddca10mr215356137.18.1761134376344; Wed, 22 Oct 2025 04:59:36 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Mauricio Fernandez Date: Wed, 22 Oct 2025 08:59:26 -0300 X-Gm-Features: AS18NWAWeyBLIaXVEiZm2zQ6U5Yyss1sWDj9sNUNKPatc5ScUdWPS6QwJ9XHfRE Message-ID: Subject: Re: Script generation through psql To: Laurenz Albe Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000e761410641be0c1b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e761410641be0c1b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Laurenz, thanks a lot for the tips.. Using -Atq and PAGER=3D"" the desired output was as expected export PAGER=3D"" 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 =3D 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 =3D 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 =3D x_id; select script_import from proceso_actualiza_fdw where id =3D 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=C3=A1ndez El mi=C3=A9, 22 oct 2025 a las 0:57, Laurenz Albe () escribi=C3=B3: > 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 =3D :vl_id; > > > > select script_import from proceso_actualiza_fdw where id =3D :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=3D'' > 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 exampl= e > > > > 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 =3D :vl_id; > > > > select script_import from proceso_actualiza_fdw where id =3D :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 th= e > procedure > and set its value to the return value of the procedure. > > Yours, > Laurenz Albe > --000000000000e761410641be0c1b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Laurenz, thanks a lot for the tips..

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

export PAGER=3D""
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: =C2=A0column "x_id" does not exist<= br>LINE 1: ...lect script_drop from proceso_actualiza_fdw where id =3D x_id= ;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 ^
HINT: =C2=A0Perhaps you meant to reference the column &q= uot;proceso_actualiza_fdw.id".
psql:scr.sql:7: ERROR: =C2=A0column "x_id" does not e= xist
LINE 1: ...ct script_import from proceso_actualiza_fdw where id =3D= x_id;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 ^
HINT: =C2=A0Perhaps you meant to reference the co= lumn "proceso_actualiza_fd= w.id".

[postg= res@maihue1 bin]$cat src.sql
= \gset x_id
call actualiza_fdw(null);

select script_drop from pro= ceso_actualiza_fdw where id =3D x_id;
select script_import from proceso_= actualiza_fdw where id =3D x_id;
\q

I certainly=C2=A0know = the variable value has been correctly assigned, 21 in this case, but I don&= #39;t know how to use it in the rest of the script.

I've tried with :x_id and doesn't=C2=A0 works eather
kind regards

Mauricio Fern=C3=A1ndez<= /div>

El mi=C3=A9, 22 oct 2025 a las 0:57, Laurenz Alb= e (<laurenz.albe@cybertec.at= >) escribi=C3=B3:
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 scri= pt 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 =3D :vl_id;
>
> select script_import from proceso_actualiza_fdw where id =3D :vl_id; >
> \q
>
> 1.- The output=C2=A0file is like :
> Pager usage is off.
> =C2=A0-- =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0+
> =C2=A0-- BORRADO DE TABLAS MODIFICADAS =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0+
>
> How can I eliminate "Pager usage is off." and the "+&qu= ot;=C2=A0 at the end of each row?

Rather than disabling the pager with a \pset command, set the PAGER environ= ment
variable to an empty string before calling "psql".

To get rid of the "+" at the end of the line, use unaligned outpu= t.
I use the options -A, -t and -q when calling "psql" from a shell = script.

So your shell script could look like

=C2=A0 PAGER=3D''
=C2=A0 psql -Atq -U ... -d ... -f ...

> 2. If in the psql script I want to pass variable values from an anonym= ous block to the rest of the script,=C2=A0how can=C2=A0I proceed?. For exam= ple
>
> DO $$
> DECLARE
> =C2=A0 vl_id integer;
> BEGIN
> =C2=A0-- vl_id is set inside the procedure
> =C2=A0 call my_procedure(vl_id);
> END $$;
>
> select script_drop from proceso_actualiza_fdw where id =3D :vl_id;
>
> select script_import from proceso_actualiza_fdw where id =3D :vl_id; >
> The script output file is the result set=C2=A0from the queries.
>
> I've tried=C2=A0with \set myvar but this doesn't works

You cannot grab any output from a DO statement.=C2=A0 I recommend that you = don't use it.

Try something like the following in your "psql" script:

=C2=A0 -- without a DO statement
=C2=A0 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
--000000000000e761410641be0c1b--