From: | Brett Schwarz <brett_schwarz(at)yahoo(dot)com> |
---|---|
To: | |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | RRules using existing data |
Date: | 2001-09-17 21:55:07 |
Message-ID: | 3BA6713B.EDE63153@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2001-09-17 21:56:42 | Re: Practical Cursors |
Previous Message | Jeff Eckermann | 2001-09-17 21:52:42 | Re: Left Joins |
From | Date | Subject | |
---|---|---|---|
Next Message | Lamar Owen | 2001-09-17 22:18:13 | Re: 7.2 RPMs |
Previous Message | Lamar Owen | 2001-09-17 21:52:00 | Re: 7.2 RPMs |