Обсуждение: Creating multiple Rules for on update
I have a view joining two tables with a (1 to 1) relationship. I am trying to create two update rules (1 rule for each table in the view). To accomplish this I am trying (unsuccessfully) to use the where condition syntax of the update rules. Basically, I only want to update a table if the columns related to its tuple are altered. If possible, I don't want to update/touch a tuple from a table who's data remains unchanged. However, the where conditions that I am using to make this distinction are giving the following error: "ERROR: cannot update a view" "HINT: You need an unconditional ON UPDATE DO INSTEAD rule." Ofcourse, if my understanding of the use of the rule's WHERE condition is why off base, I would be enteresting in knowing the proper way it should be used. Here are my sample table, view, and rule definitions: <rule are at the bottom> CREATE SEQUENCE public.person_seq INCREMENT BY 1 START WITH 1 ; CREATE TABLE public.person ( id integer primary key not null default nextval('public.person_seq'), name varchar(30) unique not null ) ; ALTER SEQUENCE public.person_seq OWNED BY public.person.id; CREATE TABLE public.husband ( id integer primary key references person(id), tiesize integer not null ) ; CREATE OR REPLACE VIEW public.vhusband (id, name, tiesize) AS SELECT A.id, A.name, B.tiesize FROM public.person as A INNER JOIN public.husband as B ON A.id = B.ID ; CREATE OR REPLACE RULE vhusband_update_person AS ON UPDATE TO public.vhusband WHERE -- this is where I am trying to constrain which table -- gets updated. Since name in only in the person table. (NEW.name)<>(OLD.name) DO INSTEAD ( UPDATE public.person SET name = NEW.name WHERE id = OLD.id ) ; CREATE OR REPLACE RULE vhusband_update_husband AS ON UPDATE TO public.vhusband WHERE -- this is where I am trying to constrain which table -- gets updated. Since tiesize in only in the husband table. (NEW.tiesize)<>(OLD.tiesize) DO INSTEAD ( UPDATE public.husband SET tiesize = NEW.tiesize WHERE id = OLD.id ) ; Regards, Richard Broersma Jr.
Richard Broersma Jr <rabroersma@yahoo.com> writes: > I have a view joining two tables with a (1 to 1) relationship. I am trying > to create two update rules (1 rule for each table in the view). To > accomplish this I am trying (unsuccessfully) to use the where condition > syntax of the update rules. > > Basically, I only want to update a table if the columns related to its tuple > are altered. If possible, I don't want to update/touch a tuple from a table > who's data remains unchanged. However, the where conditions that I am using > to make this distinction are giving the following error: > > "ERROR: cannot update a view" > "HINT: You need an unconditional ON UPDATE DO INSTEAD rule." > > Ofcourse, if my understanding of the use of the rule's WHERE condition is > why off base, I would be enteresting in knowing the proper way it should be > used. When I read the docs about RULEs I remember seeing that an unqualified RULE was needed otherwise PG wouldn't know that the operation was complete and would fail. I haven't used them, but I believe that you have two options: - use a RULE with your filtering conditions AND add an unqualified RULE that is always run - use an unqualified RULE and call a function that will perform the action for you on the right tables I believe the second solution leads to cleaner code and is easier to update / debug. But I haven't tried it... -- Jorge Godoy <jgodoy@gmail.com>
> When I read the docs about RULEs I remember seeing that an unqualified RULE > was needed otherwise PG wouldn't know that the operation was complete and > would fail. I haven't used them, but I believe that you have two options: > - use a RULE with your filtering conditions AND add an unqualified RULE that > is always run > - use an unqualified RULE and call a function that will perform the action > for you on the right tables > I believe the second solution leads to cleaner code and is easier to update / > debug. But I haven't tried it... Thanks for the Reply, I will try creating functions called from the rule as you suggest. I would also be enterested in hearing of any other solutions that anyone might have. Also, I am still curious about what preresequites must be meet in order to use the WHERE predicate of a rule. Regards, Richard Broersma Jr.