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.
