I want to set/update attributes (alignment_horizontal, alignment_vertical) of a PostGIS geometry (point) by using a trigger.
CREATE TABLE point (gid serial NOT NULL,geom geometry(point, SRID),label varchar(255),x_cord double precision,y_cord double precision,alignment_horizontal varchar(255),alignment_vertical varchar(255),CONSTRAINT point_pkey PRIMARY KEY (gid));CREATE TRIGGER label_alignment_trigger BEFORE INSERT OR UPDATE ON point FOR EACH ROW EXECUTE PROCEDURE label_alignment_triggerfn();Is there any way to to set/update two columns in one trigger function?
CREATE OR REPLACE FUNCTION label_alignment_triggerfn()RETURNS trigger AS $body$ BEGIN/** ??? -- alignment_horizontal = CASE WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) < 22.5 THEN 'Center' WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) < 112.5 THEN 'Left' WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) < 202.5 THEN 'Center' WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) < 292.5 THEN 'Right' WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) >= 292.5 THEN 'Center' END-- alignment_vertical = CASE WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) < 22.5 THEN 'Bottom' WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) < 112.5 THEN 'Half' WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) < 202.5 THEN 'Top' WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) < 292.5 THEN 'Half' WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) >= 292.5 THEN 'Bottom' END??**/ END;$body$ LANGUAGE plpgsql;
أكثر...
CREATE TABLE point (gid serial NOT NULL,geom geometry(point, SRID),label varchar(255),x_cord double precision,y_cord double precision,alignment_horizontal varchar(255),alignment_vertical varchar(255),CONSTRAINT point_pkey PRIMARY KEY (gid));CREATE TRIGGER label_alignment_trigger BEFORE INSERT OR UPDATE ON point FOR EACH ROW EXECUTE PROCEDURE label_alignment_triggerfn();Is there any way to to set/update two columns in one trigger function?
CREATE OR REPLACE FUNCTION label_alignment_triggerfn()RETURNS trigger AS $body$ BEGIN/** ??? -- alignment_horizontal = CASE WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) < 22.5 THEN 'Center' WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) < 112.5 THEN 'Left' WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) < 202.5 THEN 'Center' WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) < 292.5 THEN 'Right' WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) >= 292.5 THEN 'Center' END-- alignment_vertical = CASE WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) < 22.5 THEN 'Bottom' WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) < 112.5 THEN 'Half' WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) < 202.5 THEN 'Top' WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) < 292.5 THEN 'Half' WHEN degrees(ST_Azimuth(geom, ST_Point(x_cord, y_cord))) >= 292.5 THEN 'Bottom' END??**/ END;$body$ LANGUAGE plpgsql;
أكثر...