Re: Trigger on 'create table' ? - Mailing list pgsql-hackers

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
>>
> 
>