As we mentioned in a previous article, RECURSIVE queries, often referred to in SQL ANSI specs and by DB2 and SQL Server as
Common Table Expressions (CTE) will make it into the 8.4 release and can already be found in the dev source. Technically CTE is a
superset and RECURSIVE queries are a subclass of CTE. Looks like basic windowing functionality will make it in 8.4 as well.
One thing I'm really looking forward to have in the upcoming PostgreSQL 8.4 is the introduction
of the WITH RECURSIVE feature that IBM DB2 and SQL Server 2005 already have. Oracle has it too but in a non-standard CONNECT BY so is much less portable.
This is a feature that is perhaps more important to
us for the kind of work we do than the much complained about lack of windowing functions.
I was recently taking a snoop at IBM DB2 newsletter. Why I read magazines and newsletters on databases I don't even use I guess is to see what I'm missing out on
and to sound remotely educated on the topic when I run into one of those people. I also have a general fascination with magazines.
In it their latest newsletter they had examples of doing Fibonacci and Graphs with Common Table Expressions (CTEs).
I'd be interested in seeing what solutions David and others come out with using new features of 8.4. We can see a before 8.4 and after 8.4 recipe.
As a slightly off-topic side note - of all the Database magazines I have read - Oracle Magazine is the absolute worst. SQL Server Magazine and IBM DB2 are pretty decent.
The real problem is that Oracle's magazine is not even a database magazine.
Its a mishmash of every Oracle offering known to man squashed into a compendium that can satisfy no one. You would think that Oracle as big as their database is
would have a magazine dedicated to just that.
Perhaps there is another magazine besides Oracle Magazine, but haven't found it so I would be interested to know if I missed something.