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 1vBYBo-000RX8-Q3 for pgsql-admin@arkaria.postgresql.org; Wed, 22 Oct 2025 12:43:40 +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 1vBYBn-00GBih-FG for pgsql-admin@arkaria.postgresql.org; Wed, 22 Oct 2025 12:43:38 +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 1vBYBn-00GBiZ-2O for pgsql-admin@lists.postgresql.org; Wed, 22 Oct 2025 12:43:38 +0000 Received: from mail-vs1-xe2a.google.com ([2607:f8b0:4864:20::e2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vBYBj-003cCx-1j for pgsql-admin@lists.postgresql.org; Wed, 22 Oct 2025 12:43:37 +0000 Received: by mail-vs1-xe2a.google.com with SMTP id ada2fe7eead31-5aa6b7c085aso6183984137.2 for ; Wed, 22 Oct 2025 05:43:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1761137013; x=1761741813; 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=CmUcqiphtQIUckapbHFNs73ek6kNZ38HBMDuFjUze5Q=; b=mqMu40HkyANW+czyWsPdwHUpAAndjBv9Ac7kjR7JGp7ulcA2NCRCAod5xQSvbvnmJ5 kvYzG8YlfMjkndVFi2RRbYACqmckIvLtQKgw3hg00Lzq/khmD1cpFxMynSRARkWXZuF6 yIfFr8Uk2d03VwpUY9a0lGus1MCouU0OLSmx8qfvH62q35NqmSj/kjZcFV4MurirawF+ idXlNxneEt8vkhzyYIcrcHh+ev3VE3Fk9iuaby6QrqZ8dyZj6RHau7QY++HJ02ORvrWI L8RGwrAENS5UCflSH8EMmaktMRXZth9CrM/pJGrvb0xRhU9VFZpopCy03RIqgSiJDxjZ msSw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761137013; x=1761741813; 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=CmUcqiphtQIUckapbHFNs73ek6kNZ38HBMDuFjUze5Q=; b=LDK5yihSQ8YvYIs+luKdw8MRO4v8vQV1RqPKE1j0JIhxB2/UdODWOCraOnR0zxHZFo EYe3Vwv1MiCmG7xGT72ByOSwpy2Sf5hjUisnL5Tq6unKtOZuII2yOpmiUvsrhewdgfdH yuTt0Bp5S+V+ZyRZYJU0e7qWzBcyGsLuYO73lWJfira489govB3dx2m81Y6w84/9l+7b zg+adBrs3BA/QUIDpUzVHSOtIZZmNrjRWRZsW4+9HXhQhK/Uvt8ffn53GG4KrNC3zMWN /ckv4kh2n5IvCVa8nm+5CXmBKSHjINdlazXXQHMeaG+4NN6EWEVeXInt+v2yc2Fxs4VI T8pg== X-Gm-Message-State: AOJu0YxsOHQdL3wE3L2UBoXFkaml0PY6LGhaFLYuRmb2TtPkqtsa7vvb MA4qIWvc6njm5jnvlbyjAoEjj+76XkZpwTKYu1RJykrZZGumaIFPoiXoXqvijNv9ZpyLdic+oAc uLPjtaBlPG/+JfCV3rgbnw/dBrhFAm0zeda+M X-Gm-Gg: ASbGncu0tgde1G6U+sec0cbvhKvu2L0GR/Qy2kz5s0GmNO2GTM6lJugcsmFHVE0rN27 QPqiRiR061UAfVvS7vaAZWKpdedblhyQdrkTEF60nnZfBSWYVhzb49I9VLbCBPlkUetNKVYpJbH 4f9GALFWO5MSZofuTiGa3JqeWXlQYHo6zEmRr4MB9wAiZRbG365y7aEr/AVhLWwBe97VHKHADUr BUAVoavaJLKmVtjeBO79phOoRT/0J38YercaGaaVs8dABlmt5tTSVnOWDKZ/iPBT8rgDf4v4ko0 CZgEhuWKJNWiJQTb X-Google-Smtp-Source: AGHT+IFV7wu1uN0TY13m31Z7ztoOMd1pCnOwXrCfz6T3lTpz78D812MWC2l88p5vyvY0MBzaUzH9fROsC3qfwc+8SWw= X-Received: by 2002:a05:6102:54a1:b0:5db:1e80:7816 with SMTP id ada2fe7eead31-5db1e807cc4mr725199137.24.1761137013438; Wed, 22 Oct 2025 05:43:33 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Mauricio Fernandez Date: Wed, 22 Oct 2025 09:43:22 -0300 X-Gm-Features: AS18NWA4iBnqax6SimrudSfbY9eJ_svHvHAELjnm6aoC8YRRdr3wwPwP0zf7mRw Message-ID: Subject: Re: Script generation through psql To: Laurenz Albe Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000163c270641beaa8b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000163c270641beaa8b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Laurenz, forget my later mail....Know it works fine call actualiza_fdw(null) ;\gset 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 thank you very much kid regards Mauricio Fern=C3=A1ndez ~ El mi=C3=A9, 22 oct 2025 a las 8:59, Mauricio Fernandez (< mmauricio.fernandez@gmail.com>) escribi=C3=B3: > 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.i= d > ". > 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.i= d > ". > > *[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 examp= le >> > >> > 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 >> the procedure >> and set its value to the return value of the procedure. >> >> Yours, >> Laurenz Albe >> > --000000000000163c270641beaa8b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Laurenz, forget my later mail....Know it works fine
call actualiza_fdw(null) ;\gset

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

thank you very much

kid regards

<= /div>
Mauricio Fern=C3=A1ndez
~=C2=A0 =C2=A0 =C2=A0 =C2=A0

El mi=C3=A9, 22 oct 2025 a las 8:59, Mauricio Fernandez (= <mmauricio.fernandez@gm= ail.com>) escribi=C3=B3:
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_or= a_fdw.sh
21
psql:scr.sql:5: ERROR: =C2=A0column "x_id"= does not exist
LINE 1: ...lect script_drop from proceso_actualiza_fdw w= here 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 refer= ence the column "proceso_actualiza_fdw.id".
psql:scr.sql:7: ERROR: =C2= =A0column "x_id" does not exist
LINE 1: ...ct script_import fr= om 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=A0Pe= rhaps you meant to reference the column "proceso_actualiza_fdw.id".=

[postgres@maihue1 bin]$cat s= rc.sql
\gset x_id
call ac= tualiza_fdw(null);

select script_drop from proceso_actualiza_fdw whe= re 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'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 rega= rds

Mauricio Fern=C3=A1ndez

El mi=C3=A9, 22 o= ct 2025 a las 0:57, Laurenz Albe (<laurenz.albe@cybertec.at>) escribi=C3=B3:
On Tue, 2025-10-2= 1 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
--000000000000163c270641beaa8b--