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.