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!
