pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedDynamically accessing columns from a row type in a trigger
10+ messages / 6 participants
[nested] [flat]
* Dynamically accessing columns from a row type in a trigger
@ 2023-08-12 20:09 Rhys A.D. Stewart <rhys.stewart@gmail.com>
0 siblings, 4 replies; 10+ messages in thread
From: Rhys A.D. Stewart @ 2023-08-12 20:09 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Greetings all,
I am writing a trigger and would like to know how to dynamically
access a column from the "OLD" variable. pk_col is the column name
from the table.
I've come up with either doing this:
EXECUTE format('SELECT $1.%1$I', pk_col) INTO pk_val USING OLD;
which looks a bit excessive, or this
pk_val = to_jsonb(OLD.*)->pk_col
which looks cleaner, but then I am having to incur a little overhead
by using the to_jsonb function. Ideally, something like this would be
great:
pk_val = OLD[pk_col]
but evidently we can't subscript ROW types.
Am I missing out on a simpler or more elegant solution?
Rhys
Peace & Love | Live Long & Prosper
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dynamically accessing columns from a row type in a trigger
@ 2023-08-12 20:22 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Rhys A.D. Stewart <rhys.stewart@gmail.com>
3 siblings, 0 replies; 10+ messages in thread
From: Adrian Klaver @ 2023-08-12 20:22 UTC (permalink / raw)
To: Rhys A.D. Stewart <rhys.stewart@gmail.com>; pgsql-general@lists.postgresql.org
On 8/12/23 13:09, Rhys A.D. Stewart wrote:
> Greetings all,
>
> I am writing a trigger and would like to know how to dynamically
> access a column from the "OLD" variable. pk_col is the column name
> from the table.
>
> I've come up with either doing this:
> EXECUTE format('SELECT $1.%1$I', pk_col) INTO pk_val USING OLD;
>
> which looks a bit excessive, or this
>
> pk_val = to_jsonb(OLD.*)->pk_col
>
> which looks cleaner, but then I am having to incur a little overhead
> by using the to_jsonb function. Ideally, something like this would be
> great:
>
> pk_val = OLD[pk_col]
Well if want/can use plpython3u you can do just that:
https://www.postgresql.org/docs/current/plpython-trigger.html
as:
pk_val = TD["old"][pk_col]
>
> but evidently we can't subscript ROW types.
>
> Am I missing out on a simpler or more elegant solution?
>
> Rhys
> Peace & Love | Live Long & Prosper
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dynamically accessing columns from a row type in a trigger
@ 2023-08-12 20:31 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Rhys A.D. Stewart <rhys.stewart@gmail.com>
3 siblings, 1 reply; 10+ messages in thread
From: Adrian Klaver @ 2023-08-12 20:31 UTC (permalink / raw)
To: Rhys A.D. Stewart <rhys.stewart@gmail.com>; pgsql-general@lists.postgresql.org
On 8/12/23 13:09, Rhys A.D. Stewart wrote:
> Greetings all,
>
> I am writing a trigger and would like to know how to dynamically
> access a column from the "OLD" variable. pk_col is the column name
> from the table.
>
> I've come up with either doing this:
> EXECUTE format('SELECT $1.%1$I', pk_col) INTO pk_val USING OLD;
Got focused on pk_val = OLD[pk_col] and missed the low hanging fruit:
pk_val = OLD.pk_col
>
> which looks a bit excessive, or this
>
> pk_val = to_jsonb(OLD.*)->pk_col
>
> which looks cleaner, but then I am having to incur a little overhead
> by using the to_jsonb function. Ideally, something like this would be
> great:
>
> pk_val = OLD[pk_col]
>
> but evidently we can't subscript ROW types.
>
> Am I missing out on a simpler or more elegant solution?
>
> Rhys
> Peace & Love | Live Long & Prosper
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dynamically accessing columns from a row type in a trigger
@ 2023-08-13 03:21 Rhys A.D. Stewart <rhys.stewart@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 10+ messages in thread
From: Rhys A.D. Stewart @ 2023-08-13 03:21 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org
Hey Adrian,
Thanks for your response. I don't think I explained myself clearly.
pk_col is not the column name. pk_col is a variable that holds the
name of a column. This is one trigger for several tables with varying
structures. So pk_col would be a column specific to the current
TG_TABLE_NAME when the trigger is invoked. This is why in my example I
had to use EXECUTE to get the value of the pk_col from OLD.
Actually, now that I'm thinking about it, I don't really want to store
the value into a variable because the pk_col might be of any given
type. So ideally, I'd love a way to just get the value from OLD and
use it directly in another query. Something along the lines of:
`EXECUTE format('SELECT * FROM %1$I.sometable WHERE pk = $1', myschma)
USING OLD['pk_col']`.
I reckon I may have to look at just generating a trigger function per
table, or maybe look into using TG_ARGS.
Rhys
Peace & Love | Live Long & Prosper
On Sat, Aug 12, 2023 at 3:31 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 8/12/23 13:09, Rhys A.D. Stewart wrote:
> > Greetings all,
> >
> > I am writing a trigger and would like to know how to dynamically
> > access a column from the "OLD" variable. pk_col is the column name
> > from the table.
> >
> > I've come up with either doing this:
> > EXECUTE format('SELECT $1.%1$I', pk_col) INTO pk_val USING OLD;
>
> Got focused on pk_val = OLD[pk_col] and missed the low hanging fruit:
>
> pk_val = OLD.pk_col
>
> >
> > which looks a bit excessive, or this
> >
> > pk_val = to_jsonb(OLD.*)->pk_col
> >
> > which looks cleaner, but then I am having to incur a little overhead
> > by using the to_jsonb function. Ideally, something like this would be
> > great:
> >
> > pk_val = OLD[pk_col]
> >
> > but evidently we can't subscript ROW types.
> >
> > Am I missing out on a simpler or more elegant solution?
> >
> > Rhys
> > Peace & Love | Live Long & Prosper
> >
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dynamically accessing columns from a row type in a trigger
@ 2023-08-13 04:28 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Rhys A.D. Stewart <rhys.stewart@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Adrian Klaver @ 2023-08-13 04:28 UTC (permalink / raw)
To: Rhys A.D. Stewart <rhys.stewart@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On 8/12/23 20:21, Rhys A.D. Stewart wrote:
> Hey Adrian,
>
> Thanks for your response. I don't think I explained myself clearly.
> pk_col is not the column name. pk_col is a variable that holds the
> name of a column. This is one trigger for several tables with varying
> structures. So pk_col would be a column specific to the current
> TG_TABLE_NAME when the trigger is invoked. This is why in my example I
> had to use EXECUTE to get the value of the pk_col from OLD.
So you are looking for a generic solution.
>
> Actually, now that I'm thinking about it, I don't really want to store
> the value into a variable because the pk_col might be of any given
> type. So ideally, I'd love a way to just get the value from OLD and
> use it directly in another query. Something along the lines of:
The issue then is determining what value to get from OLD.
Is that generic e.g always the Primary Key or will it vary?
>
> `EXECUTE format('SELECT * FROM %1$I.sometable WHERE pk = $1', myschma)
> USING OLD['pk_col']`.
>
> I reckon I may have to look at just generating a trigger function per
> table, or maybe look into using TG_ARGS.
>
>
> Rhys
> Peace & Love | Live Long & Prosper
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dynamically accessing columns from a row type in a trigger
@ 2023-08-13 04:34 David G. Johnston <david.g.johnston@gmail.com>
parent: Rhys A.D. Stewart <rhys.stewart@gmail.com>
3 siblings, 0 replies; 10+ messages in thread
From: David G. Johnston @ 2023-08-13 04:34 UTC (permalink / raw)
To: Rhys A.D. Stewart <rhys.stewart@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Sat, Aug 12, 2023 at 1:10 PM Rhys A.D. Stewart <rhys.stewart@gmail.com>
wrote:
> Am I missing out on a simpler or more elegant solution?
>
>
No, you are not (at least among SQL and pl/pgsql. SQL is strongly and
statically typed. Circumventing that has a cost, but at least you do have
tools at hand when you find the need.
David J.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dynamically accessing columns from a row type in a trigger
@ 2023-08-13 04:53 jian he <jian.universality@gmail.com>
parent: Rhys A.D. Stewart <rhys.stewart@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: jian he @ 2023-08-13 04:53 UTC (permalink / raw)
To: Rhys A.D. Stewart <rhys.stewart@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Sun, Aug 13, 2023 at 11:27 AM Rhys A.D. Stewart
<rhys.stewart@gmail.com> wrote:
>
> Hey Adrian,
>
> Thanks for your response. I don't think I explained myself clearly.
> pk_col is not the column name. pk_col is a variable that holds the
> name of a column. This is one trigger for several tables with varying
> structures. So pk_col would be a column specific to the current
> TG_TABLE_NAME when the trigger is invoked. This is why in my example I
> had to use EXECUTE to get the value of the pk_col from OLD.
>
> Actually, now that I'm thinking about it, I don't really want to store
> the value into a variable because the pk_col might be of any given
> type. So ideally, I'd love a way to just get the value from OLD and
> use it directly in another query. Something along the lines of:
>
> `EXECUTE format('SELECT * FROM %1$I.sometable WHERE pk = $1', myschma)
> USING OLD['pk_col']`.
>
> I reckon I may have to look at just generating a trigger function per
> table, or maybe look into using TG_ARGS.
>
google lead me to this post:
https://stackoverflow.com/questions/55245353/access-dynamic-column-name-of-row-type-in-trigger-funct...
> table, or maybe look into using TG_ARGS.
maybe you are referring to TG_ARGV.
example of TG_ARGV =>
https://git.postgresql.org/cgit/postgresql.git/tree/src/test/regress/expected/triggers.out
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dynamically accessing columns from a row type in a trigger
@ 2023-08-15 00:20 Rhys A.D. Stewart <rhys.stewart@gmail.com>
parent: jian he <jian.universality@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Rhys A.D. Stewart @ 2023-08-15 00:20 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Hello again
> > Actually, now that I'm thinking about it, I don't really want to store
> > the value into a variable because the pk_col might be of any given
> > type. So ideally, I'd love a way to just get the value from OLD and
> > use it directly in another query. Something along the lines of:
> >
> > `EXECUTE format('SELECT * FROM %1$I.sometable WHERE pk = $1', myschma)
> > USING OLD['pk_col']`.
> >
> > I reckon I may have to look at just generating a trigger function per
> > table, or maybe look into using TG_ARGS.
So the less obvious solution that works is to create a temporary
table. A little verbose, but I get to keep the types.
`CREATE TEMPORARY TABLE _ ON COMMIT DROP AS SELECT OLD.*;`
_ as a table name makes things a little easier to type.
Rhys
Peace & Love | Live Long & Prosper
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dynamically accessing columns from a row type in a trigger
@ 2023-08-15 00:49 Rob Sargent <robjsargent@gmail.com>
parent: Rhys A.D. Stewart <rhys.stewart@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Rob Sargent @ 2023-08-15 00:49 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
So the less obvious solution that works is to create a temporary
> table. A little verbose, but I get to keep the types.
>
> `CREATE TEMPORARY TABLE _ ON COMMIT DROP AS SELECT OLD.*;`
>
> _ as a table name makes things a little easier to type.
>
>
>
> Rhys
> Peace & Love | Live Long & Prosper
>
>
If the connection creating the table is long-lived, maybe make the table
once and truncate instead.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dynamically accessing columns from a row type in a trigger
@ 2023-08-17 15:17 Lorusso Domenico <domenico.l76@gmail.com>
parent: Rhys A.D. Stewart <rhys.stewart@gmail.com>
3 siblings, 0 replies; 10+ messages in thread
From: Lorusso Domenico @ 2023-08-17 15:17 UTC (permalink / raw)
To: Rhys A.D. Stewart <rhys.stewart@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Well, some weeks ago, I read an article about that.
The more efficient approach (in pgplsql) is to use hstore.
With a similar topic, at the end, I created a group of functions that store
in an internal table, data structure for each table under the same generic
trigger.
I also store, primary key.
The goal of this approach is to compare easley old and new values,
discarding automatic and special (for my project) fields, so I can manage
historical versions, audit information etc...
Il giorno sab 12 ago 2023 alle ore 22:16 Rhys A.D. Stewart <
rhys.stewart@gmail.com> ha scritto:
> Greetings all,
>
> I am writing a trigger and would like to know how to dynamically
> access a column from the "OLD" variable. pk_col is the column name
> from the table.
>
> I've come up with either doing this:
> EXECUTE format('SELECT $1.%1$I', pk_col) INTO pk_val USING OLD;
>
> which looks a bit excessive, or this
>
> pk_val = to_jsonb(OLD.*)->pk_col
>
> which looks cleaner, but then I am having to incur a little overhead
> by using the to_jsonb function. Ideally, something like this would be
> great:
>
> pk_val = OLD[pk_col]
>
> but evidently we can't subscript ROW types.
>
> Am I missing out on a simpler or more elegant solution?
>
> Rhys
> Peace & Love | Live Long & Prosper
>
>
>
--
Domenico L.
per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2023-08-17 15:17 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-12 20:09 Dynamically accessing columns from a row type in a trigger Rhys A.D. Stewart <rhys.stewart@gmail.com>
2023-08-12 20:22 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-08-12 20:31 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-08-13 03:21 ` Rhys A.D. Stewart <rhys.stewart@gmail.com>
2023-08-13 04:28 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-08-13 04:53 ` jian he <jian.universality@gmail.com>
2023-08-15 00:20 ` Rhys A.D. Stewart <rhys.stewart@gmail.com>
2023-08-15 00:49 ` Rob Sargent <robjsargent@gmail.com>
2023-08-13 04:34 ` David G. Johnston <david.g.johnston@gmail.com>
2023-08-17 15:17 ` Lorusso Domenico <domenico.l76@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox