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

On Wed, Dec 15, 2010 at 5:02 PM, Dara Olson <dolson@glifwc.org> wrote:
> 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!
> Thanks!
> Dara
>
> CREATE OR REPLACE FUNCTION invasive_species.update_catalog_number() RETURNS
> TRIGGER AS
> 'BEGIN
>
> IF TG_OP = "UPDATE" THEN
> UPDATE invasive_species.invspp_occurrence_data
> SET catalog_number = "tsn_char" || $_$ || x(centroid(transform(the_geom,
> 4326)))|| $_$ || y(centroid(transform(the_geom, 4326)));
>
> RETURN NEW;
> END IF;
> RETURN NULL;
> END;'
> LANGUAGE plpgsql;
>
> CREATE TRIGGER catalog_num_trigger AFTER UPDATE ON
> invasive_species.invspp_occurrence_data
> FOR EACH ROW EXECUTE PROCEDURE update_catalog_number();

It looks like the UPDATE statement within update_catalog_number() is
updating the entire invspp_occurrence_data table every time it gets
called -- that is, for every row which gets udpated in this table, a
full-table UPDATE is initiated by this trigger function. Is this
really what you need?

I didn't entirely understand your goals, but a much more common use of
trigger functions is roughly like this:
 * trigger is declared as BEFORE UPDATE instead of AFTER UPDATE as you have
 * trigger modifies column(s) of the row being updated by modifing the
NEW variable
 * trigger has RETURN NEW; at the end. You have RETURN NEW; as well as
RETURN NULL;, but because your trigger is an AFTER UPDATE trigger,
neither of these do anything useful.

See also Example 39-3 at:
http://developer.postgresql.org/pgdocs/postgres/plpgsql-trigger.html
for an example of a BEFORE INSERT OR UPDATE trigger.

Hope this helps..
Josh

"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

----- Original Message -----

From: Tom Lane

To: Dara Olson

Cc: pgsql-novice@postgresql.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@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 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