[postgis-users] Function 'Event Theme' 2 'the geom'

[Frank Broniewski]

On Friday 23 May 2008 09:00:36 Gabriela Messner wrote:
> Hi all,
>
> I´d be hughly appreciated if any one could give any advice to the following
> question:
>
> Is there any function that when filling two fields (UTM-X,UTMY) in a table
> fills 'the_geom' field for that row?.
>
> Thanks in advanced.

You can use triggers for that. Create a trigger function that acts if an 
update or insert statement is fired upon your table. See my example below for 
a starter. The Postgresql manual can help you too ...

Greetings Frank

CREATE OR REPLACE FUNCTION update_locations_geom() RETURNS trigger AS 
$update_locations_geom$
	BEGIN
		IF (TG_OP = 'INSERT') THEN
			NEW.centroid = setsrid(makepoint(NEW.east,NEW.north),2169);
			RETURN NEW;
		END IF;
		IF (TG_OP = 'UPDATE') THEN
			IF (OLD.east != NEW.east OR OLD.north != NEW.north) THEN
				NEW.centroid = setsrid(makepoint(NEW.east,NEW.north),2169);
				RETURN NEW;
			END IF;
		END IF;
	END;
$update_locations_geom$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_locations_geom ON locations;

CREATE TRIGGER update_locations_geom BEFORE INSERT OR UPDATE ON locations 
	FOR EACH ROW EXECUTE PROCEDURE update_locations_geom();