Thread: Rule

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

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

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