public inbox for [email protected]help / color / mirror / Atom feed
Re: Combining metavariables and table names 4+ messages / 3 participants [nested] [flat]
* Re: Combining metavariables and table names @ 2025-12-29 01:53 David G. Johnston <[email protected]> 2025-12-29 03:41 ` Re: Combining metavariables and table names Tom Lane <[email protected]> 2025-12-30 00:58 ` Re: Combining metavariables and table names H <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: David G. Johnston @ 2025-12-29 01:53 UTC (permalink / raw) To: H <[email protected]>; +Cc: PostgreSQL Users Mailing List <[email protected]> On Sunday, December 28, 2025, H <[email protected]> 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. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Combining metavariables and table names 2025-12-29 01:53 Re: Combining metavariables and table names David G. Johnston <[email protected]> @ 2025-12-29 03:41 ` Tom Lane <[email protected]> 2025-12-30 01:01 ` Re: Combining metavariables and table names H <[email protected]> 1 sibling, 1 reply; 4+ messages in thread From: Tom Lane @ 2025-12-29 03:41 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: H <[email protected]>; PostgreSQL Users Mailing List <[email protected]> "David G. Johnston" <[email protected]> writes: > On Sunday, December 28, 2025, H <[email protected]> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Combining metavariables and table names 2025-12-29 01:53 Re: Combining metavariables and table names David G. Johnston <[email protected]> 2025-12-29 03:41 ` Re: Combining metavariables and table names Tom Lane <[email protected]> @ 2025-12-30 01:01 ` H <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: H @ 2025-12-30 01:01 UTC (permalink / raw) To: [email protected] On December 28, 2025 10:41:19 PM EST, Tom Lane <[email protected]> wrote: >"David G. Johnston" <[email protected]> writes: >> On Sunday, December 28, 2025, H <[email protected]> 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 would be considered a request for an enhancement? ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Combining metavariables and table names 2025-12-29 01:53 Re: Combining metavariables and table names David G. Johnston <[email protected]> @ 2025-12-30 00:58 ` H <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: H @ 2025-12-30 00:58 UTC (permalink / raw) To: PostgreSQL Users Mailing List <[email protected]> On December 28, 2025 8:53:50 PM EST, "David G. Johnston" <[email protected]> wrote: >On Sunday, December 28, 2025, H <[email protected]> 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. ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-12-30 01:01 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-12-29 01:53 Re: Combining metavariables and table names David G. Johnston <[email protected]> 2025-12-29 03:41 ` Tom Lane <[email protected]> 2025-12-30 01:01 ` H <[email protected]> 2025-12-30 00:58 ` H <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox