[postgis-users] find_srid() change request

[Carl Anderson]

To allow support for schema enabled Postgis in Mapserver 6.3
I request that find_srid () be changed in the following way.
This is clearly a hack but will extend the use of PostGIS w/ schemas
into Mapserver 3.6.x

preserve existing behaviour
extend behavior to automatically promote a namespace.table reference
into a schema in the place of parameter 1
into a table in place of parameter 2

Mapserver 3.6.x  converts

         CONNECTIONTYPE postgis
         DATA 'shape from cities.annex_cases'

into a Binary Cursor containing
  .. find_srid('','cities.annex_cases','shape') ...

We have geotables in schemas to provide a level of sanity and
context to the geotables;

Postgresql 7.3 by default will search the users named schema and then 
the public schema.  This is an unacceptable exposure of the schema owner
to web traffic.  We prefer to use extremely limited accounts for 
mapserver.  So when the Pg user is different from the schema the tables
are in the tables are not found.  The propsed style will allow a user 
to explicitly specify the schema.

the the above example the public.geometry_columns table contains
  f_table_catalog | f_table_schema |  f_table_name  | f_geometry_column 
| coord_dimension | srid |     type     | 
-----------------+----------------+----------------+-------------------+-----------------+------+--------------
                  | cities         | annex_cases    | shape             
|
        2 |   -1 | MULTIPOLYGON 




proposed new  find_srid()
------------------
-- FIND_SRID( <schema/database>, <table>, <geom col> )

CREATE FUNCTION find_srid(varchar,varchar,varchar) RETURNS int4 AS
'DECLARE
   schem text;
   tabl text;
   sr int4;
BEGIN
   schem = $1;
   tabl = $2;
-- if the table contains a . and the schema is empty
-- split the table into a schema and a table
-- otherwise drop through to default behavior
   if ( schem = '''' and tabl ~ ''\.'' ) then
     schem = substr(tabl,1,strpos(tabl,''.'')-1);
     tabl = substr(tabl,length(schem)+2);
   else
     schem = schem || ''%'';
   end if;

   select SRID into sr from geometry_columns where f_table_schema like 
schem and f_table_name = tabl and f_geometry_column = $3;

  return sr;
END;
'
LANGUAGE 'plpgsql' WITH (iscachable,isstrict); 


C.