public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ron Johnson <[email protected]>
To: PostgreSQL Users Mailing List <[email protected]>
Subject: Re: Combining metavariables and table names
Date: Sun, 28 Dec 2025 20:48:22 -0500
Message-ID: <CANzqJaA053wg3OiLm2412hzmkVLjS=8SJodx3a459mjcMmKKsQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

On Sun, Dec 28, 2025 at 8:39 PM H <[email protected]> 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_history"
> 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 to
> 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.

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

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: Combining metavariables and table names
  In-Reply-To: <CANzqJaA053wg3OiLm2412hzmkVLjS=8SJodx3a459mjcMmKKsQ@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