I have 2 tables that are more or less like this
CREATE TABLE localizzazione.etichette_sentieri( gid serial PRIMARY KEY, cod_grid character varying(10) NOT NULL, geom geometry(Point,32632), CONSTRAINT etichette_sentieri_cod_grid_fkey FOREIGN KEY (cod_grid) REFERENCES base.tabacco_grid (cod_grid) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT);CREATE TABLE base.tabacco_grid( cod_grid character varying(10) PRIMARY KEY, geom geometry(Polygon,32632));I tried to write a function for checking if the point is inside the polygon and if they have the same cod_grid.Something like that:
CREATE OR REPLACE FUNCTION verifica_cod_grid() RETURNS trigger AS $BODY$DECLARE conta integer;BEGIN SELECT count(*) INTO conta FROM base.tabacco_grid as grid WHERE new.cod_grid=grid.cod_grid AND st_intersects(new.geom,grid.geom);IF conta=1 THEN RAISE USING MESSAGE = 'cod_grid of the feature with gid '||new.gid||' is not correct'; ELSE RETURN new; END IF;END$BODY$ LANGUAGE 'plpgsql';The trigger in the table is
DROP TRIGGER etichette_sentieri_verifica_cod_grid ON localizzazione.etichette_sentieri;CREATE TRIGGER etichette_sentieri_verifica_cod_grid BEFORE INSERT OR UPDATE ON localizzazione.etichette_sentieri FOR EACH ROW EXECUTE PROCEDURE verifica_cod_grid();The problem is that if I made a new insert all works fine but if I move a point in another polygon (I use Qgis 2.6.1) I always receive the error message, even if the cod_grid is correct. It looks like he try to update geometry before cod_grid.
I don't understand what I have done wrong... can somebody help me?
أكثر...
CREATE TABLE localizzazione.etichette_sentieri( gid serial PRIMARY KEY, cod_grid character varying(10) NOT NULL, geom geometry(Point,32632), CONSTRAINT etichette_sentieri_cod_grid_fkey FOREIGN KEY (cod_grid) REFERENCES base.tabacco_grid (cod_grid) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT);CREATE TABLE base.tabacco_grid( cod_grid character varying(10) PRIMARY KEY, geom geometry(Polygon,32632));I tried to write a function for checking if the point is inside the polygon and if they have the same cod_grid.Something like that:
CREATE OR REPLACE FUNCTION verifica_cod_grid() RETURNS trigger AS $BODY$DECLARE conta integer;BEGIN SELECT count(*) INTO conta FROM base.tabacco_grid as grid WHERE new.cod_grid=grid.cod_grid AND st_intersects(new.geom,grid.geom);IF conta=1 THEN RAISE USING MESSAGE = 'cod_grid of the feature with gid '||new.gid||' is not correct'; ELSE RETURN new; END IF;END$BODY$ LANGUAGE 'plpgsql';The trigger in the table is
DROP TRIGGER etichette_sentieri_verifica_cod_grid ON localizzazione.etichette_sentieri;CREATE TRIGGER etichette_sentieri_verifica_cod_grid BEFORE INSERT OR UPDATE ON localizzazione.etichette_sentieri FOR EACH ROW EXECUTE PROCEDURE verifica_cod_grid();The problem is that if I made a new insert all works fine but if I move a point in another polygon (I use Qgis 2.6.1) I always receive the error message, even if the cod_grid is correct. It looks like he try to update geometry before cod_grid.
I don't understand what I have done wrong... can somebody help me?
أكثر...