Trigger on 'create table' ?

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

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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:

Paul Ramsey writes:

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.

Why not make a view?

--
Peter Eisentraut peter_e@gmx.net

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

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@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
\_

Paul Ramsey <pramsey@refractions.net> writes:

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?

atttypmod would be a natural place for that stuff, if you can fit it
into 31 bits. The bigger problem is how are you going to get
type-specific syntax through the Postgres parser? Currently, all the
types that use typmod have to have special-purpose syntax to set it.
I'm not eager to see extension datatypes trying to do the same thing...

regards, tom lane