One of my PostgreSQL databases contains different schemas which share the same structure.
-- schema region_aCREATE 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_bCREATE 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 UPDATEON point FOR EACH ROWEXECUTE 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.
أكثر...
-- schema region_aCREATE 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_bCREATE 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 UPDATEON point FOR EACH ROWEXECUTE 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.
أكثر...