Subject: psql variable substitution in plpgsql loop

From: carl clemens <[email-masked]>
Date: Sun, 21 Jun 2026 17:38:25 +0000

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;

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 21 Jun 2026 11:17:13 -0700

> the variable being within a plpgsql "goto" loop looks suspicious

psql will not modify string literals when performing variable substitution.

David J.

---

From: carl clemens <[email-masked]>
Date: Sun, 21 Jun 2026 19:27:20 +0000

ok, no problem.

Thanks