From: Chris Gamache <cgg007@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Historic Query using a view/function ?
Date: Sun, 4 Jan 2004 10:54:44 -0800 (PST)
Message-ID: <20040104185444.78502.qmail@web13808.mail.yahoo.com> (raw)
...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 at 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!?
Find out what made the Top Yahoo! Searches of 2003
http://search.yahoo.com/top2003
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cgg007@yahoo.com
Subject: Re: Historic Query using a view/function ?
In-Reply-To: <20040104185444.78502.qmail@web13808.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox