[postgis-tickets] [PostGIS] #2511: geometry_columns doesn't support materialized views or foreign tables

[PostGIS]

#2511: geometry_columns doesn't support materialized views or foreign tables
----------------------+-----------------------------------------------------
  Reporter:  pramsey  |       Owner:  pramsey      
      Type:  defect   |      Status:  reopened     
  Priority:  medium   |   Milestone:  PostGIS 2.0.5
 Component:  postgis  |     Version:  2.0.x        
Resolution:           |    Keywords:               
----------------------+-----------------------------------------------------

Comment(by robe):

 {{{
 CREATE OR REPLACE VIEW geometry_columns AS
   SELECT current_database()::varchar(256) AS f_table_catalog,
     n.nspname::varchar(256) AS f_table_schema,
     c.relname::varchar(256) AS f_table_name,
     a.attname::varchar(256) AS f_geometry_column,
     COALESCE(NULLIF(postgis_typmod_dims(a.atttypmod),2),
              postgis_constraint_dims(n.nspname, c.relname, a.attname),
              2) AS coord_dimension,
     COALESCE(NULLIF(postgis_typmod_srid(a.atttypmod),0),
              postgis_constraint_srid(n.nspname, c.relname, a.attname),
              0) AS srid,
     -- force to be uppercase with no ZM so is backwards compatible
     -- with old geometry_columns
     replace(
       replace(
         COALESCE(
           NULLIF(upper(postgis_typmod_type(a.atttypmod)::text),
 'GEOMETRY'),
           postgis_constraint_type(n.nspname, c.relname, a.attname),
           'GEOMETRY'
         ), 'ZM', ''
       ), 'Z', ''
     )::varchar(30) AS type
   FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n
   WHERE t.typname = 'geometry'::name
     AND a.attisdropped = false
     AND a.atttypid = t.oid
     AND a.attrelid = c.oid
     AND c.relnamespace = n.oid
     AND (c.relkind = 'r'::"char" OR c.relkind = 'v'::"char" OR c.relkind =
 'm'::"char" OR c.relkind = 'f'::"char" )
     AND NOT pg_is_other_temp_schema(c.relnamespace)
     AND NOT ( n.nspname = 'public' AND c.relname = 'raster_columns' )
     AND has_table_privilege( c.oid, 'SELECT'::text );
 }}}


 above is the revised to handle foreign table wrappers. My FDW geometry
 column shows up correctly with that.  I would commit but not near my dev
 environment and strk says I can't commit if I can't test and no counting
 on bots to do my dirty work.  We should probably also get rid of the
 n.nspname = 'public' while we are at it, because if you install postgis in
 its own schema it's slightly annoying that raster_columns appears in
 geometry_columns list.

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/2511#comment:7>
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.