PostgreSQL stuff

-- Fibonacci sequence
-- Example to demo WITH RECURSIVE in PostgreSQL 8.4
with recursive f as (
    select 0 as a, 1 as b
    union all
    select b as a, a+b from f where a < 100
) select a from f;

Results in:

  a 
-----
   0
   1
   1
   2
   3
   5
   8
  13
  21
  34
  55
  89
 144
(13 rows)

Update: Updated note about DB2 version and added a note about Oracle.

Update: Firebird has also implemented CTE.

Going through the presentations from FOSDEM'09 I opened up David Fetter's Trees and More presentation and thought "what could be new about trees?". Man was I wrong!

The presentation shows classical solutions to the problem of trees and hierarchical data in SQL and then moves on to show how this can be solved by the implementation of WITH RECURSIVE in the upcoming PostgreSQL 8.4.

Taking a further look at WITH and WITH RECURSIVE I realised that this is a very powerful tool. Not entirely new, as Depez points out, but still a very usable thing. RECURSIVE is the key part that is new and that could not be solved in a very good way previously.

I have just started to think about what this will mean for our database and so far I've found a case where this is gonna make some difference. Of course I have to make some experiments to verify my theory. :-D

As far as I can tell PostgreSQL is the third RDBMS implementing the WITH and WITH RECURSIVE statement, known as Common Table Expressions or CTE, from the 2003 (WITH) and 2008 (WITH RECURSIVE) SQL-standard. Previously CTE has been implemented by Microsoft, introduced in MS SQL Server 2005, IBM, with DB2 V7, and by Firebird SQL Server, since v2.1.

I thought I could slip under the radar about Oracle but I repent with this note. ;-) Oracle have had WITH since v9.0 and has had CONNECT BY that serves the purpose of WITH RECURSIVE for ages.

I'm not gonna write any examples here since there are a lot of them out there.

Resources

• 7.8. WITH Queries (development docs)
• Depesz on CTE
• Using Common Table Expressions(From MS SQL Server 2008 docs)

(Although I might come back with a little more hands on example after playing around with it. For now I recommend Depez post as it contains a good discussion and example of the feature and what it'll mean for PostgreSQL users.)