MIME-Version: 1.0
References: <8254F175-871C-4678-8D28-A67E09B099A6@meddatainc.com>
In-Reply-To: <8254F175-871C-4678-8D28-A67E09B099A6@meddatainc.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Sun, 28 Dec 2025 20:48:22 -0500
Message-ID: <CANzqJaA053wg3OiLm2412hzmkVLjS=8SJodx3a459mjcMmKKsQ@mail.gmail.com>
Subject: Re: Combining metavariables and table names
To: PostgreSQL Users Mailing List <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000d40af906470d7049"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaA053wg3OiLm2412hzmkVLjS%3D8SJodx3a459mjcMmKKsQ%40mail.gmail.com>
Precedence: bulk

--000000000000d40af906470d7049
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sun, Dec 28, 2025 at 8:39=E2=80=AFPM H <agents@meddatainc.com> wrote:

> Running postgresql 16 on Rocky Linux 9 developing a multi-tenant
> application where tenants will be represented by individual schemes.
>
> I am using the temporal tables extension to save updated/deleted rows and
> am running into a problem. During the development I am creating tables in
> an SQL files for testing. In my example, the SQL files also create a
> versioning trigger for the example table test using test_history to store
> updated/deleted rows using the following statements:
>
> Metavariable to store schema:
>
> \set s t
>
> ...
>
> CREATE OR REPLACE TRIGGER versioning_trigger
>     BEFORE INSERT OR DELETE OR UPDATE
>     ON :s.test
>     FOR EACH ROW
>     EXECUTE PROCEDURE public.versioning('sys_period', ':s.test_history',
> 'true');
>
> Creating the tables works as expected, as does inserting data into them.
> However, updating/deleting rows the following generates the error message
> "ERROR:  schema ":s" does not exist" even though it does exist. I have
> tried different variations of ':s.test_history' such as :"s"'.test_histor=
y"
> and a number of other variations, none of which work for the
> update/deletion of a row.
>
> The only way I have gotten it to work is to add the following statement t=
o
> the SQL file creating tables:
>
> \set stest :s'.test_history'
>
> and then to reference it like:
>
> CREATE OR REPLACE TRIGGER versioning_trigger
>     BEFORE INSERT OR DELETE OR UPDATE
>     ON :s.test
>     FOR EACH ROW
>     EXECUTE PROCEDURE public.versioning('sys_period', :"stest", 'true')
>
> Note the placement of the colon and the use of trouble quotes.
>
> I have a feeling I might be missing how to use the combination of a
> metavariable and a table name when used in an argument to a procedure.
>
> Even though I gotten it to work, what would the correct usage be?
>

If you can't get it to work the way you want it to, there's always bash
string variable substitution.  I use that extensively.  The benefit is
keeping all the code in one file instead of spreading it across (possibly
multiple) .sql files in addition to the shell script.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--000000000000d40af906470d7049
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Sun, Dec 28, 2025 at 8:39=E2=80=AFPM H=
 &lt;<a href=3D"mailto:agents@meddatainc.com">agents@meddatainc.com</a>&gt;=
 wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px soli=
d rgb(204,204,204);padding-left:1ex">Running postgresql 16 on Rocky Linux 9=
 developing a multi-tenant application where tenants will be represented by=
 individual schemes.<br>
<br>
I am using the temporal tables extension to save updated/deleted rows and a=
m running into a problem. During the development I am creating tables in an=
 SQL files for testing. In my example, the SQL files also create a versioni=
ng trigger for the example table test using test_history to store updated/d=
eleted rows using the following statements:<br>
<br>
Metavariable to store schema:<br>
<br>
\set s t<br>
<br>
...<br>
<br>
CREATE OR REPLACE TRIGGER versioning_trigger<br>
=C2=A0 =C2=A0 BEFORE INSERT OR DELETE OR UPDATE<br>
=C2=A0 =C2=A0 ON :s.test<br>
=C2=A0 =C2=A0 FOR EACH ROW<br>
=C2=A0 =C2=A0 EXECUTE PROCEDURE public.versioning(&#39;sys_period&#39;, =
9;:s.test_history&#39;, &#39;true&#39;);<br>
<br>
Creating the tables works as expected, as does inserting data into them. Ho=
wever, updating/deleting rows the following generates the error message &qu=
ot;ERROR:=C2=A0 schema &quot;:s&quot; does not exist&quot; even though it d=
oes exist. I have tried different variations of &#39;:s.test_history&#39; s=
uch as :&quot;s&quot;&#39;.test_history&quot; and a number of other variati=
ons, none of which work for the update/deletion of a row.<br>
<br>
The only way I have gotten it to work is to add the following statement to =
the SQL file creating tables:<br>
<br>
\set stest :s&#39;.test_history&#39;<br>
<br>
and then to reference it like:<br>
<br>
CREATE OR REPLACE TRIGGER versioning_trigger<br>
=C2=A0 =C2=A0 BEFORE INSERT OR DELETE OR UPDATE<br>
=C2=A0 =C2=A0 ON :s.test<br>
=C2=A0 =C2=A0 FOR EACH ROW<br>
=C2=A0 =C2=A0 EXECUTE PROCEDURE public.versioning(&#39;sys_period&#39;, :&q=
uot;stest&quot;, &#39;true&#39;)<br>
<br>
Note the placement of the colon and the use of trouble quotes.<br>
<br>
I have a feeling I might be missing how to use the combination of a metavar=
iable and a table name when used in an argument to a procedure.<br>
<br>
Even though I gotten it to work, what would the correct usage be?<br></bloc=
kquote><div><br></div><div>If you can&#39;t get it to work the way you want=
 it to, there&#39;s always bash string variable substitution.=C2=A0 I use t=
hat extensively.=C2=A0 The benefit is keeping all the code in one file inst=
ead of spreading it across (possibly multiple) .sql files in addition to th=
e shell script.</div><div><br></div></div><span class=3D"gmail_signature_pr=
efix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"=
ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&=
#39;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div>=
</div></div></div>

--000000000000d40af906470d7049--