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