Re: History - Mailing list pgsql-general

On Saturday 01 February 2003 23:07, will trillich wrote:
> On Sat, Feb 01, 2003 at 06:47:05PM -0600, Alan Gutierrez wrote:
> > On Friday 31 January 2003 05:27, you wrote:
> > > Should I use inherrtance and have a different table for each
> > > table I want history for or one table for the lot?
> > >               Table "public.history"
> > >  Column  |            Type             | Modifiers
> > > ---------+-----------------------------+-----------
> > >  tab     | text                        |
> > >  field   | text                        |
> > >  action  | text                        |
> > >  before  | text                        |
> > >  after   | text                        |
> > >  occured | timestamp without time zone |
> > >  key     | text                        |
> > >  who     | text                        |
> >
> > One table for the lot. If you use inheritance, it is my
> > understanding that it will be the same thing, since all the
> > data will be stored in the base table. The only thing you'd
> > remove is the table name.
>
> aha. that clears up a question i had as well. lemme see if i
> understand--
>
>     create table delta (
>         id serial primary key,
>         created date default current_date
>     );
>
>     create table loc (
>         addr varchar(80),
>         st   varchar(4),
>         city varchar(30),
>         zip  varchar(12),
>         nation varchar(3) default 'USA'
>     ) inherits ( delta );
>
>     insert into loc(addr,city,zip) values
>         ('329 Main','Middlegulch','24680');
>
> then when i
>
>     select * from delta;
>
> i'll see the id (from loc) and the created date as well?  hmm!
> and this way it's ONE sequence for all related tables. i bet
> that's a nice un-cluttering side-effect. plus, the child tables
> would all take up that much LESS space, right? whoa, serious
> paradigm shift in the works... cool!

Shift back. I am not advocating the use of PostgreSQL inheritance. When
I want to model inheritance I do so explicitly.

CREATE TABLE Person
 (person_id INTEGER NOT NULL,
  first_name VARCHAR(32),
  last_name VARCHAR(32) NOT NULL,
  PRIMARY KEY (person_id));

CREATE TABLE Worker
 (worker_id int NOT NULL REFERENCES (Person),
  date_hired DATE NOT NULL,
  PRIMARY KEY (worker_id));

> but -- is there some way to tell which offspring table the delta
> record came from? now THAT would be useful.

There is no good way.

You moved the goal posts. I thought you wanted a history table to store
changes per row. How's that coming along?

--
Alan Gutierrez - ajglist@izzy.net
http://khtml-win32.sourceforge.net/ - KHTML on Windows