Running postgresql 16 on Rocky Linux 9 developing a multi-tenant application where tenants will be represented by
individualschemes.
I am using the temporal tables extension to save updated/deleted rows and am running into a problem. During the
developmentI am creating tables in an SQL files for testing. In my example, the SQL files also create a versioning
triggerfor 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
generatesthe error message "ERROR: schema ":s" does not exist" even though it does exist. I have tried different
variationsof ':s.test_history' such as :"s"'.test_history" and a number of other variations, none of which work for the
update/deletionof 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
argumentto a procedure.
Even though I gotten it to work, what would the correct usage be?
Thanks.
