Skip site navigation (1) Skip section navigation (2)

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

From: "Dara Olson" <dolson(at)glifwc(dot)org>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: create function and trigger to update column on table update
Date: 2010-12-16 16:58:20
Message-ID: 2ABE5C72CBC24212A491D46B52BBF322@GISWKSTN2 (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Mladen GogalaDate: 2010-12-16 20:49:20
Subject: Pgstatindex and leaf fragmentation.
Previous:From: Tom LaneDate: 2010-12-16 15:12:54
Subject: Re: create function and trigger to update column on table update

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group