Rule

Lists: pgsql-sql
From: Andriy Pyrozhenko <andriy(dot)pyrozhenko(at)vanjaonline(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Rule
Date: 2001-11-20 13:38:45
Message-ID: 7524444939.20011120153845@vanjaonline.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

Hello pgsql-sql,

I have the rule for table
CREATE RULE del_jobs_del_activity AS ON DELETE TO jobs DO DELETE FROM activities WHERE job_id = OLD.job_id;

When i call:
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.

What must I change for work this query correctly?

Best regards,
Andriy mailto:andriy(dot)pyrozhenko(at)vanjaonline(dot)com

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andriy Pyrozhenko <andriy(dot)pyrozhenko(at)vanjaonline(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Rule
Date: 2001-11-20 15:24:29
Message-ID: 29543.1006269869@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

Andriy Pyrozhenko <andriy(dot)pyrozhenko(at)vanjaonline(dot)com> 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

From: Andriy Pyrozhenko <andriy(dot)pyrozhenko(at)vanjaonline(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Rule
Date: 2001-11-20 15:31:37
Message-ID: 6331217468.20011120173137@vanjaonline.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

Hello Tom,

Tuesday, November 20, 2001, 5:24:29 PM, you wrote:

TL> Andriy Pyrozhenko <andriy(dot)pyrozhenko(at)vanjaonline(dot)com> 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.

TL> I think you're going to need to use a trigger, instead. The
TL> rule-generated query runs first, and then by the time you get to the
TL> actual DELETE FROM jobs, the SELECT DISTINCT subquery doesn't find any
TL> rows ...

TL> regards, tom lane

How can I do it. I use PostgreSQL 7.1.3

Best regards,
Andriy mailto:andriy(dot)pyrozhenko(at)vanjaonline(dot)com