2

Possible Duplicate:
Is it possible to make a recursive SQL query?

Imagine an application where one would want to keep a history of the changes made to a 'record' in a database.

This can be realized by introducing a column 'revised_id' which points to the newer version of the record.

id  value                               revised_id
--------------------------------------------------
1   Initial value...                    3
2   Value of a different record         Null
3   Value which has been altered        4
4   Value which has been altered again  Null

The table illustrates two 'actual records', one of which has been altered two times

Getting all the latest versions simply means adding revised_id = null to the WHERE clause. To get the previous version simply: SELECT * FROM table WHERE revised_id = $current_id

My questing is: would it be possible to select all te previous versions of a given record?(The problem being that simple comparisons do not suffice because the records to be selected are 'chained' together.)

Community
  • 1
  • 1
Midge
  • 51
  • 3
  • 3
    What you're looking for is a [recursive query](http://stackoverflow.com/questions/5980611/postgres-recursive-query-on-the-same-table) – Lukas Eder Oct 12 '12 at 15:40
  • Interesting how not knowing what it's called can keep you from finding the answer... Anyway, **thanks guys!** – Midge Oct 12 '12 at 15:50
  • 3
    This table looks like it lacks a per-record fixed identifier (independent of the revision). Without that how to identify a "given record" in the first place? And once you have that you probably no longer need the recursive query. – Daniel Vérité Oct 12 '12 at 16:04
  • @DanielVérité: That hadn't even occurred to me... – Lukas Eder Oct 12 '12 at 16:16
  • 2
    You may have a good reason for doing it this way but I have always found it more useful to have one table containing only the current value and a seperate table that contains all changes with additional timestamps and information who made the change where from. – Eelke Oct 12 '12 at 16:19
  • It's often *much* easier to work with either (a) an separate audit table that logs changes; or (b) "created time" and "deleted time" fields that let you do temporal queries. The approach you suggest will work but is likely to have truly awful performance. – Craig Ringer Oct 13 '12 at 00:49

0 Answers0