I have a table that keeps permissions for other tables in the database.
What I want to do is create a rule that will insert into the permissions
table a default permission whenever a table row is inserted. Within the
permission table, I keep the default permissions to use for each table.
I index these by using a table_id=0. So, the rule would need to get the
default permission, and insert a new row into the permissions table. The
(abbreviated) perm table would look something like this:
CREATE TABLE perm (
id SERIAL,
table_name varchar(30),
table_id integer,
permission integer
)
example default settings for each table
---------------------------------------
INSERT INTO perm ('table1', 0, 1);
INSERT INTO perm ('table2', 0, 1);
.
.
.
so, whenever a row in another table is inserted, I want to update the
perm table with the default perm.
I tried this rule:
CREATE RULE insert_perm_table1 AS
ON INSERT TO table1
DO
INSERT INTO perm (table_name, table_id, permission) SELECT
table_name, new.table1_id, permission
FROM perm
WHERE table_name='table1' and table_id=0;
So, basically I am taking the default entry, and substituting the
table_id of 0 for the new one, and then inserting. The rule executes,
but I get different table_ids for the 2 tables (table1 and perm). The
table1 entry has an 'table_id' of one greater than the perm table entry.
Anyone have any idea why? Is there a better solution (triggers maybe)?
thanks,
--brett
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com