Re: Trigger on 'create table' ?

So close... It is a testament to my blockheadedness that I had not 
considered that (Dave probably did). Here is the standards-mandated 
definition of the table:

CREATE TABLE GEOMETRY_COLUMNS (  F_TABLE_CATALOG VARCHAR(256) NOT NULL,  F_TABLE_SCHEMA VARCHAR(256) NOT NULL,
F_TABLE_NAMEVARCHAR(256) NOT NULL,  F_GEOMETRY_COLUMN VARCHAR(256) NOT NULL,  COORD_DIMENSION INTEGER,  SRID INTEGER
REFERENCESSPATIAL_REF_SYS,  CONSTRAINT GC_PK PRIMARY KEY
 
(F_TABLE_CATALOG, F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN)
)

Everything except for COORD_DIMENSION and SRID could be extracted from 
the pg_class table as a view, which would be nice. The COORD_DIMENSION 
is just the dimensionality of the geometries, and the SRID is the 
spatial reference system identifier, which is a key into another 
mandated OpenGIS table, 'SPATIAL_REF_SYS'.

Now, if we changed the geometry type so that when one defined a geometry 
column, one had to include info about what SRID and what dimension it 
was, (ala varchar(243)) maybe the whole schmeer could reside in pg_class 
and geometry_columns would be a trivial view?

Crazy? Loony?

P.

Tom Lane wrote:
> Paul Ramsey <pramsey@refractions.net> writes:
> 
>>We have a similar requirement for PostGIS.
>>In order to meet the OpenGIS specification, we have to maintain a 
>>GEOMETRY_COLUMNS table which includes an entry for every column in the 
>>db which holds a spatial column. It would be ideal if we could have 
>>triggers run on CREATE TABLE, DROP TABLE, and ALTER TABLE to watch for 
>>the comings and goings of spatial columns and update GEOMETRY_COLUMNS 
>>appropriately.
> 
> 
> Couldn't you define GEOMETRY_COLUMNS as a view?  Seems a lot more
> efficient than firing random triggers on every table operation.
> 
>             regards, tom lane
> 


--       __     /     | Paul Ramsey     | Refractions Research     | Email: pramsey@refractions.net     | Phone: (250)
885-0632    \_