create function and trigger to update column on table update
От
Dara Olson
Тема
create function and trigger to update column on table update
Дата
Msg-id
5B6943440B4A4F16A1A77D0EA21E07B5@GISWKSTN2
Список
Дерево обсуждения
create function and trigger to update column on table update "Dara Olson" <dolson@glifwc.org>
Re: create function and trigger to update column on table update Tom Lane <tgl@sss.pgh.pa.us>
Re: create function and trigger to update column on table update Josh Kupershmidt <schmiddy@gmail.com>
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
'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)));
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;
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();
FOR EACH ROW EXECUTE PROCEDURE update_catalog_number();
В списке pgsql-novice по дате отправления
