Re: Damn triggers and NEW

On Tue, 17 Jun 2003, Tom Lane wrote:

> 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.

Quite.

When I floated the idea of doing some server programming in pltcl or plpython
the client was unhappy with it so I've therefore just been bashing out stuff in
plpgsql until I can slow down a little and think about coding the things in C.

Of course, given that plpython seems on it's last legs for now it was probably
a wise choice not to go with that particular language.

Sorry, for asking another stupid plpgsql question. Unfortunately I hit the
problem as I was trying to load up a new development database, having
discovered yesterday that at least one function had been written for a changed
design without the dev database containing the change and it looking like a
time consuming task to revert and check everything when that time was better
spent actually loading a new db.

--
Nigel J. Andrews