[postgis-users] shp2pgsql and schemas

[Carl Anderson]

On 2003.07.04 11:55 Paul Ramsey wrote:
> Now that schemas actually *exist* in pgsql, our behavior is in wrong. 
> The schema idea can be literally and correctly translated into pgsql 
> terms. So AddGeometryColumn should be dumbed down to just
> 
> AddGeometryColumn( <tablename>, <columnname>, <srid>, <type>, <dim> )
> 

There would need to be a period of transition so that client apps can 
work with the old (current) or newer (schema sensitive) style.

So if an app (such as qgis) uses the geometry_columns table as a catalog
of available geometry tables, it should try to look for whatever it 
finds in the f_table_schema and f_table_name fields  ( select * from 
schema.table)

and if that fails and IF the f_table_schema is the same as the 
connected database name (detected not from f_table_catalog)
then a second attempt is made dropping the schema portion.

in such a way users with the current geometry_columns can be supported 
as well as support a newer style.


If I may be so bold,

a document clearly explaining that the geometry_colums table is a 
catalog of postgis enabled tables, and developers should be encouraged 
to honor f_table_catalog, f_table_schema, f_table_name and 
f_geometry_column when looking for things they find in the catalog.

On the other hand applications such as UMN mapserver or OGR/GDAL
which do not use the catalog, would be aware that geometries in tables 
can occur in all the ways the catalog can identify.  UMN mapserver
depends on srid(schema,table,columnname) to find some table info.

While i do not profess to see into the future it seems likely that 
PostgreSQL will one day allow cross database queries (select * from 
db.schema.table).  As such f_table_catalog should be treated as 
meaningful
and interpreted as
      blank string	use current database connection
			(no other possible value)
      any other value	don't use until Postgis supports cross database
			queries

if a seperate database connection would be required to access something
in the catalog (geometry_columns) then it is not put into the catalog.
when PostgreSQL allows access to other databases using the current 
connection then and only then should f_table_catalog be populated.

C.