public inbox for [email protected]  

help / color / mirror / Atom feed

From: carl clemens <[email protected]>
To: [email protected]
Subject: psql variable substitution in plpgsql loop
Date: Sun, 21 Jun 2026 17:38:25 +0000
Message-ID: <CALVj=tv3UD6hVXnqdZpGQmBFk9K9pFof+H1teMn6Gw4zx8a7Eg@mail.gmail.com> (raw)

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;

view thread (3+ messages)  latest in thread

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]
  Subject: Re: psql variable substitution in plpgsql loop
  In-Reply-To: <CALVj=tv3UD6hVXnqdZpGQmBFk9K9pFof+H1teMn6Gw4zx8a7Eg@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