Received: from smtp1.mindspring.com (smtp1.mindspring.com [207.69.200.31]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA02281 for ; Mon, 17 May 1999 13:04:06 -0400 (EDT) (envelope-from postgres@mindspring.com) Received: from dyounger.internal.mindspring.com (bronx-yz.fw.mindspring.net [207.69.192.4]) by smtp1.mindspring.com (8.8.5/8.8.5) with SMTP id NAA06572; Mon, 17 May 1999 13:04:02 -0400 (EDT) Message-Id: <4.1.19990517130233.009504e0@proxy> X-Sender: (Unverified) X-Mailer: QUALCOMM Windows Eudora Pro Version 4.1 Date: Mon, 17 May 1999 13:05:25 -0400 To: jwieck@debis.com (Jan Wieck) From: Doug Younger Subject: Re: [SQL] Create Rule problems Cc: pgsql-sql@postgreSQL.org In-Reply-To: References: <4.1.19990514135041.009646d0@proxy> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" 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