Re: Damn triggers and NEW

Joe Conway <mail(at)joeconway(dot)com> writes:
> Nigel J. Andrews wrote:
>> I'd appreciate some pointers on this as it appears new/old can't be used in an
>> execute statement in triggers but that sounds completely wrong.

> I've tried this before, and unfortunately I think that statement is
> currently true.

The problem's not really specific to either NEW/OLD or to EXECUTE;
AFAICT the issue is just that plpgsql does not do run-time field
selection. A field access has to look like foo.bar where both foo
and bar are simple identifiers; you can't play games wherein the name
bar is determined at runtime.

> I ended up concluding that, short of a patch to the backend, a C code
> trigger would be needed. You might be able to do something with pltcl or
> one of the other PLs though.

I believe you can do this in pltcl --- it doesn't do any pre-parsing
or pre-optimization of the code, so whether the field name is static
or just calculated won't matter to it. Also, I believe it allows you
to inquire about the set of field names belonging to NEW or OLD, which
is another thing that's impossible in plpgsql (and wouldn't do you any
good if it were possible, because of the field-access syntax limitation).

Use the right tool for the job.

regards, tom lane