Thread: Create Rule problems

Hello, I'm trying to set up some rules and was following the examples in the
Programmer's guide form the docs, and it gives an example similar to this:

CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data WHERE NEW.sl_avail != OLD.sl_avail
DO INSERT INTO shoelace_log VALUES (                NEW.sl_name,                NEW.sl_avail,
getpgusername().               'now'::text              );
 

What I want to do is very similar:

CREATE RULE update_login AS ON UPDATE TO user_info WHERE NEW.login != OLD.login
DO UPDATE user_dept SET login = NEW.login WHERE login = OLD.login;

I get an error:
"ERROR: Table old does not exist."
What's going on?

Also, the docs say you can do multiple actions in a rule... what is the syntax?
I tried putting the actions in parens separated by semicolons or commas wit
no luck...
There wasn't an example in the docs that did more than on action.   

Thanks, Doug. 

>
> Hello,
>   I'm trying to set up some rules and was following the examples in the
> Programmer's guide form the docs, and it gives an example similar to this:
>
> CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
>   WHERE NEW.sl_avail != OLD.sl_avail
> DO INSERT INTO shoelace_log VALUES (
>                  NEW.sl_name,
>                  NEW.sl_avail,
>                  getpgusername().
>                  'now'::text
>                );
>
> What I want to do is very similar:
>
> CREATE RULE update_login AS ON UPDATE TO user_info
>   WHERE NEW.login != OLD.login
> DO UPDATE user_dept SET login = NEW.login
>   WHERE login = OLD.login;
>
> I get an error:
> "ERROR: Table old does not exist."
> What's going on?

    Which  PostgreSQL  release?  I've  just  checked  it with the
    current v6.5 tree and I'm sure it worked too with the  v6.4.2
    released.

>
> Also, the docs say you can do multiple actions in a rule... what is the syntax?
> I tried putting the actions in parens separated by semicolons or commas wit
> no luck...
> There wasn't an example in the docs that did more than on action.

    Current v6.5 tree can handle it. You have to put parens arond
    the actions and separate them with semicolon:

        CREATE TABLE t1 (a int4, b text);
        CREATE TABLE t2 (a int4, b text);
        CREATE TABLE t3 (a int4, b text);

        CREATE RULE r1 AS ON UPDATE TO t1 WHERE NEW.b != OLD.b DO (
          UPDATE t2 SET b = NEW.b WHERE t2.a = OLD.a;
          UPDATE t3 SET b = NEW.b WHERE t3.a = OLD.a;
        );

    This works for me.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

Thanks for the reply... I had a feeling it would be a version problem. I upgraded to 6.4.2 and both things work.

At 5/17/99 05:56 AM, Jan Wieck wrote:
>>
>> Hello,
>>   I'm trying to set up some rules and was following the examples in the
>> Programmer's guide form the docs, and it gives an example similar to this:
>>
>> CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
>>   WHERE NEW.sl_avail != OLD.sl_avail
>> DO INSERT INTO shoelace_log VALUES (
>>                  NEW.sl_name,
>>                  NEW.sl_avail,
>>                  getpgusername().
>>                  'now'::text
>>                );
>>
>> What I want to do is very similar:
>>
>> CREATE RULE update_login AS ON UPDATE TO user_info
>>   WHERE NEW.login != OLD.login
>> DO UPDATE user_dept SET login = NEW.login
>>   WHERE login = OLD.login;
>>
>> I get an error:
>> "ERROR: Table old does not exist."
>> What's going on?
>
>    Which  PostgreSQL  release?  I've  just  checked  it with the
>    current v6.5 tree and I'm sure it worked too with the  v6.4.2
>    released.
>
>>
>> Also, the docs say you can do multiple actions in a rule... what is the 
>syntax?
>> I tried putting the actions in parens separated by semicolons or commas wit
>> no luck...
>> There wasn't an example in the docs that did more than on action.
>
>    Current v6.5 tree can handle it. You have to put parens arond
>    the actions and separate them with semicolon:
>
>        CREATE TABLE t1 (a int4, b text);
>        CREATE TABLE t2 (a int4, b text);
>        CREATE TABLE t3 (a int4, b text);
>
>        CREATE RULE r1 AS ON UPDATE TO t1 WHERE NEW.b != OLD.b DO (
>          UPDATE t2 SET b = NEW.b WHERE t2.a = OLD.a;
>          UPDATE t3 SET b = NEW.b WHERE t3.a = OLD.a;
>        );
>
>    This works for me.
>
>
>Jan