From: | "Rhys A(dot)D(dot) Stewart" <rhys(dot)stewart(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Dynamically accessing columns from a row type in a trigger |
Date: | 2023-08-13 03:21:53 |
Message-ID: | CACg0vTkrqqnuNrHN2iNgQdAdB5JiVJthj31M1OHYSeeZwMpWXw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2023-08-13 04:28:10 | Re: Dynamically accessing columns from a row type in a trigger |
Previous Message | Anthony DeBarros | 2023-08-12 22:59:36 | Re: A Good Beginner's Book |