Lists: | pgsql-general |
---|
From: | sferriol <sylvain(dot)ferriol(at)imag(dot)fr> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | bug in delete rule ? |
Date: | 2004-03-25 15:29:48 |
Message-ID: | 4062FAEC.60005@imag.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
hello
i have a view which joins two tables
CREATE VIEW ALL_CENTERS AS
SELECT
t0.center_id,
t0.institution,
t0.category,
t0.street_number,
t0.street_name,
t0.postcode,
t0.district_name,
t0.city_name,
t0.province_name,
t0.country,
t1.update_date,
t1.number_of_beds,
t1.chief,
t1.phone_center,
t1.fax_center
FROM
center t0,
center_var t1
WHERE
t0.center_id = t1.center_id;
and i create a rule for deleting using the view:
CREATE RULE ALL_CENTERS_DEL AS ON DELETE TO all_centers
DO INSTEAD (
DELETE FROM center_var WHERE
center_id = OLD.center_id;
DELETE FROM center WHERE
center_id = OLD.center_id;
);
but i this rule delete only line in center_var
and do not remove the line in center table
why ?
sylvain
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | sferriol <sylvain(dot)ferriol(at)imag(dot)fr> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: bug in delete rule ? |
Date: | 2004-03-25 16:59:06 |
Message-ID: | 12432.1080233946@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
sferriol <sylvain(dot)ferriol(at)imag(dot)fr> writes:
> and i create a rule for deleting using the view:
> CREATE RULE ALL_CENTERS_DEL AS ON DELETE TO all_centers
> DO INSTEAD (
> DELETE FROM center_var WHERE
> center_id = OLD.center_id;
> DELETE FROM center WHERE
> center_id = OLD.center_id;
> );
> but i this rule delete only line in center_var
> and do not remove the line in center table
Once you delete the center_var row, there is no row in the view that
matches the id, so the comparison against OLD.center_id fails.
You could possibly make the view use an outer join, and be careful to
delete the dependent row first in the rule. Or maybe one of the tables
can be given a foreign-key dependency on the other, with ON DELETE CASCADE
behavior, and then the view rule need only delete explicitly from the
master table.
regards, tom lane
From: | günter strubinsky <strubinsky(at)acm(dot)org> |
---|---|
To: | "'sferriol'" <sylvain(dot)ferriol(at)imag(dot)fr>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: bug in delete rule ? |
Date: | 2004-03-25 20:07:35 |
Message-ID: | !~!UENERkVCMDkAAQACAAAAAAAAAAAAAAAAABgAAAAAAAAAN/BNEMuaw0GhLQnwyJOuDcKAAAAQAAAAXNC7R721nU6AQSY3ZBflEAEAAAAA@acm.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Why don't you apply a cascading delete on center?
See bottom of
http://www.postgresql.org/docs/7.4/interactive/ddl-constraints.html :
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
... shipping_address text,
...
);
CREATE TABLE order_items (
product_no integer REFERENCES products ON DELETE RESTRICT,
order_id integer REFERENCES orders ON DELETE CASCADE,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
Restricting and cascading deletes are the two most common options. RESTRICT
can also be written as NO ACTION and it's also the default if you do not
specify anything. There are two other options for what should happen...
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
with kind regards
günter strubinsky
<strubinsky(at)acm(dot)org>
Tel: 402.212.0196
-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of sferriol
Sent: Thursday, 25 March, 2004 09:30
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] bug in delete rule ?
hello
i have a view which joins two tables
CREATE VIEW ALL_CENTERS AS
SELECT
t0.center_id,
t0.institution,
t0.category,
t0.street_number,
t0.street_name,
t0.postcode,
t0.district_name,
t0.city_name,
t0.province_name,
t0.country,
t1.update_date,
t1.number_of_beds,
t1.chief,
t1.phone_center,
t1.fax_center
FROM
center t0,
center_var t1
WHERE
t0.center_id = t1.center_id;
and i create a rule for deleting using the view:
CREATE RULE ALL_CENTERS_DEL AS ON DELETE TO all_centers
DO INSTEAD (
DELETE FROM center_var WHERE
center_id = OLD.center_id;
DELETE FROM center WHERE
center_id = OLD.center_id;
);
but i this rule delete only line in center_var
and do not remove the line in center table
why ?
sylvain