PostGIS Cookbook - Second Edition

Extending further...

So far, we've implemented an insert trigger. What if the value changes for a particular row? In that case, we will require a separate update trigger. We'll change our original function to test the UPDATE case, and we'll use WHEN in our trigger to constrain updates to the column being changed.

Also, note that the following function is written with the assumption that the user wants to always update the changing geometries based on the changing values:

CREATE OR REPLACE FUNCTION chp02.before_insertXYZ() RETURNS trigger AS $$ BEGIN if (TG_OP='INSERT') then if (NEW.geom is null) then NEW.geom = ST_SetSRID(ST_MakePoint(NEW.x,NEW.y), 3734); end if; ELSEIF (TG_OP='UPDATE') then NEW.geom = ST_SetSRID(ST_MakePoint(NEW.x,NEW.y), ...