Historic Query using a view/function ? - Mailing list pgsql-sql

From Chris Gamache
Subject Historic Query using a view/function ?
Date
Msg-id 20031223164631.30803.qmail@web13803.mail.yahoo.com
Whole thread Raw
List pgsql-sql
...Postgresql 7.2...

I'm building the history of a table using rules. I've been trying to figure out
a way to select on a table as it would have appeared in a point in time. I
can't seem to wrap my brain around the problem, tho.

Given some tables

CREATE TABLE list ( num int4 NOT NULL,  name varchar(50),  type varchar(50),  modified timestamptz DEFAULT
('now'::text)::timestamp(6)with time zone,  CONSTRAINT list_pkey PRIMARY KEY (num)
 
) WITH OIDS;

CREATE TABLE list_log ( num int4 NOT NULL,  name varchar(50),  type varchar(50),  modified timestamptz DEFAULT
('now'::text)::timestamp(6)with time zone,  mod_type varchar(3),  log_date timestamptz DEFAULT
('now'::text)::timestamp(6)with time zone
 
) WITH OIDS;

And some rules...

CREATE RULE list_del AS ON DELETE TO list DO INSERT INTO list_log (num, name,
type, modified, mod_type) VALUES (old.num, old.name, old.type, old.modified,
'D'::"varchar");

CREATE RULE list_upd AS ON UPDATE TO list WHERE ((old.name <> new.name) OR
(old.type <> new.type)) DO INSERT INTO list_log (num, name, type, modified,
mod_type) VALUES (old.num, old.name, old.type, old.modified, 'U'::"varchar");

It'd be great to be able to do something like...

SELECT * FROM hist_list('10/10/2003'::date) WHERE name like '%Jones';

... I don't think Functions can return tables in 7.2 ... Can anyone think of a
way around this?

CG


__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/


pgsql-sql by date:

Previous
From: "Chris Travers"
Date:
Subject: Re: INHERITS and Foreign keys
Next
From: Michael Glaesemann
Date:
Subject: Distributed keys