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_NAME VARCHAR(256) NOT NULL,
F_GEOMETRY_COLUMN VARCHAR(256) NOT NULL,
COORD_DIMENSION INTEGER,
SRID INTEGER REFERENCES SPATIAL_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(at)refractions(dot)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(at)refractions(dot)net
| Phone: (250) 885-0632
\_