I believe that the more correct solution is to change mapserver's postgis 
connector to properly translate 

"geom from foo.bar" 

to have a find_srid call of: 

"find_srid( 'foo', 'bar', 'geom' )

However, your "hack" does seem reasonable, considering it really shouldn't 
break anything, and fixes this mapserver/postgis 7.3 problem quickly and easily.

Let's see what Dave and Paul think...

Chris

Quoting Carl Anderson <carl.anderson@vadose.org>:

> 
> 
> 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.
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 





