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;
-- 
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to