PostgreSQL adds windowing functions and common table expressions

As Hubert writes, PostgreSQL 8.4 has windowing functions.

Well done. I’ve been watching progress on this for a while. It greatly enhances the expressiveness of SQL.

What about common table expressions (WITH and WITH RECURSIVE)? Yes, PostgreSQL 8.4 will have common table expressions (WITH and WITH RECURSIVE) as well. That’s the “other” quantum leap in the expressiveness of SQL in my mind.

Meanwhile I’m helping a client rewrite subqueries and finding bugs in MySQL’s subquery handling that cause equivalent expressions to return different results. Alas, nested-loop left-deep query plans made it easier for MySQL to have different storage engines, but hard for it to go beyond the basics of expressiveness in SQL. (Warning: uninformed opinion alert. Maybe I’m blaming the wrong thing.)

This post should not be regarded as MySQL bashing or PostgreSQL glorifying. Use the tool that works for you, standard disclaimers, etc etc.

I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and lots of open-source software for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB. More about me.