3

I have the following view

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

on a table mytable with a geom column of the following type geometry(multipolygon, 25832)

How do I make the geom column of the view have the type geometry(polygon, 25832)?

0

1 Answer 1

6

You need to properly cast the column type to include the respective type modifiers like so:

CREATE OR REPLACE VIEW myview AS
  SELECT
    a.id,
    ST_SetSRID(dmp.geom, 25832)::GEOMETRY(POLYGON, 25832) AS geom
  FROM
    mytable a,
    LATERAL ST_Dump(a.geom) AS dmp
;

The (implicit CROSS JOIN) LATERAL construct is an elegant way to expand the result of set-returning functions - the above query does the exact same as your row-type expansion using (<row>).<column>.

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.