Обсуждение: 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 19026

Thanx 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:
>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