Thread: Tantalizing reference to passing data from NEW variable in a trigger function...

All,
This question seems to have been asked a number of times on this list, 
recently (including by me) without being answered: how to pass the row 
data stored in the NEW variable from a trigger function into a 
sub-function. None of the obvious syntaxes work, including one by a 
respondent in this list that suggested assigning NEW to a record 
variable, and then passing *that*. After searching up and down the 
archives, I see the following tantalizing snippet (from 6/2003) in the 
context of a rule:

Gunter Diehl <gdid ( at ) gmx ( dot ) de> writes:> create funtion f1(v, v) returns void as '...'> create rule vupt as
onupdate to v do instead select f1(new, old)> While accepting this definitions, pg says it can not handle "whole-tuple>
references"at runtime.
 

FWIW, the "old" case works fine.  "new" doesn't work so well because the
new row hasn't been formed into a tuple at the point where the rule
runs; it only exists as a list of variables.  (This is a rather handwavy
explanation, but I think it captures the key point.)  While this could
probably be fixed with some effort, I doubt it's going to happen soon.
Is there any chance of doing what you want with a trigger instead of a
rule?
                                                    ^^^^^

If there's a page somewhere that says how to do this, I'd be extremely 
grateful for a pointer. The obvious maneuver of copying NEW's data 
field-by-field into a composite variable is rather unappetizing when the 
row is wide...                          Rich Cullingford                          rculling@sysd.com

Rich Cullingford wrote:
> This question seems to have been asked a number of times on this list, 
> recently (including by me) without being answered: how to pass the row 
> data stored in the NEW variable from a trigger function into a 
> sub-function. None of the obvious syntaxes work, including one by a 
> respondent in this list that suggested assigning NEW to a record 
> variable, and then passing *that*. After searching up and down the 
> archives, I see the following tantalizing snippet (from 6/2003) in the 
> context of a rule:

The standard answer to this has been to use PL/Tcl for your trigger
function instead of PL/pgSQL. I imagine PL/Python would work also, but
I'm not sure.

(FWIW, PL/R would work too, but I doubt you would want to install R just
to have this capability.)

I looked a bit at making this work in PL/pgSQL at some point -- IIRC the
issues to be solved were non-trivial.

Joe

Joe Conway <mail@joeconway.com> writes:
> Rich Cullingford wrote:
>> This question seems to have been asked a number of times on this list, 
>> recently (including by me) without being answered: how to pass the row 
>> data stored in the NEW variable from a trigger function into a 
>> sub-function.

> I looked a bit at making this work in PL/pgSQL at some point -- IIRC the
> issues to be solved were non-trivial.

I think that this might be less non-trivial given the recent changes in
the composite-type-datum support.  I haven't got round to looking at
plpgsql in particular, though.
        regards, tom lane