[GIS] How to create a trigger for multiple schemas

postgispostgresqltrigger

One of my PostgreSQL databases contains different schemas which share the same structure.

-- schema region_a
CREATE TABLE region_a.point (
gid serial NOT NULL,
geom geometry(point, SRID),
attribute_sample varchar(255),
CONSTRAINT point_pkey PRIMARY KEY (gid)
);

CREATE TABLE region_a.polygon (
gid serial NOT NULL,
geom geometry(polygon, SRID),
attribute varchar(255),
CONSTRAINT polygon_pkey PRIMARY KEY (gid)
);

-- schema region_b
CREATE TABLE region_b.point (
gid serial NOT NULL,
geom geometry(point, SRID),
attribute_sample varchar(255),
CONSTRAINT point_pkey PRIMARY KEY (gid)
);

CREATE TABLE region_b.polygon (
gid serial NOT NULL,
geom geometry(polygon, SRID),
attribute varchar(255),
CONSTRAINT polygon_pkey PRIMARY KEY (gid)
);

-- schema region_c
-- ...

Now I wonder how to create a trigger to add sample points on the polygon features within a schema.

CREATE OR REPLACE FUNCTION sample_attribute_from_polygon()
RETURNS trigger AS $body$
    BEGIN
        NEW.attribute_sample = (SELECT attribute FROM polygon
        WHERE ST_Within(NEW.geom, polygon.geom));
        RETURN NEW;
    END;
$body$ LANGUAGE plpgsql; 

CREATE TRIGGER sample_attribute_from_polygon_tg BEFORE INSERT OR UPDATE
ON point FOR EACH ROW
EXECUTE PROCEDURE sample_attribute_from_polygon();

Is there any way to use the same trigger for all schemas? I'm looking for a solution that's also working when renaming a schema.

Best Answer

You can bind a trigger function to as many tables as you want; just execute a CREATE TRIGGER statement for each binding. Make sure to schema-qualify the table name in your statement (BEFORE INSERT OR UPDATE ON a.point etc.) If you have a large number of schemas, you could generate the SQL dynamically by iterating over the rows in information_schema.schemata.

If the trigger function needs to do something different depending on which table it's running on, you can access the table schema and name through TG_TABLE_SCHEMA and TG_TABLE_NAME. (See the docs for all available variables).

In your case, a dynamic trigger function might be something like this:

CREATE OR REPLACE FUNCTION sample_attribute_from_polygon()
RETURNS trigger AS $body$
    BEGIN
    EXECUTE 'SELECT attribute FROM ' || TG_TABLE_SCHEMA || '.polygon WHERE ST_Within($1, polygon.geom) LIMIT 1' 
      USING NEW.geom 
      INTO NEW.attribute_sample;
    RETURN NEW;
    END;
$body$ LANGUAGE plpgsql;

Related Solutions

[GIS] Trigger to fill attributes (data, client ip) in table for each new objects, PostGIS

Something even simpler:

-- I just create a dumb table:

CREATE TABLE point (
    id serial primary key,
    anything varchar
);

-- Add field names with default values as desired:

ALTER TABLE point ADD COLUMN username varchar DEFAULT "current_user"();
ALTER TABLE point ADD COLUMN address varchar DEFAULT "inet_client_addr"();
ALTER TABLE point ADD COLUMN time timestamp without time zone default now();

-- Now let's feed the table:

INSERT INTO point (anything) VALUES ('something'), ('something else');

-- See if it works:

SELECT * FROM point;
 id |    anything    | username |     address     |            time            
----+----------------+----------+-----------------+----------------------------
  1 | something      | lougit   | 192.168.1.29/32 | 2013-10-15 12:34:46.427796
  2 | something else | lougit   | 192.168.1.29/32 | 2013-10-15 12:34:46.427796
(2 rows)

I recently had a similar problem; but using a TRIGGER led to an overhead which was a bit too high for the (small) size of the postgresql server. Large amounts of data were to be inserted in the table, and it induced some latency. So, instead, I simply used default values, as shown above. And it worked well.

Hope it helps!

[GIS] How to use interacting PostgreSQL/PostGIS triggers in QGIS to create multiple labels on polygons

You could write a before-delete trigger and modify your insert and update-trigger like my following example. The workflow works so far but the code could still be "cleaned" and optimized to prevent recursions between the different triggers... so I post my code as "work in progress" ;)

https://gist.github.com/neogis-de/a1d08c38d8b9c5d316c7

CREATE TABLE label_point (
gid serial NOT NULL,
geom geometry(point, 3857),
label_sample varchar(255),
CONSTRAINT label_point_pkey PRIMARY KEY (gid)
);

CREATE TABLE soil (
gid serial NOT NULL,
geom geometry(polygon, 3857),
label varchar(255),
CONSTRAINT soil_pkey PRIMARY KEY (gid)
);


------------------------------------------

-- Trigger for point Layer

