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;