From: | Vik Fearing <vik(at)2ndquadrant(dot)fr> |
---|---|
To: | Peter Devoy <peter(at)3xe(dot)co(dot)uk>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: How can I use a dynamic column name after NEW. or OLD. in trigger procedure? |
Date: | 2016-01-19 22:14:20 |
Message-ID: | 569EB53C.1000408@2ndquadrant.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Mead | 2016-01-19 22:16:41 | Re: How can I use a dynamic column name after NEW. or OLD. in trigger procedure? |
Previous Message | Peter Devoy | 2016-01-19 22:05:44 | How can I use a dynamic column name after NEW. or OLD. in trigger procedure? |