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

Thank you so very much for the help! It worked perfectly.
Below is what I ended up with...

CREATE OR REPLACE FUNCTION invasive_species.update_catalog_number() RETURNS TRIGGER AS
'BEGIN
NEW.catalog_number := NEW.tsn_char || x(centroid(transform(NEW.the_geom, 4326))) || y(centroid(transform(NEW.the_geom, 4326)));
RETURN NEW;
END;'
LANGUAGE plpgsql;
CREATE TRIGGER catalog_num_trigger BEFORE INSERT OR UPDATE ON invasive_species.invspp_occurrence_data
FOR EACH ROW EXECUTE PROCEDURE invasive_species.update_catalog_number();
----- Original Message -----
From: Tom Lane
To: Dara Olson
Cc: pgsql-novice(at)postgresql(dot)org
Sent: Thursday, December 16, 2010 9:12 AM
Subject: Re: [NOVICE] create function and trigger to update column on table update

"Dara Olson" <dolson(at)glifwc(dot)org> writes:
> I am new to creating triggers/functions. I am trying to create a trigger and function that when a specific table is updated or 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 complete wrong direction? Is there a way to define that the column only gets updated on the records that were updated or inserted. 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