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.
Best Answer
There are a few issues to consider. First, have you considered using a VIEW? I.e.
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, useST_Y
and notnew.st_y
. Lastly, the trigger must be aBEFORE
trigger, since it modifies the record before it is stored.