rule with multiple DELETE action part - Mailing list pgsql-general

From Papp Gyozo
Subject rule with multiple DELETE action part
Date
Msg-id Pine.GSO.4.21.0009201738220.1021-100000@ural2
Whole thread Raw
In response to rule with multiple DELETE action part  (Papp Gyozo <s7461pap@hszk.bme.hu>)
Responses Re: rule with multiple DELETE action part
List pgsql-general
I'm just wondering anybody read my question?
If not, here you are:

On Mon, 18 Sep 2000, Papp Gyozo wrote:

> Hello,
>
> now, my only question is how I can make a rule on a view with multiple delete action part which
> works well. I need a mechanism which deletes two rows from two tables
> which are in a one-to-one join.
>
> The example listed below is a quite different from the real tables I use.
> The table in the same role as "t_two" references the other table ("t_one").
> If rules with multiple action don't work correctly this would mean that
> my only last chance is using the REFERENCES constraint with ON DELETE CASCADE option?
> I hope not, because it is not for the same, I want to deny to delete rows
> if it has a pair in the other table.
>
> By the way, multiple inserts seem to work.
>
> try=# CREATE TABLE t_one (c_i integer PRIMARY KEY, c_t text);
> CREATE
> try=#  CREATE TABLE t_two (c_i integer REFERENCES t_one, c_d date);
> CREATE
> try=# CREATE VIEW v_one_two AS SELECT t_one.oid as c_oid, t_one.c_i, c_d, c_t
> try-# FROM t_one, t_two WHERE t_one.c_i = t_two.c_i;
> CREATE 81186 1
> try=# CREATE RULE r_one_two_del AS ON DELETE TO v_one_two
> try-# DO INSTEAD (DELETE FROM t_two WHERE c_i = old.c_i; DELETE FROM t_one WHERE c_i = old.c_i;);
> CREATE 81187 1
>
> try=# SELECT * FROM v_one_two;
>  c_oid | c_i |    c_d     |  c_t
> -------+-----+------------+-------
>  81157 |   1 | 2000-09-01 | hello
>  81158 |   2 | 1999-12-31 | world
>  81159 |   3 | 2000-08-12 | brave
> (3 rows)
>
> try=# DELETE FROM v_one_two WHERE c_i = 2;
> DELETE 0
> try=# SELECT * FROM t_one;
>  c_i |  c_t
> -----+-------
>    1 | hello
>    2 | world
>    3 | brave
>    4 | guy
> (4 rows)
>
> try=# SELECT * FROM t_two;
>  c_i |    c_d
> -----+------------
>    1 | 2000-09-01
>    3 | 2000-08-12
> (2 rows)


pgsql-general by date:

Previous
From: Buddy Lee Haystack
Date:
Subject: Re: pqReadData() -- backend closed the channel unexp
Next
From: Buddy Lee Haystack
Date:
Subject: Re: pqReadData() -- backend closed the channel unexp