5

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"

2 Answers 2

5

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.

2
  • I get a syntax error executing this: ERROR: syntax error at or near "TRIGGER" Position: 66
    – Lokomotywa
    May 17 at 7:21
  • 1
    @Lokomotywa sorry, the RETURN statement of a function definition has to follow the signature - I corrected that.
    – geozelot
    May 17 at 7:31
0

Something using st_multi should do it:

UPDATE mytable set geom = st_multi(polygon);

should do it for you.

1
  • I need to merge the new polygon with those which are already part of the multipolygon, so this would most probably not do the job.
    – Lokomotywa
    May 16 at 15:10

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.