Re: [HACKERS] Trigger on 'create table' ?
Paul Ramsey [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Trigger on 'create table' ?
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Trigger on 'create table' ?
Paul Ramsey [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] | Phone: (250) 885-0632 \_ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Trigger on 'create table' ?
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 ( cityvarchar(80), loint, hiint, prcpreal, datedate ); would result in the following being executed automatically: CREATE TABLE t_weather ( cityvarchar(80), loint, hiint, prcpreal, datedate, hidden_columnint ); 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
Re: [HACKERS] 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 ( cityvarchar(80), loint, hiint, prcpreal, datedate ); would result in the following being executed automatically: CREATE TABLE t_weather ( cityvarchar(80), loint, hiint, prcpreal, datedate, hidden_columnint ); 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 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 ( cityvarchar(80), loint, hiint, prcpreal, datedate ); would result in the following being executed automatically: CREATE TABLE t_weather ( cityvarchar(80), loint, hiint, prcpreal, datedate, hidden_columnint ); 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