CREATE OR REPLACE FUNCTION sample_label_point() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
raise notice 'point trigger starts now: %', now();
IF TG_OP = 'INSERT'
THEN
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
        soil AS s
        WHERE st_Within(NEW.geom, s.geom)) AS foo) > 0
  THEN
    SELECT soil.label
INTO NEW.label_sample
FROM soil
WHERE ST_Intersects(NEW.geom, soil.geom);
raise notice 'point trigger ends now: %', now();
    RETURN NEW; 
  ELSE
    RAISE notice 'no intersection';
    RAISE notice 'point trigger ends now: %', now();
    RETURN NEW;
  END IF;
ELSIF TG_OP = 'UPDATE'
THEN
IF
(ST_Equals(NEW.geom , OLD.geom)=FALSE)
THEN

  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
        soil AS s
        WHERE st_Within(NEW.geom, s.geom)
        AND (t.gid <> OLD.gid)) AS foo) > 0 
  THEN
SELECT soil.label
INTO NEW.label_sample
FROM soil
WHERE ST_Intersects(NEW.geom, soil.geom);
    RAISE Notice 'Intersection found!';
    RETURN NEW; 

  ELSE 
  SELECT NULL
    INTO NEW.label_sample;
  RETURN NEW; 
  raise notice 'point trigger ends now: %', now();
  END IF; 
ELSE
Raise Notice 'Update of attribute data';
raise notice 'point trigger ends now: %', now();
Return NEW;
END IF;
END IF; 
END; 
$BODY$ 
LANGUAGE plpgsql;


CREATE TRIGGER label_point_trigger
BEFORE INSERT OR UPDATE ON label_point
FOR EACH ROW EXECUTE PROCEDURE sample_label_point();

-----------------------------------------

-- Trigger for Soil Layer

CREATE OR REPLACE FUNCTION soil_label() RETURNS TRIGGER AS
$BODY$
DECLARE
new_label text := quote_ident(NEW.label);  -- assign at declaration
BEGIN
IF TG_OP = 'INSERT'
THEN
raise notice 'soil insert-trigger starts now: %', now();
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
        soil AS s
        WHERE st_Within(t.geom, NEW.geom)) AS foo) > 0
  THEN
    EXECUTE 'UPDATE label_point SET label_sample = $2 WHERE ST_Within(label_point.geom, $1)'
        USING NEW.geom, NEW.label;
--   raise notice 'soil trigger ends now: %', now();
    RETURN NEW; 
  ELSE
    RAISE Notice 'no intersection';
    RETURN NEW;
  END IF;
ELSIF TG_OP = 'UPDATE'
THEN
raise notice 'soil update-trigger starts now: %', now(); 
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
        soil AS s
        WHERE st_Within(t.geom, NEW.geom)
        --AND (t.gid <> OLD.gid)
        ) 
        AS foo) > 0 
  THEN
EXECUTE 'UPDATE label_point SET label_sample = ' ||  quote_literal(NEW.label)  || ' WHERE ST_Within(label_point.geom, $1)'
        USING NEW.geom;

   raise notice 'UPDATE label_point SET label_sample = % WHERE ST_Within(label_point.geom, %)', new_label, NEW.geom;

   raise notice'Label found: %', NEW.label;
    RAISE Notice 'Intersection found!';
    RETURN NEW; 

  ELSE 
  EXECUTE 'UPDATE label_point SET label_sample = NULL WHERE ST_Within(label_point.geom, $2)'
        USING NEW.geom, OLD.geom;
  RAISE NOTICE 'no intersection (anymore) of feature with gid=%', NEW.gid;
  RETURN NEW; 
  END IF; 
END IF; 
RAISE NOTICE 'Soil-trigger ends now: %', now();
END; 
$BODY$ 
LANGUAGE plpgsql;

CREATE TRIGGER label_soil_trigger
BEFORE INSERT OR UPDATE ON soil
FOR EACH ROW EXECUTE PROCEDURE soil_label();



------------------------------------------------------

-- Delete Trigger

CREATE OR REPLACE FUNCTION public.before_delete_soil()
  RETURNS trigger AS
$BODY$
BEGIN
 RAISE NOTICE 'Trigger % of table % is active % % 
 for record %', TG_NAME, TG_RELNAME, TG_WHEN, TG_OP,
                OLD.label;
 RAISE NOTICE 'Label % was deleted for Point with gid=%', 
               OLD.label, OLD.gid;
 UPDATE label_point SET label_sample = NULL WHERE ST_Within(label_point.geom, OLD.geom);
 RETURN OLD;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.before_delete_soil()
  OWNER TO postgres;


CREATE TRIGGER trigger_before_delete_soil
  BEFORE DELETE
  ON public.soil
  FOR EACH ROW
  EXECUTE PROCEDURE public.before_delete_soil();

EDIT1: Updated/Cleaned Code:

I have cleaned the code and build in one check to prevent recursive execution of the trigger. Now it works pretty fast:

BEGIN;

-------------------------------------------------------

-- CREATE test tables

