- Mailing list pgsql-sql

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