Adding a polygon to a multipolygon column

Question

I have a table that contains a multipolygon column.

In my application, I use a view on the table which splits the multipolygon in several polygons:

CREATE VIEW myview
AS SELECT a.id,
    ST_SetSRID((st_dump(a.geom)).geom,25832) AS geom
   FROM mytable a;

I want to declare a rule on that view, that I can perform an update operation on mytable in order that a newly added polygon gets added to the multipolygon column.

CREATE RULE insert_geom_to_myview AS ON INSERT TO myview
    DO instead UPDATE mytable set geom = "add_new_polygon_to_multipolygon"

Answer 1

I strongly recommend using a trigger to work this out - apart from some other things they have less confusing side effects, and can be extended much easier:

CREATE FUNCTION my_view__instead_of__insert__trigger_function()
  RETURNS TRIGGER AS
  $$
    BEGIN
      UPDATE mytable
         SET geom = ST_Multi(ST_Union(geom, NEW.geom))
      WHERE  id = NEW.id
      ;

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

CREATE TRIGGER my_view__instead_of__insert
  INSTEAD OF INSERT ON myview
    FOR EACH ROW
    EXECUTE PROCEDURE my_view__instead_of__insert__trigger_function()
;

---

Note:

This is a minimal trigger implementation: as with any kind of insert handling mechanic you will want to add validity checks, graceful error handling and most of all, a guarantee that you will always update by a single, valid id only.

Answer 2

Something using st_multi should do it:

UPDATE mytable set geom = st_multi(polygon);

should do it for you.