Opened 8 years ago

Last modified 2 years ago

#1705 new enhancement

Get constraint-based columns in views to register correctly

Reported by: robe Owned by: robe
Priority: medium Milestone: PostGIS Fund Me
Component: postgis Version: master
Keywords: Cc:

Description (last modified by robe)

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.

Change History (5)

comment:1 Changed 8 years ago by robe

Description: modified (diff)

comment:2 Changed 7 years ago by robe

Milestone: PostGIS 2.1.0PostGIS 2.2.0

comment:3 Changed 4 years ago by robe

Milestone: PostGIS 2.2.0PostGIS 2.3.0

comment:4 Changed 3 years ago by robe

Milestone: PostGIS 2.3.0PostGIS Future

comment:5 Changed 2 years ago by robe

Milestone: PostGIS FuturePostGIS Fund Me

Milestone renamed

Note: See TracTickets for help on using tickets.