public inbox for [email protected]  

help / color / mirror / Atom feed

psql variable substitution in plpgsql loop
3+ messages / 2 participants
[nested] [flat]

* psql variable substitution in plpgsql loop
@ 2026-06-21 17:38  carl clemens <[email protected]>
  0 siblings, 1 reply; 3+ messages in thread

From: carl clemens @ 2026-06-21 17:38 UTC (permalink / raw)
  To: [email protected]

Hi,

psql fails to populate a table in variable substitution.
the variable being within a plpgsql "goto" loop looks suspicious

thank you

Linux neptune 6.1.0-42-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.159-1
(2025-12-30) x86_64 GNU/Linux

psql (PostgreSQL) 18.2

psql -d bench  -v ON_ERROR_STOP=1 --variable=SCHEMANAME=test2 <
create.sql  # success

create.sql
begin;
drop table if exists :SCHEMANAME.a_eod100k_s;

CREATE TABLE :SCHEMANAME.a_eod100k_s (
    ts timestamp without time zone NOT NULL,
    store integer NOT NULL,
    dept integer NOT NULL,
    category integer NOT NULL,
    class integer NOT NULL,
    in_stock integer
)
WITH (fillfactor='100');

ALTER TABLE ONLY :SCHEMANAME.a_eod100k_s
    ADD CONSTRAINT a_eod100k_s_pk PRIMARY KEY (ts, store, dept, category,
class);
commit;

psql -d bench  -v ON_ERROR_STOP=1 --variable=SCHEMANAME=test2 < aa.sql #
fail

ERROR:  42601: syntax error at or near ":"
LINE 18:           insert into :SCHEMANAME.a_eod100k_s values(l_ts, s..

aa.sql:
begin;
truncate test.a_eod100k_s;
do
$$
declare
  l_ts timestamp := '1970-01-01 00:00:00'::timestamp;
  l_rows int4 := 10;
begin
  --truncate test.a_eod100k_s;

  for store in 1..16
  loop
  <<bstart>>
    for dept in 1..32
    loop
      for category in 1..64
      loop
        for class in 1..32
        loop
          insert into :SCHEMANAME.a_eod100k_s values(l_ts, store, dept,
category, class, 10);
          l_rows := l_rows +1;
          exit bstart when l_rows > 100000;
        end loop;
      end loop;
    end loop;
    l_ts := l_ts +'1 day'::interval;
  end loop;
end
$$;
commit;


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: psql variable substitution in plpgsql loop
@ 2026-06-21 18:17  David G. Johnston <[email protected]>
  parent: carl clemens <[email protected]>
  0 siblings, 1 reply; 3+ messages in thread

From: David G. Johnston @ 2026-06-21 18:17 UTC (permalink / raw)
  To: carl clemens <[email protected]>; +Cc: [email protected] <[email protected]>

On Sunday, June 21, 2026, carl clemens <[email protected]> wrote:
>
> psql fails to populate a table in variable substitution.
> the variable being within a plpgsql "goto" loop looks suspicious
>

psql will not modify string literals when performing variable substitution.

David J.


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: psql variable substitution in plpgsql loop
@ 2026-06-21 19:27  carl clemens <[email protected]>
  parent: David G. Johnston <[email protected]>
  0 siblings, 0 replies; 3+ messages in thread

From: carl clemens @ 2026-06-21 19:27 UTC (permalink / raw)
  To: David G. Johnston <[email protected]>; +Cc: [email protected] <[email protected]>

ok, no problem.

Thanks

On Sun, Jun 21, 2026 at 6:17 PM David G. Johnston <
[email protected]> wrote:

> On Sunday, June 21, 2026, carl clemens <[email protected]> wrote:
>>
>> psql fails to populate a table in variable substitution.
>> the variable being within a plpgsql "goto" loop looks suspicious
>>
>
> psql will not modify string literals when performing variable substitution.
>
> David J.
>
>


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2026-06-21 19:27 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-21 17:38 psql variable substitution in plpgsql loop carl clemens <[email protected]>
2026-06-21 18:17 ` David G. Johnston <[email protected]>
2026-06-21 19:27   ` carl clemens <[email protected]>

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