Combining metavariables and table names

From: H <agents(at)meddatainc(dot)com>
To: PostgreSQL Users Mailing List <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Combining metavariables and table names
Date: 2025-12-29 01:38:50
Message-ID: 8254F175-871C-4678-8D28-A67E09B099A6@meddatainc.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Thanks.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron Johnson 2025-12-29 01:48:22 Re: Combining metavariables and table names
Previous Message Ray O'Donnell 2025-12-27 16:14:52 Re: plpgsql: remove a field from a record?