From: Fred Wilson Horch <fhorch@ecoaccess.org>
To: pgsql-docs@postgresql.org
Subject: Can this be added to manual?
Date: Thu, 18 Feb 1999 20:56:06 -0500
Message-ID: <36CCC4B6.4A2FE629@ecoaccess.org> (raw)
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@cpsgroup.com>
> To: Remigiusz Sokolowski <rems@gdansk.sprint.pl>, Joerg
> Huettmann <joerg.huettmann@informatik.tu-chemnitz.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
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-docs@postgresql.org
Cc: fhorch@ecoaccess.org
Subject: Re: Can this be added to manual?
In-Reply-To: <36CCC4B6.4A2FE629@ecoaccess.org>
* 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