1

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.

3

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();
  • The trigger runs ok now. The problem was that I was writing the NEW. outside the function. And the after word too. – Pablo Pardo Apr 13 '15 at 8:23
0

Use new.geo instead. It should work.

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.