create function and trigger to update column on table update

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();

Responses

Browse pgsql-novice by date

  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