Structures that Work - PostGIS - page 78

Now,anyinsertsintoourtableshouldbepopulatedwithnewgeometryrecords.Let
us do a test insert using the following query:
INSERT INTO chp02.xwhyzed1 (x, y, z)
VALUES (random()*5, random()*7,
random()*106);
Extending further...
So far we've implemented an insert trigger. What if the value changes for a par-
ticularrow?Inthatcase,wewillrequireaseparateupdatetrigger.We'llchangeour
original function to test the UPDATE case, and we'll use WHEN in our trigger to con-
strain 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.xyz_pop_geom()
RETURNS TRIGGER AS $popgeom$
BEGIN
IF(TG_OP='INSERT') THEN
UPDATE chp02.xwhyzed1
SET geom = ST_SetSRID(ST_MakePoint(x,y),
3734)
WHERE geom IS NULL
;
ELSIF(TG_OP='UPDATE') THEN
UPDATE chp02.xwhyzed1
SET geom = ST_SetSRID(ST_MakePoint(x,y),
3734)
;
END IF;
RETURN NEW;