Received: from www-uk-05.digiserve.com ([195.172.23.63]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f26GYZx29898 for ; Tue, 6 Mar 2001 11:34:35 -0500 (EST) (envelope-from dev@archonet.com) Received: from mainbox.archonet.com (mwynhau.demon.co.uk [193.237.186.96]) by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id QAA14875; Tue, 6 Mar 2001 16:35:41 GMT Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id 47CAF2A475; Tue, 6 Mar 2001 16:10:17 +0000 (GMT) From: dev@archonet.com Date: Tue, 06 Mar 2001 16:10:16 GMT Message-ID: <20010306.16101600@client.archonet.com> Subject: Re: Problems with RULE To: Jens Hartwig Cc: PSQL-Sql "(E-Mail)" Reply-To: dev@archonet.com In-Reply-To: <001f01c0a60c$b6976f50$c10ac98a@0000864A433A> References: <001f01c0a60c$b6976f50$c10ac98a@0000864A433A> X-Mailer: Mozilla/3.0 (compatible; StarOffice/5.2;Linux) X-Priority: 3 (Normal) MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by mail.postgresql.org id f26GYZx29899 X-Archive-Number: 200103/104 X-Sequence-Number: 1755 On 3/6/01, 7:11:48 AM, Jens Hartwig wrote regarding [SQL] Problems with RULE: > Hello all, > I tried to implement the following rule: if someone wants to delete a record > from a table t_xyz (id integer, deleted boolean) the record should get a > delete-flag (deleted = true). When this "pre-deleted" record is deleted for > the next time it should be physically deleted from the database. > I implemented the following rule: > CREATE RULE r_del_xyz > AS ON DELETE TO t_xyz WHERE (old.deleted = false) > DO INSTEAD > UPDATE t_xyz > SET deleted = true > WHERE id = old.id; > Now I tested the new rule: > INSERT INTO t_xyz VALUES (1, false); > INSERT INTO t_xyz VALUES (2, false); > DELETE FROM t_xyz WHERE id = 1; > SELECT * FROM t_xyz ; > id | deleted > ----+--------- > 2 | f > What has happened? The rule seems to be ignored and the record was deleted! No help I'm afraid, but I encountered something similar the other day on 7.1b3 CREATE RULE ... AS ON UPDATE TO ... WHERE ... DO INSTEAD UPDATE ... Didn't run, but removing the WHERE did. I had thought someone had raised this recently, but looking through the list I can't see it now, so maybe it's a real bug. Is there any mention of this is the CHANGES file in beta4? I'll try and check this end whether it applies to all rule-types if you add a WHERE. - Richard Huxton