Skip site navigation (1) Skip section navigation (2)

Re: Multiple Rules :: Postgres Is confused !!

From: "Najib Abi Fadel" <nabifadel(at)usj(dot)edu(dot)lb>
To: "Richard Huxton" <dev(at)archonet(dot)com>
Cc: "generalpost" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Multiple Rules :: Postgres Is confused !!
Date: 2004-09-30 07:29:48
Message-ID: 003201c4a6bf$48919000$f664a8c0@najib (view raw or flat)
Thread:
Lists: pgsql-general
> ALL rules get executed. Conditions get combined (actually, parse trees
> get merged).
>
> === BEGIN rule_test.sql ===
> CREATE TABLE foo (a int4 PRIMARY KEY, b text);
>
> COPY foo FROM stdin;
> 1 aaa
> 2 bbb
> 3 ccc
> 4 aaa
> 5 bbb
> 6 ccc
> \.
>
> CREATE VIEW foo_v AS SELECT * FROM foo WHERE b='bbb';
>
> CREATE RULE foo_v_upd1 AS ON UPDATE TO foo_v DO INSTEAD
> UPDATE foo SET b = 'z' || NEW.b WHERE a = OLD.a;
>
> SELECT * FROM foo ORDER BY a;
>
> UPDATE foo_v SET b='xxx';
>
> SELECT * FROM foo ORDER BY a;
> === END rule_test.sql ===
>
> This will update 2 rows (those with b='bbb') since we impose no WHERE in
> our update but the view does. The OLD/NEW refer to target rows
> before/after the change.
>
> Does that make things clearer?
> --

If i am getting this right the update command: "UPDATE foo_v SET b='xxx';"
will first get the "a" values (2 and 5)  from the view and then execute the
update on this rows.
?

So im my case, when i call the update : "update transactions_sco_v set
traiter='t' where id = 53597;"
IF
    select cursus_id, vers_id, traiter, code_type_academic, cod_etu from
transactions_sco_v where id = 53597;
Returns
 -[ RECORD 1 ]------+-------
cursus_id          | 62
vers_id            | 6
traiter            | f
code_type_academic | ECT
cod_etu            | 041400

this will execute the 3 update corresponding to the 3 rules i defined for
the values of cursus_id, vers_id, traiter, code_type_academic, cod_etu
returned above !!!
and i will have the following 3 updates executes !

UPDATE transactions_sco SET traiter = 't' WHERE cursus_id = 62 AND vers_id =
6 traiter = 'f' AND code_type_academic = 'ECT' and cod_etu = '041400';

UPDATE transactions_sco SET traiter = 't' WHERE cursus_id = 62 AND vers_id =
6 traiter = 'f' AND code_type_academic = 'ECT';


UPDATE transactions_sco SET traiter = 't' WHERE id = 53597;


I AM GETTING THIS RIGHT ??

THANX AGAIN FOR YOUR HELP.















In response to

Responses

pgsql-general by date

Next:From: Richard HuxtonDate: 2004-09-30 07:42:23
Subject: Re: Multiple Rules :: Postgres Is confused !!
Previous:From: Mike MorrisDate: 2004-09-30 06:34:16
Subject: psql slow disconnect via ssl

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group