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?