pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Historic Query using a view/function ?
2+ messages / 1 participants
[nested] [flat]

* Historic Query using a view/function ?
@ 2003-12-23 16:46 Chris Gamache <cgg007@yahoo.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Chris Gamache @ 2003-12-23 16:46 UTC (permalink / raw)
  To: 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/



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Historic Query using a view/function ?
@ 2004-01-04 18:54 Chris Gamache <cgg007@yahoo.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Chris Gamache @ 2004-01-04 18:54 UTC (permalink / raw)
  To: 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 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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2004-01-04 18:54 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-23 16:46 Historic Query using a view/function ? Chris Gamache <cgg007@yahoo.com>
2004-01-04 18:54 Historic Query using a view/function ? Chris Gamache <cgg007@yahoo.com>

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