From: | Glen Eustace <geustace(at)godzone(dot)net(dot)nz> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Updating a table via a view |
Date: | 2003-02-10 08:07:03 |
Message-ID: | 1044864423.6857.315.camel@agree-6 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I am trying to maintain a history of rows. The base table has a start
and end date. I am updating the table using a view and a rule. The
rule, updates the end date on the current record and then inserts a new
row with the modified columns, or at least thats what I want to happen.
The update is occuring but the insert doesn't. I get no error but no
row. The rule looks like this;
CREATE RULE a_update
AS ON UPDATE TO a DO INSTEAD
(UPDATE a_hist
SET tend = now()
WHERE (a.x = old.x)
AND (a.tend = 'infinity'::timestamptz);
INSERT INTO a_hist (
x,
tstart,
tend,
y,
z
) VALUES (
new.x,
now(),
'infinity'::timestamptz,
new.y,
new.z
);
);
Any pointer as to what I am doing wrong ?
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen and Rosanne Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 5301
Ph/Fax: +64 6 357 8168, Mob: +64 21 424 015
From | Date | Subject | |
---|---|---|---|
Next Message | ahoward | 2003-02-10 08:19:26 | Re: Updating a table via a view |
Previous Message | Mark Kirkwood | 2003-02-10 07:47:51 | Re: PostgreSQL x Oracle |