Markus Bertheau <twanger@bluetwanger.de> writes:
> The trigger is probably recursively being called.
Well, of course. Every UPDATE causes another UPDATE, which queues
another trigger firing. What else would you expect but an infinite
loop?
The correct way to do this is illustrated in the plpgsql trigger
example at the bottom of this page:
http://www.postgresql.org/docs/7.4/static/plpgsql-trigger.html
You use a BEFORE trigger and alter the NEW record before it gets
written.
AFTER triggers are not intended for modifying data in the record they
are fired for --- it's too late for that. (Even if you avoid the
infinite loop by testing whether you really need to do another UPDATE
or not, it's still ridiculously inefficient to force another cycle of
UPDATE when you could just as easily have gotten it right beforehand.)
Usually people use AFTER triggers for end-of-command consistency
checking or for propagating information to other tables.
regards, tom lane