0

We are currently working on database with multipolygons and we're using QGIS 3. Sometimes it's very useful to work with parts (each page of document is separate part of multipolygon), however at the end I need as few parts as possible. When I tried to update geometries with St_UnaryUnion I had some problems and finally ended with 'temporary workaround' like this:

update workspace.tests
set geom = geom2 from (
select ST_Multi(ST_UnaryUnion(ST_Multi(geom))) as geom2
from workspace.tests
where id_number = 23) as ge
where id_number = 23;

It works fine, but I have to control each row separately (or write some kind of loop). So I read many stackexchange topics and tried to write my first trigger.

CREATE OR REPLACE FUNCTION workspace.mergpoly() RETURNS trigger AS $mergpoly$
    BEGIN
        NEW.geom := ST_Multi(ST_UnaryUnion(ST_Multi(OLD.geom)));
        NEW.howmanyparts := ST_NumGeometries(NEW.geom);
   RETURN NEW;
    END;
$mergpoly$ LANGUAGE plpgsql;

CREATE TRIGGER mergpoly AFTER INSERT OR UPDATE ON workspace.tests
    FOR EACH ROW EXECUTE PROCEDURE workspace.mergpoly();

The problem is: it does nothing. I tried also other ways but I failed every time. I'm quite new to PLSQL and PostgreSQL. Am I close to good answer or would you write it completely different?

2

The documentation says:

The return value of a row-level trigger fired AFTER … is always ignored; it might as well be null.

To change the new row, you must use a BEFORE trigger.

  • Wow. It works! Thank you. But I also had to change NEW.geom := ST_Multi(ST_UnaryUnion(ST_Multi(OLD.geom))); into NEW.geom := ST_Multi(ST_UnaryUnion(ST_Multi(NEW.geom)));. Otherwise it undo all changes before saving. – Krakatau Jan 8 at 12:23

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

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