Subject: Re: Combining metavariables and table names

From: "David G. Johnston" <[email-masked]>
Date: Mon, 29 Dec 2025 18:17:37 -0700

On Mon, Dec 29, 2025 at 5:58 PM H <[email-masked]> 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.

---

From: H <[email-masked]>
Date: Mon, 29 Dec 2025 22:02:20 -0500

>postgres=# select format('%I.%I', :'sch', 'tblname');

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

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 29 Dec 2025 20:28:09 -0700

On Monday, December 29, 2025, H <[email-masked]> 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.