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.96) (envelope-from ) id 1wbM82-002Tqw-1W for pgsql-bugs@arkaria.postgresql.org; Sun, 21 Jun 2026 17:38:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wbM81-003bu6-0R for pgsql-bugs@arkaria.postgresql.org; Sun, 21 Jun 2026 17:38:41 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wbM80-003bty-2R for pgsql-bugs@lists.postgresql.org; Sun, 21 Jun 2026 17:38:40 +0000 Received: from mail-dy1-x1335.google.com ([2607:f8b0:4864:20::1335]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wbM7z-00000001TFL-0Svf for pgsql-bugs@lists.postgresql.org; Sun, 21 Jun 2026 17:38:39 +0000 Received: by mail-dy1-x1335.google.com with SMTP id 5a478bee46e88-30c23abc62eso1173286eec.1 for ; Sun, 21 Jun 2026 10:38:38 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1782063518; cv=none; d=google.com; s=arc-20240605; b=YX3RHwYwYdKEDYxkeW/GbdmynmC14ch/6CAmUuvAO/gA0LrkI/aaMkQtHVGYNw2vBR Vnre5y38f1pTVGwPI8HQ2AEDNgZxGn0vSPiCczqxcYpQTanVbZ8Ga4bZwodHfnFlHYKs aT64kePqnmQcvub3AD7eryx5fDtFLA9lfithVMvt72Zkj7Hd8yldRyQW5n6mA1ZzzlRA N/CNpOn/Xz+7s8KhYD3uouzW0U+OlLd0X2KzEjmZ3ifu4fPiGzC1WSVOFD2LqP5jGU/C +yKjAPSPw8o/pD+EyfduxWcSSNBvmflG6e9zpQeL7GPTNWrJ6QXQYbyf3r47pYUZTU2f F3PQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=to:subject:message-id:date:from:mime-version:dkim-signature; bh=bvqbChQrilYfB22KdOO2PUbamV8aMSV9O3nSse8OhSs=; fh=/gQe77b11iMZdcPj/nJr/Ghqi6rQp5FPrPbdO93bmOA=; b=ILYY7DafoGvYOvBDugxhME0YIKxbs81YfvFSTP6UrgXS7ZaEZFuLLcfeT/LP4areCt I4uA6klZNsGdqIcRlz5j58v+s7wowCgKiCeee24Pj23Np1yi58SgDc5hon4fF1a6Vfqt GyxUF8i1dClzsGT7wiU/zRaKVFRUADPfLMj85IDRwyPWo6iLpN0bVvXZozBGDt4YnHwr IcaSEzWrRCKfTcuM2vEdyN1D9tJqgFbVQZfLWvt1Vo+PKDqCpCQmMoxknZLNb+Tk6mol lPzZCOEVew6ppsPm/vvZcw4UtGk5D2SQkfx5Byn84FPLN7Cm9Yyu2uTTfZFQIUwTHJiR RfRA==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1782063518; x=1782668318; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=bvqbChQrilYfB22KdOO2PUbamV8aMSV9O3nSse8OhSs=; b=T6vWOpExSwBMTerENtXtKvKq6s9dtchZuU8edwKQVUorjfUoZYQWlumqnBYLagm06T /nwiGS3udSnt2LO8biHKwf1lJ/vsbJIq0LTtHuD5yLjM8DjndSqrNXT9aiSTpp//7Tdc yHHa8kVPPP5WvqrJmXns1VMYFstj3M+A9Y97DoE/g7yvMjPrzxEfcUrjkxpSW/dqqkJS 2Rz5y4sALqCLwqBneZT/zHVIcNme8GYknnhoDuqMgJtSuQaSqBTNfo46dCd/C26bE2U4 sjdvi+VRp0f9Z7cdQiw2NLqrAtcsmHqUIfEVCd1u5C0bcagALjKhkLnxBczpWDmXKLJh 132g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1782063518; x=1782668318; h=to:subject:message-id:date:from:mime-version:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=bvqbChQrilYfB22KdOO2PUbamV8aMSV9O3nSse8OhSs=; b=CsBRhnN/lnRr8c4bW2jz0xjX3X2IUKLfIQkdobSQki424BTLCvn7a/E3/S2EqZ9B69 hUioTClwHrtGB4iVqTRfcNVUjLYX6eoqOtCcoe7BCUa3zLNDkQW6CwU2tkk67OUp3rFI SG20aDRIM8gthMDvG6RzKGgcv0cnWPJ7tCszBpV1auphE5sqZW6gyf2TS8qUovDAqC+2 bpJcOieplO/MNMPGCw/g8MRESEhUUHXdM0Bbr7p5VPtrrCpl00Slrrog6w8JuNvRkd33 j0kF6RGz8CZBw6I0Ol7qVaCC/oOZpNtIx/pOHTWvAneaFvLw+4QqRDUNHn0r1lZ7cxKe nfBA== X-Gm-Message-State: AOJu0Yyz08Iiitzhj4eNpV5AsOAeZqawmbsjMww2SZGoC06wGnmwcIHk iZ+2n6AD9OnIA70AOmhCkaS8473PiGcdhdS6KBO6OARVR0ysFFsobAnRpNY7Xbp78vHW33vNN/D Q624LmMZifJ3paA9iexg9y1elS2UBM0J/Zbbe X-Gm-Gg: AfdE7cnTHE5BMVAA4WisyzonhsCP3W26Za5YYj/pHjs8C2Awo2zHKisIQSd60Z6SXy3 9/2l3aUcdZ3pIZNqZL6Fj9C0Gf7PnlY46u91CpAJNXPkbV86wgO+9Ip0471bK20tcW/wI/EdOQa KPmNC3vHDv6e2g5cMf3nLK5HcM8FLXp5HcSaidEDuy/UvV9CG1lI2eth198z6LqplVxECj0/oZ2 2bOS6CiNW93RSuJUuIquNsYE2vZCA61mRC/AqB0xLfYERFC8su0DPLfwaEX7CEgdrQFJ71o X-Received: by 2002:a05:7300:80ca:b0:30b:f3b8:3e7f with SMTP id 5a478bee46e88-30c06fb6af6mr8104330eec.1.1782063517616; Sun, 21 Jun 2026 10:38:37 -0700 (PDT) MIME-Version: 1.0 From: carl clemens Date: Sun, 21 Jun 2026 17:38:25 +0000 X-Gm-Features: AVVi8Cf9i-o2bftJFoposj76PFctuqsmXGt02A9nqbP7WFMAqROrzFXatbFV6_c Message-ID: Subject: psql variable substitution in plpgsql loop To: pgsql-bugs@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000ef4e880654c6fef6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ef4e880654c6fef6 Content-Type: text/plain; charset="UTF-8" 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 <> 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; --000000000000ef4e880654c6fef6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,

psql fails to populate a table i= n variable substitution.
the variable being within a plpgsql "goto&= quot; loop looks=C2=A0suspicious=C2=A0

thank you=C2=A0

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 =C2= =A0-v ON_ERROR_STOP=3D1 --variable=3DSCHEMANAME=3Dtest2 <=C2=A0 create.s= ql=C2=A0 # success

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

CREATE TABLE :SCHEMANAME.a_eod100k_s (
= =C2=A0 =C2=A0 ts timestamp without time zone NOT NULL,
=C2=A0 =C2=A0 sto= re integer NOT NULL,
=C2=A0 =C2=A0 dept integer NOT NULL,
=C2=A0 =C2= =A0 category integer NOT NULL,
=C2=A0 =C2=A0 class integer NOT NULL,
= =C2=A0 =C2=A0 in_stock integer
)
WITH (fillfactor=3D'100');
ALTER TABLE ONLY :SCHEMANAME.a_eod100k_s
=C2=A0 =C2=A0 ADD CONSTRA= INT a_eod100k_s_pk PRIMARY KEY (ts, store, dept, category, class);
com= mit;

psql -d bench =C2=A0-v ON_ERROR_STOP=3D1 --variable= =3DSCHEMANAME=3Dtest2 < aa.sql # fail

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

aa.sql:
= begin;
truncate test.a_eod100k_s;
do
$$
declare
=C2=A0 l_ts = timestamp :=3D '1970-01-01 00:00:00'::timestamp;
=C2=A0 l_rows i= nt4 :=3D 10;
begin
=C2=A0 --truncate test.a_eod100k_s;

=C2=A0 = for store in 1..16
=C2=A0 loop
=C2=A0 <<bstart>>
=C2= =A0 =C2=A0 for dept in 1..32
=C2=A0 =C2=A0 loop
=C2=A0 =C2=A0 =C2=A0 = for category in 1..64
=C2=A0 =C2=A0 =C2=A0 loop
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 for class in 1..32
=C2=A0 =C2=A0 =C2=A0 =C2=A0 loop
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 insert into :SCHEMANAME.a_eod100k_s values(l_ts, s= tore, dept, category, class, 10);
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 l_r= ows :=3D l_rows +1;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 exit bstart when = l_rows > 100000;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 end loop;
=C2=A0 =C2= =A0 =C2=A0 end loop;
=C2=A0 =C2=A0 end loop;
=C2=A0 =C2=A0 l_ts :=3D = l_ts +'1 day'::interval;
=C2=A0 end loop;
end
$$;
com= mit;

--000000000000ef4e880654c6fef6--