CREATE TABLE label_point (
gid serial NOT NULL,
geom geometry(point, 3857),
label_sample varchar(255),
CONSTRAINT label_point_pkey PRIMARY KEY (gid)
);

CREATE TABLE soil (
gid serial NOT NULL,
geom geometry(polygon, 3857),
label varchar(255),
CONSTRAINT soil_pkey PRIMARY KEY (gid)
);


-------------------------------------------------------

-- Trigger function for label_point layer

CREATE OR REPLACE FUNCTION sample_label_point() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
IF TG_OP = 'INSERT'
THEN
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
        soil AS s
        WHERE st_Within(NEW.geom, s.geom)) AS foo) > 0
  THEN
    SELECT soil.label
INTO NEW.label_sample
FROM soil
WHERE ST_Intersects(NEW.geom, soil.geom);
    RETURN NEW; 
  ELSE
    RETURN NEW;
  END IF;
ELSIF TG_OP = 'UPDATE'
THEN
IF
(ST_Equals(NEW.geom , OLD.geom)=FALSE)
THEN
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
        soil AS s
        WHERE st_Within(NEW.geom, s.geom)
        ) AS foo) > 0 
  THEN
SELECT soil.label
INTO NEW.label_sample
FROM soil
WHERE ST_Intersects(NEW.geom, soil.geom);
    RETURN NEW; 
  ELSE 
  SELECT NULL
    INTO NEW.label_sample;
  RETURN NEW; 
  END IF; 
ELSE
Return NEW;
END IF;
END IF; 
END; 
$BODY$ 
LANGUAGE plpgsql;

-------------------------------------------------------

-- CREATE TRIGGER for label_point layer

 CREATE TRIGGER label_point_trigger_insert
  BEFORE INSERT 
  ON public.label_point
  FOR EACH ROW
  EXECUTE PROCEDURE public.sample_label_point();

    CREATE TRIGGER label_point_trigger_update
  BEFORE UPDATE OF geom
  ON public.label_point
  FOR EACH ROW
    WHEN (OLD.geom IS DISTINCT FROM NEW.geom) 
  EXECUTE PROCEDURE public.sample_label_point();


-------------------------------------------------------

-- Trigger function for soil_label layer

CREATE OR REPLACE FUNCTION soil_label() RETURNS TRIGGER AS
$BODY$
DECLARE
new_label text := quote_ident(NEW.label);  -- assign at declaration
BEGIN
IF TG_OP = 'INSERT'
THEN
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
        soil AS s
        WHERE st_Within(t.geom, NEW.geom)) AS foo) > 0
  THEN
    EXECUTE 'UPDATE label_point SET label_sample = $2 WHERE ST_Within(label_point.geom, $1)'
        USING NEW.geom, NEW.label;
    RETURN NEW; 
  ELSE
    RETURN NEW;
  END IF;
ELSIF TG_OP = 'UPDATE'
THEN
  IF
    (SELECT COUNT(*)
     FROM
       (SELECT t.gid
        FROM label_point AS t,
        soil AS s
        WHERE st_Within(t.geom, NEW.geom)
        ) 
        AS foo) > 0 
  THEN
EXECUTE 'UPDATE label_point SET label_sample = ' ||  quote_literal(NEW.label)  || ' WHERE ST_Within(label_point.geom, $1)'
        USING NEW.geom;
    RETURN NEW; 
  ELSE 
  EXECUTE 'UPDATE label_point SET label_sample = NULL WHERE ST_Within(label_point.geom, $2)'
        USING NEW.geom, OLD.geom;
  RETURN NEW; 
  END IF; 
END IF; 
END; 
$BODY$ 
LANGUAGE plpgsql;


-------------------------------------------------------

-- CREATE TRIGGER for soil layer


CREATE TRIGGER label_soil_trigger_insert
  BEFORE INSERT
  ON public.soil
  FOR EACH ROW
  EXECUTE PROCEDURE public.soil_label();


CREATE TRIGGER label_soil_trigger_update
  BEFORE UPDATE OF geom
  ON public.soil
  FOR EACH ROW
  WHEN (OLD.geom IS DISTINCT FROM NEW.geom) 
  EXECUTE PROCEDURE public.soil_label();



------------------------------------------------------

-- Delete Trigger

CREATE OR REPLACE FUNCTION public.before_delete_soil()
  RETURNS trigger AS
$BODY$
BEGIN
 UPDATE label_point SET label_sample = NULL WHERE ST_Within(label_point.geom, OLD.geom);
 RETURN OLD;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER trigger_before_delete_soil
  BEFORE DELETE
  ON public.soil
  FOR EACH ROW
  EXECUTE PROCEDURE public.before_delete_soil();

  COMMIT;

https://gist.github.com/neogis-de/27bcf7ee4f36a93fd62e

Best Answer

Related Solutions

[GIS] Trigger to fill attributes (data, client ip) in table for each new objects, PostGIS

[GIS] How to use interacting PostgreSQL/PostGIS triggers in QGIS to create multiple labels on polygons

Related Question