Thread: 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.