PostgreSQL has supported what are called Out (output) parameters since version 8.1.
We were surprised it has been that long since we always thought of it as a feature from 8.2+
until it recently came up for discussion on PostGIS newsgroup and we decided to investigate how
long it has been supported.
What are OUT parameters? These are parameters you define as part of the function argument
list that get returned back as part of the result. When you create functions, the arguments
are defaulted to IN parameters when not explicitly specified (which means they are passed in and not returned) which
is why you sometimes see PgAdmin do something like IN somevariable variabletype when you use
the function wizard.
You can have INOUT parameters as well which are function inputs that both get passed in, can be modified
by the function and also get returned.
As a side note - In 8.4, PostgreSQL was enhanced to allow dynamic sql RETURN QUERY using RETURN QUERY EXECUTE syntax for plpgsql queries and also
allow set returning functions being called in the SELECT part for any pl language. In prior versions,
this was only a feature of PL functions written in SQL.
8.3 introduced RETURN query which required a static sql statement, but did make things a bit easier.
One of the common use cases for using OUT parameters is to be able to return multiple outputs from a function without having
to declare a PostgreSQL type as output of the function. In this article we shall cover all variants of this. We'll just focus on sql and plpgsql for this
discussion, since we are not sure to what extent other pl languages (if at all) support IN OUT.
One of the very handy features introduced in PostgreSQL 8.4 is the new aggregate function called array_agg which is a companion function to the unnest function we discussed earlier. This
takes a set of elements similar to what COUNT, SUM etc do and builds an array out of them. This approach is faster than the old used array_append , array_accum since it does not rebuild the array on each iteration.
Sadly it does not appear to be completely swappable with array_append as there does not seem to be a mechanism to use it to build your own custom aggregate functions that need to maintain the set of objects flowing thru the aggregate without venturing into C land. This we tried to do
in our median example but were unsuccessful.
We had originally thought array_agg was a PostgreSQL only creation, but it turns out that array_agg is a function defined in the ANSI SQL:2008 specs and for one appears to exist in IBM DB2 as well. I don't think
Oracle or any other database supports it as of yet.
As we had demonstrated in the other article, we shall demonstrate the olden days and what array_agg brings to the table to make your life easier.
In this issue we shall be celebrating the arrival of PostgreSQL 8.4 by showcasing the new treats
that PostgreSQL 8.4 has to offer. Although 8.4 has some nice big ticket items like Windowing Functions which we
briefly covered numerous times and Common Table Expressions, it also has some small ticket items. These small ticket items
while small, are perhaps more useful than even the big ticket ones because they are more commonly used constructs.
In this article we shall introduce the new unnest() function which makes converting an array to a table like structure
not only easier, but much more efficient. We will also be covering the new enhancements to our favorite function the
PostgreSQL 8.4 has ANSI SQL:2003 window functions support. These are often classified under the umbrella terms of basic Analytical or Online Application Processing (OLAP) functions.
They are used most commonly for producing cumulative sums, moving averages and generally rolling calculations that need to look at a subset of the overall dataset (a window frame of data) often relative to a particular row.
For users who use SQL window constructs extensively, this may have been one reason in the past to not to give PostgreSQL a second look. While you may not
consider PostgreSQL as a replacement for existing projects because of the cost of migration, recoding and testing, this added new feature is definitely a selling point
for new project consideration.
If you rely heavily on windowing functions, the things you probably want to know most about the new PostgreSQL 8.4 offering are:
What SQL window functionality is supported?
How does PostgreSQL 8.4 offering compare to that of the database you are currently using?
Is the subset of functionality you use supported?
To make this an easier exercise we have curled thru the documents of the other database vendors to distill what the SQL Windowing functionality they provide in their core product.
If you find any mistakes or ambiguities in the below please don't hesitate to let us know and we will gladly amend.
For those who are not sure what this is and what all the big fuss is about, please read our rich commentary on the topic of window functions.