Updating a table via a view

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

Responses

Browse pgsql-general by date

  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