From: | Paul Ramsey <pramsey(at)refractions(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, Bruce David <brucedavid(at)bigfoot(dot)com>, pgsql-hackers(at)postgresql(dot)org, David Blasby <dblasby(at)refractions(dot)net> |
Subject: | Re: Trigger on 'create table' ? |
Date: | 2002-10-30 00:56:31 |
Message-ID: | 3DBF2E3F.2050607@refractions.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
\_
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2002-10-30 02:15:06 | Re: float output precision questions |
Previous Message | Stephan Szabo | 2002-10-30 00:22:31 | Re: float output precision questions |