Re: create function and trigger to update column on table update

"Dara Olson" <dolson@glifwc.org> writes:
> I am new to creating triggers/functions.  I am trying to create a trigger and function that when a specific table is
updatedor records added that it updates an existing column (catalog_number) from an existing column and
latitude/longitude. Below is what I have so far.  Does anyone know what I am doing wrong or if I am going in the
completewrong direction?  Is there a way to define that the column only gets updated on the records that were updated
orinserted. Any help would be greatly appreciated! 

Yeah, you're going in the wrong direction.  You don't want to issue a
new UPDATE from an UPDATE trigger: that will just send you into an
infinite loop of repeated updates.  What you want to do is (1) use a
BEFORE trigger, not an AFTER trigger, and (2) modify the NEW row, rather
than looking at the table proper.  So the body of the function would look
something like

NEW.catalog_number := "tsn_char" || x(centroid(transform(NEW.the_geom, 4326))) || y(centroid(transform(NEW.the_geom,
4326)));
RETURN NEW;

if I've guessed at the intent of your code correctly.  There are
examples to look at in the plpgsql chapter of the manual.

            regards, tom lane