From: | "Dara Olson" <dolson(at)glifwc(dot)org> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | create function and trigger to update column on table update |
Date: | 2010-12-15 22:02:37 |
Message-ID: | 5B6943440B4A4F16A1A77D0EA21E07B5@GISWKSTN2 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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();
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Kupershmidt | 2010-12-16 02:29:18 | Re: pgstatspack version? |
Previous Message | Aarni | 2010-12-15 10:21:50 | Re: Getting tables from one DB to another |