Re: Dynamically accessing columns from a row type in a trigger

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