Re: Postgres code for a query intermediate dataset

On 14/09/14 06:35, Atri Sharma wrote:
>
>
> On Sat, Sep 13, 2014 at 11:52 PM, David G Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> wrote:
>
> Atri Sharma wrote
> > On Sat, Sep 13, 2014 at 11:06 PM, Rohit Goyal &lt;
>
>
>
> Or rather even if you want to be able to reference the older
> versions of
> that record there is nothing in PostgreSQL to facilitate that. You
> have to
> manually create and manage the data so that you know during what
> time period
> a given record is valid.
>
> David J.
>
>
>
>
>
> Sometimes I do miss 'time travel' we used to have :)
>
> Regards,
>
> Atri
> --
> Regards,
> Atri
> /l'apprenant/
That is only because the Guild of Time Travellers was formed, and we are
very selective in whom we allow to join. It was a massive undertaking
to purge the knowledge of effective time travel from the general
populace (H. G. Wells had to be expelled with a partial brain wipe)! :-)

On a more serious note:
I did design and implement a system to allow what the original poster
was after, it involved 2 tables for each logical table, and used both an
EFFECTIVE_DATE & an AS_AT_DATE. This allowed insurance quotes to be
valid for a given of time, even if the insurance rates were set change
after the quote was given (but before the quote expired). This was
about 15 years ago. It was amusing that my wife joined that team 10
years after I left, and found 2 of my original colleagues still there!

Cheers,
Gavin