2

I have 2 layers on PostGIS: a point layer named 'tdia' and a polygon layer named 'com': -the com layer contains the city name. (Polygon) -the tdia layer contains bus stops. (point) I want to create a trigger on my layer tdia that allows to recover the name of the city after creating a bus stops.

My code does not work:

CREATE TABLE tdia (gid SERIAL NOT NULL PRIMARY KEY, numero
VARCHAR(5), CODE_INSEE VARCHAR(5), NOM_COM VARCHAR(50), date DATE, geom geometry(point, 2154))


CREATE OR REPLACE FUNCTION maj_tdia()
RETURNS "trigger" AS
$BODY$
BEGIN
UPDATE tdia SET CODE_INSEE =
(SELECT com.CODE_INSEE
FROM com, tdia
WHERE st_intersects (tdia.geometry, com.geometry));
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER maj_tdia BEFORE INSERT OR UPDATE
ON tdia
FOR EACH ROW
EXECUTE PROCEDURE maj_tdia();

What am I doing wrong?

  • what is the error you see? – Ian Turton Dec 2 '18 at 11:43
  • @IanTurton ...there's likely no error message. the trigger runs just fine, it's simply not doing anything useful while actually doing a lot: that trigger function updates the entire table before every row that is to be inserted! the update cannot see the new rows, nothing happens...you want to use the NEW.<col> row/column specifier, within a function that also returns NEW. – ThingumaBob Dec 2 '18 at 13:21
7

Don't UPDATE the tables, simply set the value of the NEW record; the NEW (and OLD in UPDATES) record is passed to the function for each row and will hold the full set of values that is adressed by the UPDATE/INSERT. BEFORE(/AFTER) triggers then accept a (manipulated) record from their functions to be inserted/updated (instead):

CREATE OR REPLACE FUNCTION maj_tdia()
RETURNS TRIGGER AS
$BODY$
  BEGIN
    NEW.code_insee := (
      SELECT code_insee
      FROM   com
      WHERE  ST_Intersects(com.geometry, NEW.geometry)
    );
    RETURN NEW;
  END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER maj_tdia
BEFORE INSERT OR UPDATE
ON tdia
FOR EACH ROW
EXECUTE PROCEDURE maj_tdia();

Let me add:

It might be useful to separate INSERT and UPDATE triggers in this context; e.g. if you update only the name of a bus station, it is unnecessary to calculate the intersection, especially if the target tables are large.

You can specify that the trigger will only fire when geometry is directly or implicitly referred to within the UPDATE statement (and actually differs from the stored geometry):

CREATE TRIGGER maj_tdia_ins
    BEFORE INSERT
    ON tdia
    FOR EACH ROW
    EXECUTE PROCEDURE maj_tdia();

CREATE TRIGGER maj_tdia_upd
    BEFORE UPDATE
    OF geometry
    ON tdia
    FOR EACH ROW
    WHEN (ST_Equals(NEW.geometry, OLD.geometry) = false)
    EXECUTE PROCEDURE maj_tdia();
  • as a more general sidenote: this is a simple solution using simple triggers on a simple case; in general, triggers need quite a bit of attention to work as expected. it's worth to dive into the docs to get aquainted with some of the more delicate sideeffects, like the behaviour with table constraints etc. – ThingumaBob Dec 2 '18 at 14:49
  • @fcka hope it helped! if it did, consider accepting the answer (hit.the tick next to an it). – ThingumaBob Dec 2 '18 at 21:13

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.