Обсуждение: Combining metavariables and table names

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.

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?

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

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

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
wouldbe considered a request for an enhancement? 

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.

I used the exact syntax you provided, however, I suspect that format() cannot be used in an argument in CREATE TRIGGER.