Using PostGIS trigger?

Question

I'm trying to update a lat and lon columns each time I insert a point with a trigger, but I don't know why I get an error. This is the syntax I'm writing:

CREATE OR REPLACE FUNCTION update_tg2()
RETURNS trigger AS
$$
BEGIN
update places set new.lat  = new.st_y(geo::geometry);
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS triger_coords2 on places;
create trigger triger_coords2 after insert or update on places
for each row execute procedure update_tg2();

And when insert: insert into places (id, nombre, geo) values (18, 'my place', ST_GeomFromText('POINT(-0.42154 38.38000)', 4326))

I'm getting the error:

ERROR:  column "geo" does not exist
LINE 1: SELECT new.st_y(geo::geometry)

Of course, the column exists. If I select st_y(geo::geometry) from places, I can see its Y coord.

Does anyone know what could be failing?

I'm quite new with functions.

Answer 1

There are a few issues to consider. First, have you considered using a VIEW? I.e.

CREATE OR REPLACE VIEW places_with_coods AS
  SELECT gid, ST_Y(geo::geometry) AS lat, ST_X(geo::geometry) AS lon
  FROM places;

However, if you want to use triggers, these changes will make things work. The important pieces being that NEW is the row, which can be modified and returned. Also, use ST_Y and not new.st_y. Lastly, the trigger must be a BEFORE trigger, since it modifies the record before it is stored.

CREATE OR REPLACE FUNCTION update_tg2()
RETURNS trigger AS
$$
BEGIN
  NEW.lat := ST_Y(NEW.geo::geometry);
  NEW.lon := ST_X(NEW.geo::geometry);
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';
DROP TRIGGER IF EXISTS triger_coords2 on places;
CREATE TRIGGER triger_coords2 BEFORE INSERT OR UPDATE ON places
  FOR EACH ROW EXECUTE PROCEDURE update_tg2();

Answer 2

Use new.geo instead. It should work.