Postgresql – How to delete constraint inside a trigger function

postgispostgresql

I have read a lot about it, but being new to Postgres, I am at a loss.

The trigger is :

CREATE TRIGGER foopolygon
BEFORE INSERT OR UPDATE OR DELETE ON polygon
FOR EACH ROW EXECUTE PROCEDURE maj_foo();

Part of the function is :

CREATE OR REPLACE FUNCTION maj_foo() 
RETURNS TRIGGER AS
$BODY$
    IF TG_OP = 'INSERT'
     THEN

    ALTER TABLE points
    DROP CONSTRAINT bar;

    INSERT INTO points(gid,fookey)
    VALUES(0,NEW.fookey);

    EXECUTE 'UPDATE points SET fookey = ' ||  quote_literal(NEW.fookey)  || ' WHERE ST_Within(points.geom, $1)'
    USING NEW.geom;

  ALTER TABLE points
  add constraint bar
  foreign key (fookey)
  references polygon (fookey);

      COMMIT;
IF TG_OP = 'UPDATE'......

Best Answer

I think you want to set your foreign key DEFERRABLE INITIALLY DEFERRED so that you don't have to drop and recreate it every time there is an INSERT or other data change. Maybe even this is not needed, but it depends on what happens in the rest of the transaction.

Then the rest is just 

IF tg_op = 'INSERT
THEN
    INSERT INTO points (gid, fookey)
    VALUES (0, NEW.fookey);

    UPDATE points 
    SET fookey = NEW.fookey
    WHERE ST_Within(points.geom, NEW.geom);
ELSIF ...

Notes:

  • you cannot COMMIT from inside a function
  • the DB is either PostgreSQL or Postgres, not Postgre.
  • logically, this is more an AFTER trigger than a BEFORE one.
Related Question