[postgis-devel] [PostGIS] #1705: Get constraint-based columns in views to register correctly

[PostGIS]

#1705: Get constraint-based columns in views to register correctly
-------------------------+--------------------------------------------------
 Reporter:  robe         |       Owner:  robe         
     Type:  enhancement  |      Status:  new          
 Priority:  medium       |   Milestone:  PostGIS 2.1.0
Component:  postgis      |     Version:  trunk        
 Keywords:               |  
-------------------------+--------------------------------------------------
Description changed by robe:

Old description:

> right now we have the case (which I've described in the docs)
>

> {{{CREATE TABLE pois_ny(gid SERIAL PRIMARY KEY
>    , poi_name text, cat varchar(20)
>    , geom geometry(POINT,4326) );
> SELECT AddGeometryColumn('pois_ny', 'geom_2160', 2160, 'POINT', 2,
> false);
>
> CREATE VIEW vw_pois_ny_parks AS
> SELECT *
>   FROM pois_ny
>   WHERE cat='park';
>
> SELECT f_table_name, f_geometry_column, srid, type
>         FROM geometry_columns
>         WHERE f_table_name LIKE '%pois_ny%';
> }}}
>
> Yields:
>
> {{{
>   f_table_name   | f_geometry_column | srid |   type
> -----------------+-------------------+------+----------
> pois_nyc         | geom              | 4326 | POINT
> pois_ny          | geom              | 4326 | POINT
> pois_ny          | geom_2160         | 2160 | POINT
> vw_pois_ny_parks | geom              | 4326 | POINT
> vw_pois_ny_parks | geom_2160         |    0 | GEOMETRY
> }}}
>
> Observe how the typmod based derived view column registers correctly, but
> the constraint based derived one does not.
>
> This is an issue I've been aware of for quite sometime, but haven't
> decided if the extra timing needed to do the extra hop for the constraint
> based view one is worth it.  Sadly I've got a lot of views like this
> which I can patch by recreating the view and explicitly casting the
> column in the view, but it would be nice if I didn't need to. For many
> cases, I also can't convert these to typmod since they involve
> inheritance hierarchies where the geometry type of each child table is
> not the same.

New description:

 right now we have the case (which I've described in the docs)

 {{{
 CREATE TABLE pois_ny(gid SERIAL PRIMARY KEY
    , poi_name text, cat varchar(20)
    , geom geometry(POINT,4326) );
 SELECT AddGeometryColumn('pois_ny', 'geom_2160', 2160, 'POINT', 2, false);

 CREATE VIEW vw_pois_ny_parks AS
 SELECT *
   FROM pois_ny
   WHERE cat='park';

 SELECT f_table_name, f_geometry_column, srid, type
         FROM geometry_columns
         WHERE f_table_name LIKE '%pois_ny%';
 }}}


 Yields:

 {{{
   f_table_name   | f_geometry_column | srid |   type
 -----------------+-------------------+------+----------
 pois_nyc         | geom              | 4326 | POINT
 pois_ny          | geom              | 4326 | POINT
 pois_ny          | geom_2160         | 2160 | POINT
 vw_pois_ny_parks | geom              | 4326 | POINT
 vw_pois_ny_parks | geom_2160         |    0 | GEOMETRY
 }}}

 Observe how the typmod based derived view column registers correctly, but
 the constraint based derived one does not.

 This is an issue I've been aware of for quite sometime, but haven't
 decided if the extra timing needed to do the extra hop for the constraint
 based view one is worth it.  Sadly I've got a lot of views like this which
 I can patch by recreating the view and explicitly casting the column in
 the view, but it would be nice if I didn't need to. For many cases, I also
 can't convert these to typmod since they involve inheritance hierarchies
 where the geometry type of each child table is not the same.

--

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/1705#comment:1>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.