Re: Trigger on 'create table' ?

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.
Unfortunately, triggers seem to not be allowed on system tables, or we
would just watch pg_class for new geometry columns and update the
OpenGIS table as necessary. :/

Bruce Momjian wrote:
> Sorry, I can't think of any to have a view created automatically for
> every table creation. Interesting idea, though. I wonder if you could
> pass the CREATE string into a function that does the proper creation.
>
> ---------------------------------------------------------------------------
>
> Bruce David wrote:
>
>>All,
>>
>>I'm trying to implement row level security using PostgreSQL. I envision
>>having column on each and every table in the database that will contain a
>>discriminating value that determines if a given user is able to see that row
>>or not. Users do not directly access the database; they get at it only
>>through client applications.
>>
>>Is there a way to initially configure PostgreSQL such that each and every
>>time a 'create table foo....' command is executed that, something else would
>>happen instead?
>>
>>For example,
>>
>>CREATE TABLE weather (
>> city varchar(80),
>> lo int,
>> hi int,
>> prcp real,
>> date date
>>);
>>
>>would result in the following being executed automatically:
>>
>>CREATE TABLE t_weather (
>> city varchar(80),
>> lo int,
>> hi int,
>> prcp real,
>> date date,
>> hidden_column int
>>);
>>
>>CREATE VIEW weather AS
>> SELECT city, lo, hi, prcp, date
>> FROM t_weather;
>>
>>
>>How the view gets populated is another discussion entirely.
>>
>>But is there a way to set things up initially such that each 'create table'
>>command will exhibit the behavior described above?
>>
>>Thanks.
>>
>>--
>>
>>Bruce
>>
>>
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: Have you checked our extensive FAQ?
>>
>>http://www.postgresql.org/users-lounge/docs/faq.html
>>
>
>