One of my PostgreSQL/PostGIS databases stores time series data. Every year contains a polygon geometry table and a point geometry table.
CREATE TABLE data_2014.polygon (gid serial NOT NULL,geom geometry(polygon, SRID),attribute01 varchar(255),CONSTRAINT polygon_pkey PRIMARY KEY (gid)) CREATE TABLE data_2014.point (gid serial NOT NULL,geom geometry(polygon, SRID),attribute01_from_polygon varchar(255),CONSTRAINT point_pkey PRIMARY KEY (gid))CREATE TABLE data_2015.polygon (gid serial NOT NULL,geom geometry(polygon, SRID),attribute01 varchar(255),CONSTRAINT polygon_pkey PRIMARY KEY (gid)) CREATE TABLE data_2015.point (gid serial NOT NULL,geom geometry(polygon, SRID),attribute01_from_polygon varchar(255),CONSTRAINT point_pkey PRIMARY KEY (gid)) -- data_2016, data_2017, ...Now I want to use a trigger to create a new ST_PointOnSurface point feature when adding a new polygon feature:
INSERT INTO TG_TABLE_SCHEMA.point (geom, attribute01_from_polygon) SELECT ST_PointOnSurface(geom), attribute01 FROM TG_TABLE_SCHEMA.polygon;Can someone help me with the trigger function? I want to store it in the public schema.
CREATE TRIGGER pointonsurface_polygon_tg BEFORE INSERT OR UPDATEON insert_schema_here.polygon FOR EACH ROWEXECUTE PROCEDURE pointonsurface_polygon();CREATE OR REPLACE FUNCTION pointonsurface_polygon()RETURNS trigger AS $body$ BEGIN EXECUTE --? USING --? RETURN --? END;$body$ LANGUAGE plpgsql;
أكثر...
CREATE TABLE data_2014.polygon (gid serial NOT NULL,geom geometry(polygon, SRID),attribute01 varchar(255),CONSTRAINT polygon_pkey PRIMARY KEY (gid)) CREATE TABLE data_2014.point (gid serial NOT NULL,geom geometry(polygon, SRID),attribute01_from_polygon varchar(255),CONSTRAINT point_pkey PRIMARY KEY (gid))CREATE TABLE data_2015.polygon (gid serial NOT NULL,geom geometry(polygon, SRID),attribute01 varchar(255),CONSTRAINT polygon_pkey PRIMARY KEY (gid)) CREATE TABLE data_2015.point (gid serial NOT NULL,geom geometry(polygon, SRID),attribute01_from_polygon varchar(255),CONSTRAINT point_pkey PRIMARY KEY (gid)) -- data_2016, data_2017, ...Now I want to use a trigger to create a new ST_PointOnSurface point feature when adding a new polygon feature:
INSERT INTO TG_TABLE_SCHEMA.point (geom, attribute01_from_polygon) SELECT ST_PointOnSurface(geom), attribute01 FROM TG_TABLE_SCHEMA.polygon;Can someone help me with the trigger function? I want to store it in the public schema.
CREATE TRIGGER pointonsurface_polygon_tg BEFORE INSERT OR UPDATEON insert_schema_here.polygon FOR EACH ROWEXECUTE PROCEDURE pointonsurface_polygon();CREATE OR REPLACE FUNCTION pointonsurface_polygon()RETURNS trigger AS $body$ BEGIN EXECUTE --? USING --? RETURN --? END;$body$ LANGUAGE plpgsql;
أكثر...