Received: from smtp1.mail.yahoo.com (smtp1.mail.yahoo.com [128.11.69.60]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f0QIvHq34942 for ; Fri, 26 Jan 2001 13:57:17 -0500 (EST) (envelope-from janwieck@yahoo.com) Received: from j13.us.greatbridge.com (HELO jupiter.greatbridge.com) (216.54.52.153) by smtp.mail.vip.suc.yahoo.com with SMTP; 26 Jan 2001 18:57:15 -0000 X-Apparently-From: Received: (from janwieck@localhost) by jupiter.greatbridge.com (8.9.3/8.9.3) id OAA03570; Fri, 26 Jan 2001 14:05:30 -0500 From: Jan Wieck Message-Id: <200101261905.OAA03570@jupiter.greatbridge.com> Subject: To: PostgreSQL SQL Date: Fri, 26 Jan 2001 14:05:30 -0500 (EST) CC: [SQL]@jupiter.greatbridge.com, Rule@jupiter.greatbridge.com, not@jupiter.greatbridge.com, invoked@jupiter.greatbridge.com, in@jupiter.greatbridge.com, 7.1@jupiter.greatbridge.com X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200101/392 X-Sequence-Number: 1065 Kyle wrote: > The only complication is > that there are a class of records which the user should be able to view, but not > modify. For example, > the employee can create and modify working records as long as the only > modification to their status > is to move them on to "open status" (creating an "approved" record would be a bad > idea.) > > But the user should be able to view all their records (working, open, approved, > and even paid). > > Hence, the restrictions on update are more stringent than those on select. Ah. Describe the entire problem and you'll get a complete answer: CREATE TABLE wr_table ( w_id serial PRIMARY KEY, w_user name, w_state text, w_data text ); CREATE CREATE VIEW wr_view AS SELECT * FROM wr_table WHERE w_user = CURRENT_USER; CREATE CREATE RULE wr_view_ins AS ON INSERT TO wr_view DO INSTEAD INSERT INTO wr_table (w_user, w_state, w_data) VALUES ( CURRENT_USER, 'OPEN', new.w_data ); CREATE CREATE RULE wr_view_upd AS ON UPDATE TO wr_view DO INSTEAD UPDATE wr_table SET w_data = new.w_data WHERE w_id = old.w_id AND w_state = 'OPEN'; CREATE CREATE RULE wr_view_del AS ON DELETE TO wr_view DO INSTEAD DELETE FROM wr_table WHERE w_id = old.w_id AND w_state = 'OPEN'; CREATE INSERT INTO wr_table (w_user, w_state, w_data) VALUES ('pgsql', 'OPEN', 'Open item 1 of pgsql'); INSERT 19392 1 INSERT INTO wr_table (w_user, w_state, w_data) VALUES ('pgsql', 'OPEN', 'Open item 2 of pgsql'); INSERT 19393 1 INSERT INTO wr_table (w_user, w_state, w_data) VALUES ('pgsql', 'CLOSED', 'Closed item 3 of pgsql'); INSERT 19394 1 INSERT INTO wr_table (w_user, w_state, w_data) VALUES ('someone', 'OPEN', 'Open item of someone else'); INSERT 19395 1 INSERT INTO wr_table (w_user, w_state, w_data) VALUES ('someone', 'CLOSED', 'Closed item of someone else'); INSERT 19396 1 SELECT CURRENT_USER; current_user -------------- pgsql (1 row) SELECT * FROM wr_table ORDER BY w_id; w_id | w_user | w_state | w_data ------+---------+---------+----------------------------- 1 | pgsql | OPEN | Open item 1 of pgsql 2 | pgsql | OPEN | Open item 2 of pgsql 3 | pgsql | CLOSED | Closed item 3 of pgsql 4 | someone | OPEN | Open item of someone else 5 | someone | CLOSED | Closed item of someone else (5 rows) UPDATE wr_view SET w_data = 'Changed item 2 of pgsql' WHERE w_id = 2; UPDATE 1 SELECT * FROM wr_table ORDER BY w_id; w_id | w_user | w_state | w_data ------+---------+---------+----------------------------- 1 | pgsql | OPEN | Open item 1 of pgsql 2 | pgsql | OPEN | Changed item 2 of pgsql 3 | pgsql | CLOSED | Closed item 3 of pgsql 4 | someone | OPEN | Open item of someone else 5 | someone | CLOSED | Closed item of someone else (5 rows) UPDATE wr_view SET w_data = 'Changed item of someone else' WHERE w_id = 4; UPDATE 0 SELECT * FROM wr_table ORDER BY w_id; w_id | w_user | w_state | w_data ------+---------+---------+----------------------------- 1 | pgsql | OPEN | Open item 1 of pgsql 2 | pgsql | OPEN | Changed item 2 of pgsql 3 | pgsql | CLOSED | Closed item 3 of pgsql 4 | someone | OPEN | Open item of someone else 5 | someone | CLOSED | Closed item of someone else (5 rows) UPDATE wr_view SET w_data = 'Changed item 3 of pgsql' WHERE w_id = 3; UPDATE 0 SELECT * FROM wr_table ORDER BY w_id; w_id | w_user | w_state | w_data ------+---------+---------+----------------------------- 1 | pgsql | OPEN | Open item 1 of pgsql 2 | pgsql | OPEN | Changed item 2 of pgsql 3 | pgsql | CLOSED | Closed item 3 of pgsql 4 | someone | OPEN | Open item of someone else 5 | someone | CLOSED | Closed item of someone else (5 rows) DELETE FROM wr_view; DELETE 2 SELECT * FROM wr_table ORDER BY w_id; w_id | w_user | w_state | w_data ------+---------+---------+----------------------------- 3 | pgsql | CLOSED | Closed item 3 of pgsql 4 | someone | OPEN | Open item of someone else 5 | someone | CLOSED | Closed item of someone else (3 rows) INSERT INTO wr_view VALUES (99, 'someone', 'CLOSED', 'Meant for someone'); INSERT 19397 1 SELECT * FROM wr_table ORDER BY w_id; w_id | w_user | w_state | w_data ------+---------+---------+----------------------------- 3 | pgsql | CLOSED | Closed item 3 of pgsql 4 | someone | OPEN | Open item of someone else 5 | someone | CLOSED | Closed item of someone else 6 | pgsql | OPEN | Meant for someone (4 rows) Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com