Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAKFOYm29070 for ; Tue, 20 Nov 2001 10:24:34 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fAKFOTc29546; Tue, 20 Nov 2001 10:24:30 -0500 (EST) To: Andriy Pyrozhenko cc: pgsql-sql@postgresql.org Subject: Re: Rule In-reply-to: <7524444939.20011120153845@vanjaonline.com> References: <7524444939.20011120153845@vanjaonline.com> Comments: In-reply-to Andriy Pyrozhenko message dated "Tue, 20 Nov 2001 15:38:45 +0200" Date: Tue, 20 Nov 2001 10:24:29 -0500 Message-ID: <29543.1006269869@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200111/293 X-Sequence-Number: 5675 Andriy Pyrozhenko writes: > CREATE RULE del_jobs_del_activity AS ON DELETE TO jobs DO DELETE FROM activities WHERE job_id = OLD.job_id; > DELETE FROM jobs WHERE job_id IN (SELECT DISTINCT job_id FROM > activities WHERE load_no = 123) > This query does not delete the record from jobs but delete the > record by rule. I think you're going to need to use a trigger, instead. The rule-generated query runs first, and then by the time you get to the actual DELETE FROM jobs, the SELECT DISTINCT subquery doesn't find any rows ... regards, tom lane