From: Jan Wieck <janwieck@Yahoo.com>
To: PostgreSQL SQL <pgsql-sql@PostgreSQL.org>
Cc: [SQL] <@jupiter.greatbridge.com>
Cc: Rule@jupiter.greatbridge.com
Cc: not@jupiter.greatbridge.com
Cc: invoked@jupiter.greatbridge.com
Cc: in@jupiter.greatbridge.com
Cc: 7.1@jupiter.greatbridge.com
Date: Fri, 26 Jan 2001 14:05:30 -0500 (EST)
Message-ID: <200101261905.OAA03570@jupiter.greatbridge.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: janwieck@Yahoo.com, @jupiter.greatbridge.com, Rule@jupiter.greatbridge.com, not@jupiter.greatbridge.com, invoked@jupiter.greatbridge.com, in@jupiter.greatbridge.com, 7.1@jupiter.greatbridge.com
Subject: Re:
In-Reply-To: <200101261905.OAA03570@jupiter.greatbridge.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox