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_tableSET w_data = new.w_data WHERE w_id = old.w_id AND w_state = 'OPEN'; CREATE CREATE RULE
wr_view_delAS 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',
'Openitem 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
INTOwr_table (w_user, w_state, w_data) VALUES ('someone', 'CLOSED', 'Closed item of someone else');
INSERT19396 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 *
FROMwr_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 *
FROMwr_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 |
Closeditem 3 of pgsql 4 | someone | OPEN | Open item of someone else 5 | someone | CLOSED |
Closeditem of someone else (3 rows)
INSERT INTO wr_view VALUES (99, 'someone', 'CLOSED', 'Meant for someone'); INSERT 19397 1 SELECT *
FROMwr_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