Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de [53.2.131.8]) by hub.org (8.9.3/8.9.3) with SMTP id FAA50812 for ; Mon, 17 May 1999 05:57:34 -0400 (EDT) (envelope-from wieck@sapserv.debis.de) Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-sql@postgreSQL.org id m10jK8G-000EBZC; Mon, 17 May 99 11:56 MET DST Message-Id: From: jwieck@debis.com (Jan Wieck) Subject: Re: [SQL] Create Rule problems To: postgres@mindspring.com (Doug Younger) Date: Mon, 17 May 1999 11:56:24 +0200 (MET DST) Cc: pgsql-sql@postgreSQL.org Reply-To: jwieck@debis.com (Jan Wieck) In-Reply-To: <4.1.19990514135041.009646d0@proxy> from "Doug Younger" at May 14, 99 01:50:51 pm X-Mailer: ELM [version 2.4 PL25] Content-Type: text > > 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) #