PostgreSQL has offered support for powerful analytics and windowing for a couple of years now already. Many people all around the globe use analytics to make their applications more powerful and even faster. However, there is a small little feature in the area of analytics which is not that widely known. The power to use composite data types along with analytics.

A simple example

AS always some sample data is needed. For analytics we at Cybertec rely on a simple data set containing some data from the oil industry. Here is how it can be loaded:

test=# CREATE TABLE t_oil (country text, year int, production int);

CREATE TABLE

test=# COPY t_oil FROM PROGRAM 'curl www.cybertec.at/secret/oil.txt';

COPY 92

All the data is drawn from the net (in case you got “curl” installed) and ends up in a table:

test=# SELECT *

                        FROM            t_oil

                        WHERE          country = 'USA'

                        ORDER BY year;

 country | year | production

---------+------+------------

 USA     | 1965 |       9014

 USA     | 1966 |       9579

 USA     | 1967 |      10219

 USA     | 1968 |      10600

 USA     | 1969 |      10828

*snip*

lag is a popular windowing function allowing us to move data around within the result set. lag(production, 1) means that the value in the production column should be pushed one row further down.

Here is how it works:

test=# SELECT *, lag(production, 1)

                        OVER (ORDER BY year)

            FROM            t_oil

            WHERE          country = 'USA'

            ORDER BY year;

 country | year | production |  lag 

---------+------+------------+-------

 USA     | 1965 |       9014 |     

 USA     | 1966 |       9579 |  9014

 USA     | 1967 |      10219 |  9579

 USA     | 1968 |      10600 | 10219

 USA     | 1969 |      10828 | 10600

Mind that we need an ORDER BY inside the OVER clause to make sure that we know into which direction to move the data. Order is essential to this kind of operation.

Moving beyond a simple field

So far so good. In the previous example one value was pushed one line down the resultset. However, it is also possible to move more complex structures around. It can be pretty useful to move an entire row:

test=# SELECT *, lag(t_oil, 1)

                        OVER (ORDER BY year)

            FROM            t_oil

            WHERE          country = 'USA'

            ORDER BY year;

 country | year | production |       lag       

---------+------+------------+------------------

 USA     | 1965 |       9014 |

 USA     | 1966 |       9579 | (USA,1965,9014)

 USA     | 1967 |      10219 | (USA,1966,9579)

 USA     | 1968 |      10600 | (USA,1967,10219)

 USA     | 1969 |      10828 | (USA,1968,10600)

In PostgreSQL every table definition can be seen as a composite data type. Therefore it can actually be used as a field. In this example all it does is saving us from a typing exercise. However, it can be useful if more complicated values have to be passed around (many of them).

The main question arising now is: How can a composite type be broken up again? The trick can be achieved like this:

test=# SELECT country, year, production, (lag).*  

            FROM ( SELECT *, lag(t_oil, 1) OVER (ORDER BY year)                                                                                  FROM    t_oil

                                   WHERE   country = 'USA'

                                   ORDER BY year ) AS x;

 country | year | production | country | year | production

---------+------+------------+---------+------+------------

 USA     | 1965 |       9014 |         |      |          

 USA     | 1966 |       9579 | USA     | 1965 |       9014

 USA     | 1967 |      10219 | USA     | 1966 |       9579

 USA     | 1968 |      10600 | USA     | 1967 |      10219

 USA     | 1969 |      10828 | USA     | 1968 |      10600

(column).* helps us to extract all fields out of the composite type once again.