From: | Richard Broersma Jr <rabroersma(at)yahoo(dot)com> |
---|---|
To: | General PostgreSQL List <pgsql-general(at)postgresql(dot)org> |
Subject: | Creating multiple Rules for on update |
Date: | 2006-12-11 16:08:29 |
Message-ID: | 788653.65203.qm@web31807.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tony Caduto | 2006-12-11 16:12:57 | Re: TOAD-like query builder for PostgreSQL? |
Previous Message | Tom Lane | 2006-12-11 16:06:47 | Re: search_path when restoring to new db |