How to create a trigger for multiple schemas?

Question

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.

Answer 1

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; 

• Lunar Sea: Great, the trigger function works fine after duplicating or renaming a schema!
• Lunar Sea: I also have to update all point geometries within a polygon geometry when editing the polygon geometry. Do I need a second trigger function to do this?
• dbaston: Yes, I'd think so.
• Lunar Sea: I've tried EXECUTE 'UPDATE ' || TG_TABLE_SCHEMA || '.point SET attribute_sample = new.attribute WHERE st_within(point.geom, 1ドル)' USING NEW.geom; RETURN NEW; but QGIS says missing FROM-clause entry for table "new". I suppose there is something wrong with my syntax.
• dbaston: You could set up a trigger on your polygons table, running AFTER INSERT OR UPDATE that sets the attribute_sample in the relevant records of the points table to NULL. It doesn't actually matter what you set the value to, because this update is itself going to trigger a refresh against the just--updated polygon table. Multiple interacting triggers can get confusing very quickly.