Re: How can I use a dynamic column name after NEW. or OLD. in trigger procedure?

On 01/19/2016 11:14 PM, Vik Fearing wrote:
> On 01/19/2016 11:05 PM, Peter Devoy wrote:
>> As part of the extension I am writing I am trying to create a trigger
>> procedure in which the value of the primary key of the NEW or OLD row
>> is used. The trigger will be fired by arbitrary tables so the column
>> name must be dynamic.  Something like:
>>
>>         pk_column := 'foo_id'; --example assignment only
>>
>>         EXECUTE 'INSERT INTO  bar (baz) VALUES ($1)'
>>         USING NEW.quote_literal(pk_column);
>>
>> Out of desperation I have pretty much brute forced many weird
>> combinations of quote_literal, quote_ident, ::regclass, || and USING.
>> Unfortunately, I have not been able to get anything to work so any
>> help would be very much appreciated.
>
> Everything gets easier when you use format(). The following should do
> what you want:
>
>     EXECUTE format('INSERT INTO bar (baz) VALUES (($1).%L)', pk_column)
>         USING NEW;

Argh!  I *always* type the wrong one.  It should be %I instead of %L
(identifier instead of literal).  Sorry about that.
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support