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.
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).
Simplify repetitively used select statements but that are not used outside of a specific body of work. True you could often
break these out as SQL functions, but clutters the space if not used anywhere else and violates our general rule of thumb of keep
code closest in contextual space to where it is most used so its purpose is obvious and it can be more easily extricated when it becomes obsolete.
Create recursive queries - such as those defining tree structures. Again in many cases you can perform these tricks in current PostgreSQL
versions already using recursive stored 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:
cooperate with GROUP BY aggregates
Ranking and ROW_NUMBER()
What will NOT make it:
sliding window (window framing)
lead(), lag(), etc. that reach for random rows
user defined window functions
Details of what is coming and what's dropped and the general saga can be found at the following links: