Received: from neptun.sns-felb.debis.de (neptun.sns-felb.debis.de [53.122.101.2]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f277Vox00939 for ; Wed, 7 Mar 2001 02:32:08 -0500 (EST) (envelope-from jens.hartwig@t-systems.de) Received: by neptun.sns-felb.debis.de; id IAA21696; Wed, 7 Mar 2001 08:17:46 +0100 Received: from unknown(53.113.82.10) by neptun.sns-felb.debis.de via smap (V5.0) id xma021692; Wed, 7 Mar 01 08:17:43 +0100 Received: from mail1.b3.dsh.de (localhost [127.0.0.1]) by dshmail1.dsh.de (8.9.1/8.8.7) with ESMTP id IAA14177; Wed, 7 Mar 2001 08:18:15 +0100 (MET) Received: from 0000864A433A ([138.201.10.193]) by mail1.b3.dsh.de (8.10.0/8.10.0) with SMTP id f277IKX16717; Wed, 7 Mar 2001 08:18:20 +0100 From: "Jens Hartwig" To: "'Tom Lane'" Cc: Subject: AW: Problems with RULE Date: Wed, 7 Mar 2001 08:18:19 +0100 Message-ID: <000d01c0a6d6$c9976bd0$c10ac98a@0000864A433A> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2911.0) In-Reply-To: <7272.983909452@sss.pgh.pa.us> X-Mimeole: Produced By Microsoft MimeOLE V5.50.4133.2400 Importance: Normal X-Archive-Number: 200103/135 X-Sequence-Number: 1787 JH: > > [...] > > 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. > > [...] TL: > In my experience, anything you want to do that can be expressed as > an operation or condition on an individual target tuple of an > INSERT/UPDATE/DELETE is best done in a trigger, for reasons of both > performance and understandability. Rules are good for things that > involve conditions on multiple tuples. > [...] I am afraid, that I do not really understand this: if I insert one record in a view there also is only ONE tuple involved, isn't it? By the way, I admit that my example is not really useful :-) It should only demonstrate the use of rules for the book I am writing on. Further I do not understand the following: > You'd probably have better luck doing this with a trigger. With this > rule, the DELETE query expands into two operations, which can > be written > as: > > UPDATE t_xyz SET deleted = true > WHERE id IN > (SELECT old.id FROM t_xyz old WHERE old.id = 1 AND > old.deleted = false); > > DELETE FROM t_xyz WHERE id = 1 AND NOT (deleted = false); What would have happened, if I executed an unconditional DELETE? => DELETE FROM t_xyz; Which statement would have been generated by PostgreSQL in this case? Best Regards, Jens ----------------------------------------------------- T-Systems Projektleiter debis Systemhaus GEI GmbH Hausanschrift: Eichhornstraße 3, 10785 Berlin Postanschrift: 10785 Berlin Telefon: (004930) 25 54-32 82 Telefax: (004930) 25 54-31 87 Mobiltelefon: (0170) 167 26 48 E-Mail: jens.hartwig@t-systems.de Internet: http://www.t-systems.de