Thread: Rule

Rule

From
Andriy Pyrozhenko
Date:
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
doesnot 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.pyrozhenko@vanjaonline.com




Re: Rule

From
Tom Lane
Date:
Andriy Pyrozhenko <andriy.pyrozhenko@vanjaonline.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


Re: Rule

From
Andriy Pyrozhenko
Date:
Hello Tom,

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

TL> Andriy Pyrozhenko <andriy.pyrozhenko@vanjaonline.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.pyrozhenko@vanjaonline.com