| 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.
| 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? |