PGCon2009 - Final Release

PGCon 2009
The PostgreSQL Conference

David Fetter
Hitoshi Harada
Day Talks - first day - 2009-05-21
Room DMS 1150
Start time 16:00
Duration 01:00
ID 128
Event type Lecture
Track Version 8.4
Language used for presentation English

Introducing Windowing Functions

Internals and Externals

PostgreSQL 8.4 has much of the functionality of SQL:2008 windowing functions. In this presentation, you'll learn what windowing functions are for, how they work, both from the end-user and the RDBMS implementation perspective, and plans for future enhancements. You will also learn how to create user-defined windowing functions in C.

Before windowing functions, SQL had only rudimentary support for ordering and lists, which made it complicated to do operations referring to other rows in the result set. In OLAP contexts, this was a severe limitation, which windowing functions address. You will get a more thorough grounding in the theoretical background of the problems and of how windowing solves them.

Next, the Magic Inside: the internal design of PostgreSQL's window functions. To implement windowing functions, it was necessary to make changes through most of PostgreSQL, starting from the parser, on through the analyzer, optimizer, planner, and executor. You will get a broad overview of how these parts work in the windowing context.

Third, you'll learn how to use the built-in windowing functions, present and future, with their keywords and syntax in light of the SQL:2008 standard and the behavior of other RDBMSs, including things PostgreSQL does not yet support.

No presentation would be complete without a demonstration of PostgreSQL's extensibility, and you'll see how to implement your very own windowing function in C, touching on the APIs you'll use to do so.