From: | Lorusso Domenico <domenico(dot)l76(at)gmail(dot)com> |
---|---|
To: | "Rhys A(dot)D(dot) Stewart" <rhys(dot)stewart(at)gmail(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-17 15:17:53 |
Message-ID: | CAJMpnG5cqTgjwkaqhg3_0x1Aof4Y4kuumgv3moz7cAUrMFWc_g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)stewart(at)gmail(dot)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.]
From | Date | Subject | |
---|---|---|---|
Next Message | Lorusso Domenico | 2023-08-17 15:43:26 | Schema renaming cascade |
Previous Message | Adrian Klaver | 2023-08-17 14:36:20 | Re: Base files compatibility between PG11 and PG15 |