4

In PostGIS I have two tables: closures (id, name, endpoint, geom) and chambers (id, name, geom). A closure must always be placed in a chamber for which it will take the chambers name it has been placed into and insert it in the closures's endpoint field. The following piece of code works as expected;

CREATE OR REPLACE FUNCTION the_schema.closures_endpoint_name_trigger_function() RETURNS TRIGGER AS $$

    BEGIN 
         IF (SELECT count(a.name) FROM the_schema.chambers a WHERE st_within(NEW.geom, st_buffer(a.geom,0.1))) > 0
         THEN NEW.endpoint =(SELECT a.name as name FROM the_schema.chambers a WHERE st_within(NEW.geom, st_buffer(a.geom,0.1)) LIMIT 1);
         ELSEIF (SELECT count(a.name) FROM the_schema.chambers a WHERE st_within(NEW.geom, st_buffer(a.geom,0.1))) = 0
         THEN RAISE EXCEPTION 'YOU NEED TO PLACE A CLOSURE IN A CHAMBER';
         ELSEIF NEW.endpoint is null
         THEN RAISE EXCEPTION 'YOU MUST SAVE CHAMBERS LAYER FIRST OR MAKE SURE CLOSURES SNAP THEIR ENDPOINT';
         END IF;
    RETURN NEW;
    END;

$$ language plpgsql;

CREATE TRIGGER closures_endpoint_name_trigger
BEFORE INSERT OR UPDATE
ON the_schema.closures
FOR EACH ROW
EXECUTE PROCEDURE the_schema.closures_endpoint_name_trigger_function();

But I have realised that if I delete a chamber or change it's placement, the closure will still have the endpoint of the chamber I just deleted or moved. Is there a way to create a trigger that deletes the closure or set to null the endpoint value (so that it raises an exception) after having removed or moved a chamber? I want to do it with a trigger as it is based on location so a 'on delete cascade' statement referencing the two related fields do not work for me. Any ideas as to how to achieve this?

2
  • 3
    A FOREIGN KEY endpoint REFERENCES chambers ("name") ON UPDATE CASCADE ON DELETE CASCADE should actually work pretty well? Except, of course, "name" is not unique. Otherwise an ON DELETE trigger on chambers doing a DELETE FROM closures WHERE ST_Intersects(geom, OLD.geom) AND endpoint = OLD."name" - to include an intersection check
    – geozelot
    Sep 27 at 14:17
  • Thank you geozelot I created a trigger as you said and works as expected.
    – franrvr
    Sep 28 at 9:01
3

As mentioned in comments, just add an AFTER DELETE trigger with the intended logic, e.g.

CREATE OR REPLACE FUNCTION the_schema.chamber_closure_cascade_trigger_function()
    RETURNS TRIGGER LANGUAGE 'plpgsql' AS
    $$
    BEGIN
        DELETE
        FROM    the_schema.closures AS c
        WHERE   ST_DWithin(c.geom, OLD.geom, 0.1)
        -- AND   c.endpoint = OLD."name"
        ;

        RETURN NULL;
    END;
    $$
;

CREATE TRIGGER chamber_closure_cascade_trigger
    AFTER DELETE
    ON the_schema.chambers
    FOR EACH ROW
    EXECUTE PROCEDURE the_schema.chamber_closure_cascade_trigger_function()
;

Note the usage of the index optimized ST_DWithin call instead of the ST_Within(ST_Buffer) construct. In general, your UPSERT trigger is rather convoluted and inefficient - consider rewriting it to sth. like

CREATE OR REPLACE FUNCTION the_schema.closures_endpoint_name_trigger_function()
    RETURNS TRIGGER LANGUAGE 'plpgsql' AS
    $$
    BEGIN
        SELECT  c."name"
        FROM    the_schema.chambers AS c
        WHERE   ST_DWithin(c.geom, NEW.geom, 0.1)
        LIMIT   1
        INTO    NEW.endpoint
        ;

        IF NOT FOUND THEN
            RAISE EXCEPTION 'YOU NEED TO PLACE A CLOSURE WITHIN A CHAMBER';
        END IF;

        RETURN NEW;
    END;
    $$
;

CREATE TRIGGER closures_endpoint_name_trigger
    BEFORE INSERT OR UPDATE
    ON the_schema.closures
    FOR EACH ROW
    EXECUTE PROCEDURE the_schema.closures_endpoint_name_trigger_function()
;

This omits the somewhat awkward implementation in your code for when an UPDATE takes place and NEW.endpoint is NULL, and simply tries to find the matching chamber as for an ÌNSERT.

1
  • Thank you it works perfectly. I will rewrite my code so it is more efficient.
    – franrvr
    Sep 28 at 9:00

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.