Skip site navigation (1) Skip section navigation (2)

Can this be added to manual?

From: Fred Wilson Horch <fhorch(at)ecoaccess(dot)org>
To: pgsql-docs(at)postgresql(dot)org
Subject: Can this be added to manual?
Date: 1999-02-19 01:56:06
Message-ID: 36CCC4B6.4A2FE629@ecoaccess.org (view raw or flat)
Thread:
Lists: pgsql-docs
I found this to be an extremely useful message to help understand how
aggregates and joins work.  Can this be included in the manual for 6.5?

In particular, the following commands would be nice to use as examples
in the chapters on joins and using aggregates:

> -- all that have ever existed (most recent)
> SELECT * from stuff* s1 WHERE d = (SELECT MAX(d) FROM stuff* s2 WHERE
> s1.id = s2.id);
> -- records @ or before '2/2/1999 13:50:00'
> SELECT * from stuff* s1 WHERE d = (SELECT MAX(d) FROM stuff* s2 WHERE
> s1.id = s2.id and s2.d <= '2/2/1999 13:50:00');

Just a suggestion,
--Fred

Full message:

> RE: [GENERAL] Missing features ?
> 
>      From: "Jackson, DeJuan" <djackson(at)cpsgroup(dot)com> 
>      To: Remigiusz Sokolowski <rems(at)gdansk(dot)sprint(dot)pl>, Joerg
>      Huettmann <joerg(dot)huettmann(at)informatik(dot)tu-chemnitz(dot)de> 
>      Subject: RE: [GENERAL] Missing features ? 
>      Date: Tue, 2 Feb 1999 13:55:39 -0600 
> 
> [Time travel] was depreciated because of it's high overhead verses it's
> utility.  In other words not enough people were using it to make it
> worth the cost in everyone's database.  There are many way that you can
> design the same type of system in your own database.  I suggest using
> inheritance and RULES. Example (assuming id is PRIMARY KEY):
> 
> CREATE TABLE stuff (
>  id INT, 
>  t TEXT, 
>  d DATETIME DEFAULT(DATETIME(NOW()))
> );
> CREATE UNIQUE INDEX stuff_pkey ON stuff(id);
> CREATE TABLE old_stuff () INHERITS (stuff);
> CREATE RULE del_stuff AS ON DELETE TO stuff DO 
>  INSERT INTO old_stuff SELECT old.*;
> CREATE RULE up_stuff AS ON UPDATE TO stuff DO 
>  INSERT INTO old_stuff SELECT old.*;
> INSERT INTO stuff (id, t) VALUES (1, 'Hello there.'); 
> INSERT INTO stuff (id, t) VALUES (2, 'Hi there.'); 
> INSERT INTO stuff (id, t) VALUES (3, 'Hello there again.'); 
> INSERT INTO stuff (id, t) VALUES (4, 'Hi there once more.'); 
> INSERT INTO stuff (id, t) VALUES (5, 'Hello there.');
> UPDATE stuff SET d=now(), t='Hi' WHERE t='Hello there.';
> DELETE FROM stuff WHERE id=2;
> DELETE FROM stuff WHERE id=5;
> -- current snapshot
> SELECT * from stuff;
> -- all that have ever existed (most recent)
> SELECT * from stuff* s1 WHERE d = (SELECT MAX(d) FROM stuff* s2 WHERE
> s1.id = s2.id);
> -- records @ or before '2/2/1999 13:50:00'
> SELECT * from stuff* s1 WHERE d = (SELECT MAX(d) FROM stuff* s2 WHERE
> s1.id = s2.id and s2.d <= '2/2/1999 13:50:00');
> 
> I think you get the idea.  If you want to store the date an item was
> delete/updated rather than when it was inserted the rules become:
> CREATE RULE del_stuff AS ON DELETE TO stuff DO 
>  INSERT INTO old_stuff SELECT old.id, old.t, datetime(now());
> CREATE RULE up_stuff AS ON UPDATE TO stuff DO 
>  INSERT INTO old_stuff SELECT old.id, old.t, datetime(now());
> 
> Hope this helps,
>         -DEJ

pgsql-docs by date

Next:From: ZinchikDate: 1999-02-19 12:57:57
Subject: unsubscribe
Previous:From: Hans de GoedeDate: 1999-02-18 14:34:33
Subject: A little nitpick correction

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group