| 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: | Whole Thread | Raw Message | 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 |