5

The trigger below don't works and I need that when a new pipeline (adutoras) is inserted or updated, the procedure check what are the municipalities (municipios) that intersects the pipes and put the names of the municipalities inside the field "municipios_abastecidos".

CREATE OR REPLACE FUNCTION topology_fields() 
RETURNS "trigger" AS
$$
BEGIN
NEW.municipios_abastecidos:=(
                      SELECT string_agg(municipios.nome,', ')
                      FROM municipios, adutoras
                      WHERE ST_Intersects(adutoras.geom, municipios.geom)
                      AND adutoras.gid = NEW.gid
                    );
RETURN NEW;
END; 
$$
LANGUAGE 'plpgsql';


CREATE TRIGGER fill_topology_fields
BEFORE INSERT OR UPDATE ON adutoras
FOR EACH ROW EXECUTE PROCEDURE topology_fields();

1 Answer 1

4

Solved,

CREATE OR REPLACE FUNCTION topology_fields()
RETURNS trigger AS
$$
BEGIN

SELECT string_agg(municipios.nome,', ')
INTO NEW.municipios_abastecidos
FROM municipios
WHERE ST_Intersects(NEW.geom, municipios.geom);

RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER fill_topology_fields
BEFORE INSERT OR UPDATE ON adutoras
FOR EACH ROW EXECUTE PROCEDURE topology_fields();

Appreciate the help from PostGIS-User List, especially Ralf Suhr and Tom Glancy, thank you so much!

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.