Combining metavariables and table names
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.
On Sun, Dec 28, 2025 at 8:39 PM H <agents@meddatainc.com> 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!
On Sunday, December 28, 2025, H <agents@meddatainc.com> wrote:
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.
No, what you are doing is impossible if you limit yourself to direct SQL
command syntax writing.
Even though I gotten it to work, what would the correct usage be?
I’d probably do something like:
versioning(…, format(‘%I.%I’, :’s’, ‘test_history’), …)
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Sunday, December 28, 2025, H <agents@meddatainc.com> wrote:
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.
No, what you are doing is impossible if you limit yourself to direct SQL
command syntax writing.
Yeah :-(
I’d probably do something like:
versioning(…, format(‘%I.%I’, :’s’, ‘test_history’), …)
The reason this is hard is that we don't support expressions in
CREATE TRIGGER, only simple literals. So any such processing would
have to be done in the client-side code that is sending the command,
and I don't think psql's variable-substitution ability is quite up
to the job.
You might be able to make it work through the hacky method of
supplying the schema name and table name as separate trigger
arguments.
Another idea could be to construct the desired string as a SELECT
result, and then use \gexec. There's a few too many moving parts
in that for my taste, but maybe it could work.
regards, tom lane
On December 28, 2025 8:53:50 PM EST, "David G. Johnston" <david.g.johnston@gmail.com> wrote:
On Sunday, December 28, 2025, H <agents@meddatainc.com> wrote:
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 aprocedure.
No, what you are doing is impossible if you limit yourself to direct
SQL
command syntax writing.Even though I gotten it to work, what would the correct usage be?
I’d probably do something like:
versioning(…, format(‘%I.%I’, :’s’, ‘test_history’), …)
David J.
Just tried your suggestion to use format() but it resulted in a 'syntax error at or near "("'
Seems I am stuck using the approach I found to work, ie. generating a string constant in another meta variable.
On December 28, 2025 10:41:19 PM EST, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Sunday, December 28, 2025, H <agents@meddatainc.com> wrote:
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 aprocedure.
No, what you are doing is impossible if you limit yourself to direct
SQL
command syntax writing.
Yeah :-(
I’d probably do something like:
versioning(…, format(‘%I.%I’, :’s’, ‘test_history’), …)The reason this is hard is that we don't support expressions in
CREATE TRIGGER, only simple literals. So any such processing would
have to be done in the client-side code that is sending the command,
and I don't think psql's variable-substitution ability is quite up
to the job.You might be able to make it work through the hacky method of
supplying the schema name and table name as separate trigger
arguments.Another idea could be to construct the desired string as a SELECT
result, and then use \gexec. There's a few too many moving parts
in that for my taste, but maybe it could work.regards, tom lane
That would explain it! I now see that when I read the CREATE TRIGGER documentation.
I will use the workaround I found, ie. generating a literal string in another meta variable. It would be nice if this would be considered a request for an enhancement?
On Mon, Dec 29, 2025 at 5:58 PM H <agents@meddatainc.com> wrote:
Just tried your suggestion to use format() but it resulted in a 'syntax
error at or near "("'
Helps to show exactly what you tried. I provided pseudo-code.
postgres=# \set sch 'schemaname'
postgres=# select format('%I.%I', :'sch', 'tblname');
format
--------------------
schemaname.tblname
(1 row)
Admittedly, if the names do require quoting, and depending on how the
function uses the value, some additional tweaking may be required.
There shouldn't be anything you cannot do with format that you can do with
pre-processing and setting the variable up-front. Admittedly that is
probably cleaner overall anyway though.
David J.
On December 29, 2025 8:17:37 PM GMT-05:00, "David G. Johnston" <david.g.johnston@gmail.com> wrote:
On Mon, Dec 29, 2025 at 5:58 PM H <agents@meddatainc.com> wrote:
Just tried your suggestion to use format() but it resulted in a
'syntax
error at or near "("'
Helps to show exactly what you tried. I provided pseudo-code.
postgres=# \set sch 'schemaname'
postgres=# select format('%I.%I', :'sch', 'tblname');
format
--------------------
schemaname.tblname
(1 row)Admittedly, if the names do require quoting, and depending on how the
function uses the value, some additional tweaking may be required.There shouldn't be anything you cannot do with format that you can do
with
pre-processing and setting the variable up-front. Admittedly that is
probably cleaner overall anyway though.David J.
I used the exact syntax you provided, however, I suspect that format() cannot be used in an argument in CREATE TRIGGER.
On Monday, December 29, 2025, H <agents@meddatainc.com> wrote:
I used the exact syntax you provided, however, I suspect that format()
cannot be used in an argument in CREATE TRIGGER.
Oh, right. I was apparently overlooking the context and got tunnel vision
around the “versioning” function call that isn’t really a call but rather a
stored expression; and so the arguments must be simple literals (as
documented).
In that case you do indeed need to do the work in the client since the
server needs the final result handed to it.
You could also just create a wrapper function that calls versioning and has
the two name part arguments as separate inputs.
David J.