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.]
