Re-write rules on views
Hi all,
The docs seem slim in this department.. what is the proper syntax for an
update to a view with multiple tables?
Here's a view I'd like to update:
CREATE VIEW "users_perms_view" AS
SELECT
users.user_id,
users.username,
users.name_first,
users.name_last,
permissions.permission_name,
permissions.permission_description
FROM
users,
users_permissions,
permissions
WHERE
((users_permissions.user_id = users.user_id) AND
(users_permissions.permission_id = permissions.permission_id));
This rule works fine as far as it goes:
CREATE RULE "update_users_perms_view" AS
ON UPDATE TO users_perms_view DO INSTEAD
UPDATE
users SET
username = NEW.username,
password = NEW.password,
name_first = NEW.name_first,
name_last = NEW.name_last,
active = NEW.active,
WHERE
user_id = OLD.user_id;
How do I update the users_permissions and permissions table?
Thanx,
Rip <rip@onlineinfo.net> writes:
The docs seem slim in this department.. what is the proper
syntax for an update to a view with multiple tables?
I think you are looking for a multiple-action rule. Just put square
brackets, or parens if you prefer, around the list of action queries.
regards, tom lane
Tom Lane wrote:
Rip <rip@onlineinfo.net> writes:
The docs seem slim in this department.. what is the proper
syntax for an update to a view with multiple tables?I think you are looking for a multiple-action rule. Just put square
brackets, or parens if you prefer, around the list of action queries.
I recommend parens. Brackets are going away in 7.3. TODO has:
* Remove brackets as multi-statement rule grouping, must use parens
and I will attack that easy one soon. :-)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026Thanx for the help. Actually I discovered the real problem.... it's a php
bug. when php hits the ';' in the multiple action it assumes the query is
over and starts a new one. I'll post this bug report to php and back to the
list.
At 06:28 PM 2/27/02 -0500, you wrote:
Show quoted text
Tom Lane wrote:
Rip <rip@onlineinfo.net> writes:
The docs seem slim in this department.. what is the proper
syntax for an update to a view with multiple tables?I think you are looking for a multiple-action rule. Just put square
brackets, or parens if you prefer, around the list of action queries.I recommend parens. Brackets are going away in 7.3. TODO has:
* Remove brackets as multi-statement rule grouping, must use parens
and I will attack that easy one soon. :-)
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026