Wednesday, November 16, 2011

Postgresql - My first trigger ever!!

Today I stumbled upon triggers for the first time ever. This one updates a "calculated" geometry column in a postgis enabled database.

CREATE FUNCTION llenar_geom4326 () RETURNS trigger AS $$
BEGIN
NEW.geom_4326 = ST_GeomFromText('POINT(' || NEW.longitud || ' '|| NEW.latitud || ')',4326);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER llenar_geom4326_trg BEFORE INSERT OR UPDATE
ON geo.lugar FOR EACH ROW
EXECUTE PROCEDURE llenar_geom4326 ();


Once I get passed that Oracle certification issue, I'll study plpgsql in depth.

Source:

http://developer.postgresql.org/pgdocs/postgres/sql-createtrigger.html

No comments:

Post a Comment