CTEs and Windowing Functions in 8.4

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.

A summary of where your favorite patches are at can be found at the September 2008 PostgreSQL 8.4 commit-fest summary page http://wiki.postgresql.org/wiki/CommitFest:2008-09.

What the hell is a RECURSIVE query and a common table expression (CTE) and why should I care?

CTE just specifies a way of defining a commonly used table expression (sort of like a view, but can also be used within a view (at least in SQL Server), we admittedly haven't experimented with 8.4 yet). A recursive CTE is a CTE that uses itself to define itself. There are two main reasons why people use CTEs (or rather why we use them).

Some good examples on how this would work in 8.4 can be lifted off the recent hackers thread on WITH RECURSIVE.

ALAS windowing functions

Well PostgreSQL 8.4 won't have complete support of Windowing Functions, but it looks like it will be on par or slightly better than what is available in SQL Server 2005, but not quite as good as Oracle and DB2. So to summarize from discussions read.

What will make it:

What will NOT make it:

Details of what is coming and what's dropped and the general saga can be found at the following links:

http://umitanuki.net/pgsql/wfv04/design.html and also the hackers Windowing thread http://archives.postgresql.org/pgsql-hackers/2008-09/msg00001.php.

YUM 8.4 snapshots

For those running RedHat EL, Fedora or CentOS and too lazy to compile yourself, check out Devrim's 8.4 RPM snapshots which will be released every week during commitfest via the new PostgreSQL Yum repository.