sql functions     

PostgreSQL has this interesting placeholder called anyelement which it has had for a long time and its complement anyarray. They are used when you want to define a function that can handle many types arguments or can output many types of outputs. They are particularly useful for defining aggregates, which we demonstrated in Who's on First and Who's on Last and several other aggregate articles.

Anyelement / anyarray can be used just as conveniently in other functions. The main gotcha is that when you pass in the first anyelement/anyarray all subsequent anyelement / anyarray must match the same data type as the first anyelement / anyarray.

PostgreSQL 8.4 introduced the ability to create user-defined variadic functions. These are basically functions that take as input an undefined number of arguments where the argument that is an undefined number are all of the same type and are the last input arguments. Depesz went over it two years ago in Waiting for 8.4 variadic functions, so we are a bit late to the party. In a nutshell -- variadic functions are syntactic sugar for functions that would otherwise take arrays. In this article we'll provide some more demonstrations of them to supplement Depesz article.

I was reminded that I had never explored this feature, when recently documenting one of the new PostGIS 2.0 Raster functions - ST_Reclass which employs this feature. I think ST_Reclass is a superb function and one of my favorite raster functions thus far that I hope to put to good use soon. Our new PostGIS family member,Bborie Park, is running thru our PostGIS Raster milestones much faster than I had dreamed. He's already implemented a good chunk of stuff we discussed in Chapter 13 - PostGIS Raster and had stated you probably won't see in PostGIS 2.0. He's going a bit faster than I can catalog them, so the documentation is already embarrassingly behind the fantastic functionality that is already present in PostGIS 2.0.

In a prior article Use of Out and InOut Parameters we demonstrated how to use OUT parameters and INOUT parameters to return a set of records from a PostgreSQL function. There is another approach to doing this, and that is to use the ANSI Standard RETURNS TABLE construct. If you come from a SQL Server or IBM DB2 background, the RETURNS TABLE construct is probably most familiar, but still how you use it and what is legal in it is a little different than it is in SQL Server or IBM DB2. We'll save the contrast compare as a topic for another article.

In terms of performance between using OUT vs. RETURNS TABLE, we haven't noticed much of a difference. The main thing that is nice about RETURNS TABLE is just that it's syntactically more pleasing in the sense that its clearer the structure of what you are returning.

In these next examples, we'll demonstrate similar examples we showed in the aforementioned article except using the RETURNS TABLE. Be warned that the RETURNS TABLE construct is only available for PostgreSQL 8.4+, while the OUT approach has existed since PostgreSQL 8.1. With that said, if you need your code to work on 8.3 or lower, you can't use RETURNS TABLE. When in doubt about a feature and you are creating code that needs to support earlier versions of PostgreSQL (as we have to in the PostGIS development group), or you want to get stubborn users off old antiquated versions of PostgreSQL and need a bit of ammunition (as we have to (on PostGIS development including our own developers - and you know who you are :) ) ) check the PostgreSQL feature matrix. It will save you a lot of grief.

I am happy to report, that the final proof of the PostGIS in Action E-Book got released today and the printed version is scheduled for release Aprill 11th, 2011 and should be available on Amazon and other locations around then. The other e-Reader formats will come after that. You can buy from here or download the two free chapters, if you haven't already.

Each hard-copy purchase comes with a free E-Book version. There is a coupon in the back of the book when you get it to get the E-Book versions.

Yes, I know it's been a really really long time. On the bright side, we produced twice as much content as we had set out to do and that was with keeping things as concise as we could get away with, still managing to cover more than we set out to cover, and stripping out as many unnecessary words as we could muster. So 520 pages and almost 2 years later, this is where we are.

A good chunk of the additional bulk of the book was the appendices which are about 150 pages total and focus strictly on PostgreSQL and SQL. After many comments from early reviewers, we thought it unfair not to have a good chunk of PostgreSQL and just general relational database content to familiarize programmers and GIS folks with the RDBMS that PostGIS lives in. Most GIS folk unfortunately have the hardest time with getting up to speed with SQL and just standard RDBMS management.

Two free chapters and accompanying code for all chapters

The two free chapters we selectively picked because we thought they would be most beneficial to newcomers and people new to relational databases. So the free chapters are:

• Chapter 1: What is a spatial database? Which provides a fast paced history of PostGIS, PostgreSQL, Spatial Databases and moves into an even faster journey into converting flat file restaurant locations to spatial point geometries, loading in an ESRI shapefile of roads. Then shows you how to write standard spatial queries and render the results.
• Appendix C: SQL Primer -- goes through querying information_schemas, the common points of writing SELECT, INSERT, UPDATE, DELETE SQL statements and the finer points of using aggregate functions, Windowing constructs and common table expressions as well as a brief overview of how PostgreSQL stacks up with other relational databases (SQL Server, Oracle, IBM DB2, MySQL, Firebird) in SQL features.
• All the chapter code and accompanying data. It's a bit hefty at 57 MB.

So even if you don't buy our book, we hope you find the free chapters useful.

You can get a more detailed listing of all the chapters from the PostGIS in Action book site.

We'd like to thank all those who supported us through this long and unpredictable journey. Hopefully we'll have several more, though hopefully a bit less nerve-racking than this first one.

One of the coolest features of PostgreSQL is the ability to write functions using plain old SQL. This feature it has had for a long time. Even before PostgreSQL 8.2. No other database to our knowledge has this feature. By SQL we mean sans procedural mumbo jumbo like loops and what not. This is cool for two reasons:

• Plain old SQL is the simplest to write and most anyone can write one and is just what the doctor ordered in many cases. PostgreSQL even allows you to write aggregate functions with plain old SQL. Try to write an aggregate function in SQL Server you've got to pull out your Visual Studio this and that and do some compiling and loading and you better know C# or VB.NET. Try in MySQL and you better learn C. Do the same in PostgreSQL (you have a large choice of languages including SQL) and the code is simple to write. Nevermind with MySQL and SQL Server, you aren't even allowed to do those type of things on a shared server or a server where the IT department is paranoid. The closest with this much ease would be Oracle, which is unnecessarily verbose.
• Most importantly -- since it is just SQL, for simple user-defined functions, a PostgreSQL sql function can often be in-lined into the overall query plan since it only uses what is legal in plain old SQL.

This inlining feature is part of the secret sauce that makes PostGIS fast and easy to use. So instead of writing geom1 && geom2 AND Intersects(geom1,geom2) -- a user can write ST_Intersects(geom1,geom2) . The short-hand is even more striking when you think of the ST_DWithin function.

With an inlined function, the planner has visibility into the function and breaks apart the spatial index short-circuit test && from the more exhaustive absolute test Intersects(geom1,geom2) and has great flexibility in reordering the clauses in the plan.

Pierre Racine has been diligently working on PostGIS WKT Raster development. He was recently creating an sql function that uses output parameters. That was all nice and well, except he couldn't figure out how to output the output parameters as columns.

The function looked something like this:

CREATE FUNCTION somefunction(rast raster, OUT field1 integer, OUT field2 sometype, etc.) AS
	$$ blah blah blah $$
LANGUAGE 'sql';

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.

PostgreSQL 8.2 and above has this pretty neat feature of allowing you to define aggregate functions that take more than one column as an input. First we'll start off with a rather pointless but easy to relate to example and then we'll follow up with something a bit more interesting.

For more examples of creating aggregates in PostgreSQL, check out our other articles:

• Aug/Sept 2008: Build Median Aggregate Function in SQL
• Aug/Sept 2008: More Aggregate Fun: Who's on First and Who's on Last
• Feb/March 2009: PLPython Part 4: PLPython meets aggregates

Microsoft Access has these peculiar set of aggregates called First and Last. We try to avoid them because while the concept is useful, we find Microsoft Access's implementation of them a bit broken. MS Access power users we know moving over to something like MySQL, SQL Server, and PostgreSQL often ask - where's first and where's last? First we shall go over what exactly these aggregates do in MS Access and how they are different from MIN and MAX and what they should do in an ideal world. Then we shall create our ideal world in PostgreSQL.

One of the things we love most about PostgreSQL is the ease with which one can define new aggregate functions with even a language as succinct as SQL. Normally when we have needed a median function, we've just used the built-in median function in PL/R as we briefly demonstrated in Language Architecture in PostgreSQL.

If all you demand is a simple median aggregate function ever then installing the whole R statistical environment so you can use PL/R is overkill and much less portable.

In this article we will demonstrate how to create a Median function with nothing but the built-in PostgreSQL SQL language, array constructs, and functions.

One of the great selling points of PostgreSQL is its pluggable PL language architecture. MySQL is known for its pluggable storage and PostgreSQL is known for its pluggable PL language architecture. From Monty's notes on slide 12 looks like MySQL may be working on a pluggable PL language architecture of their own. The most common of these languages are the all-purpose languages SQL and C (these are built-in and not really PLs like the others, but we'll throw them in there), PLPgSQL which is also built-in but not always enabled, PL/Perl, PL/Python, and the domain specific languages PL/R, PL/SH and gaining popularity Skype released PL/Proxy. There are others in the family such as PL/Tcl, PL/PHP, PL/Ruby, PL/Scheme (a dialect of Lisp), PL/Java, PL/Lua and PL/LOLCode (for kicks and as a reference implementation. Think of LOLCode as PostgreSQL Pluggable PL equivalent of MySQL's BLACK HOLE storage engine.) .

The other interesting thing about the PostgreSQL PL language architecture is that it is a fairly thin wrapper around these languages. This means the kind of code you write in those languages is pretty much what you would write if you were doing general programming in those languages minus some spi calls. Since the handler is a just a thin wrapper around the environment, the language environment must be installed on the database server before you can use the PL language handler. This means you can have these functions utilized in your SQL statements and you can write in a language you feel comfortable with if you can get the darn PL compiled for your environment or someone has already kindly compiled it for your environment or that it is even compilable for your environment. The pluggable PL architecture means you can write a PL Handler for your favorite language or invent your own language that you can run in the database. In the end the barrier between code,data, and semantic constructs is more of a constraint imposed by compilers. If you have any doubts about the above statement, you need only look at some javascript injection attacks to bring the statement home. One of my fantasies is developing a language that morphs itself, that utilizes the database as its morphing engine and its OS and that breaks the illusion of data being data, code being code, and lacks rigid semantics. Of the languages we have worked with, SmallTalk comes closest to a language that satisfies these ideals and Lisp to a much lesser extent. Lisp lacked the semantic elegance of SmallTalk among other things.

Most people are used to having their procedural language push their data around. PL code living in PostgreSQL allows your data to push your procedural code around in a set-based way. This is a simple but pretty powerful feature since data is in general more fluid than code. For interpretated/just-in time compiled languages it can live in the database, for compiled it has to call compiled functions.

PostgreSQL 8.3 introduced a couple of new features that improves the processing of functions and makes plpgsql functions easier to write. These are as follows:

1. The new ROWS and COST declarations for a function. These can be used for any PostgreSQL function written in any language. These declarations allow the function designer to dictate to the planner how many records to expect and provide a hint as to how expensive a function call is. COST is measured in CPU cycles. A higher COST number means more costly. For example a high cost function called in an AND where condition will not be called if any of the less costly functions result in a false evaluation. The number of ROWs as well as COST will give the planner a better idea of which strategy to use.
2. RETURN QUERY functionality was introduced as well and only applies to plpgsql written functions. This is both an easier as well as a more efficient way of returning query results in plpgsql functions. Hubert Lubazeuwski provides an example of this in set returning functions in 8.3. We shall provide yet another example of this.
3. Server configuration parameters can now be set on a per-function basis. This is useful say in cases where you know a function will need a lot of work_mem, but you don't want to give all queries accessing the database that greater level of worker memory or you are doing something that index scan just works much better than sequential scan and you want to change the planners default behavior only for this function.
4. Scrollable Cursors in PL/pgSQL - this is documented in Declaring Cursor Variables
5. Plan Invalidation - Merlin Moncure covers this in PostgreSQL 8.3 Features: Plan Invalidation so we won't bother giving another example of this. Basic point to take away from this is that in procedures where you have stale plans floating dependent on tables being dropped by a function, those plans will be automagically deleted so you don't have ghost plans breaking your function.

Have you ever noticed that in PostgreSQL you can put set returning functions in the SELECT part of an sql statement if the function is written in language SQL or C. Try the same trick for PL written functions such as plpgsql, plperl, plr etc, and you get a slap on the wrist of the form ERROR: set-valued function called in context that cannot accept a set. For Plpgsql and other PL languages you must put the set returning function in the FROM clause.

Below is a simple example:

--Build test data
CREATE TABLE test
(
  test_id serial NOT NULL,
  test_date date,
  CONSTRAINT pk_test PRIMARY KEY (test_id)
)
WITH (OIDS=FALSE);

INSERT INTO test(test_date)
	SELECT current_date + n
		FROM generate_series(1,1000) n;
		
--test function with sql
CREATE OR REPLACE FUNCTION fnsqltestprevn(id integer, lastn integer)
  RETURNS SETOF test AS
$$
	SELECT *
	FROM test 
	WHERE test_id < $1 ORDER BY test_id
	LIMIT $2
$$
  LANGUAGE 'sql' VOLATILE;
  
--Test example 1 works fine
SELECT (fnsqltestprevn(6,5)).*;

--Test example 2 works fine
SELECT *
	FROM fnsqltestprevn(6,5);
	
--Same test function written as plpgsql
CREATE OR REPLACE FUNCTION fnplpgsqltestprevn(id integer, prevn integer)
  RETURNS SETOF test AS
$$
DECLARE
    rectest test;
BEGIN
	FOR rectest 
		IN(SELECT * 
			FROM test 
			WHERE test_id < id 
			ORDER BY test_id LIMIT prevn)  
								LOOP
		RETURN NEXT rectest;
	END LOOP;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;

--Test example 1 - gives error
-- ERROR:  set-valued function called in context that cannot accept a set
SELECT (fnplpgsqltestprevn(6,5)).*;

--Test example 2 works fine
SELECT *
	FROM fnplpgsqltestprevn(6,5);

--But what if we did this?
CREATE OR REPLACE FUNCTION fnsqltrojtestprevn(id integer, prevn integer)
	RETURNS SETOF test AS
$$
	SELECT * FROM fnplpgsqltestprevn($1, $2);
$$
LANGUAGE 'sql' VOLATILE;

--Test example 1 - works fine
SELECT (fnsqltrojtestprevn(6,5)).*;

--Test example 2 works fine
SELECT *
	FROM fnsqltrojtestprevn(6,5);

All interesting, but so what? you may ask. It is bad practice to put set returning functions in a SELECT clause. Such things are commonly mistakes and should be avoided.

Functional Row Expansion

It turns out that there are a whole class of problems in SQL where the simplest way to achieve the desired result is via a technique we shall call Functional Row Expansion. By that, we mean that for each record in a given set, we want to return another set of records that can not be expressed as a constant join expression. Basically the join expression is different for each record or the function we want to apply is too complicated to be expressed as a static join statement or join at all.

Taking the above example. Lets say for each record in test, you want to return the 4 records preceding including the current one. So basically you want to explode each row into 5 or fewer rows. Your general gut reaction would be do something as follows:

	
		SELECT test.test_id As ref_id, test.test_date as ref_date, targ.*
			FROM test , 
				(SELECT tinner.* 
					FROM test as tinner 
						WHERE tinner.test_id <= test.test_id 
					ORDER BY tinner.test_id LIMIT 5) As targ;
					
		SELECT test.test_id As ref_id, test.test_date as ref_date, targ.*
			FROM test,fnsqltrojtestprevn(test.test_id, 5) As targ;
	

	
SELECT test.test_id As ref_id, test.test_date as ref_date, 
	(fnsqltrojtestprevn(test.test_id, 5)).*
  FROM test
	

Keep in mind what makes the above tricky is that you want to return at most 4 of the preceding plus current. If you want to return all the preceding plus current, then you can do a trivial self join as follows:

	
SELECT test.test_id As ref_id, test.test_date as ref_date, targ.*
  FROM test INNER JOIN
	 test As targ ON targ.test_id <= test.test_id
  ORDER BY test.test_id, targ.test_id
	

So as you can see - its sometimes tricky to tell when you need to use this technique and when you don't.

For this trivial example, writing the function as an SQL only function works fine and is the best to use. SQL functions unfortunately lack the ability to define dynamic sql statements, among other deficiencies so resorting to using a pl language is often easier which means you lose this useful feature of sql functions. Stuffing a pl function in an SQL function just might do the trick. We haven't tried this on other pl languages except plpgsql, but we suspect it should work the same.

Perhaps one of the most unique and exciting things that makes PostgreSQL stand out from other database systems, are the numerous choices of languages one can use to create database functions, triggers and define new aggregate functions with. Not only can you use various languages to write your database stored functions with, but often times the code you write lives right in the database. You have no idea how cool this is until you see it in action.

The other interesting thing about the PostgreSQL language architecture is the relative ease with which new languages can be incorporated in the system.

Native Languages of PostgreSQL

There are 3 languages that come packaged with PostgreSQL (2 non-PL ones are installed automatically and not even listed as languages (C and SQL) in the languages section of a db). The defacto PL/PgSQL procedural language is available for install in all PostgreSQL distributions, but need not be installed in a db by default .

1. C Extern which allows for binding C libraries as functions. C Extern is similar to the way languages like MySQL bind C libraries for use in DB or the way SQL Server 2005+ binds .NET assemblies as functions in SQL Server.
2. SQL - this is a non-procedural language. It allows one to write parameterized db stored functions with plain SQL, but lacks procedural logic constructs such as IF, FOR, WHILE and so forth. It is basically a macro substitution language. Functions written in this way are basically in-lined in with the queries they are used (except in case of STABLE, IMMUTABLE defined in which case cached results are often used) in so they are more easily optimizable than functions written in other languages. NOTE: that MySQL 5+ also has a Procedural language called SQL, but the MySQL SQL language is a procedural language more in line with PostgreSQL pl/pgsql and closer in syntax to DB2's SQL PL. I'll also note that DB2 has a concept of INLINE SQL PL which is kind of like PostgreSQL sql language, although a bit more powerful.
3. PL/PgSQL - this is PostgreSQL defacto Procedural Language. It is not always installed by default in a database but the language handler is always available for installation. The equivalent but slightly different in syntax in other systems would be Transact SQL in SQL Server/Sybase, PL/SQL in Oracle, SQL in MySQL5+, and SQL PL in DB2.

The PL languages

Aside from PL/pgSQL there are numerous other procedural languages that one can use to create database stored functions and triggers. Some of these languages are fairly stable and even more are experimental. Some are only supported on Unix/Linux, but many are supported on Unix/Linux/MacOS/windows. In any case there are 3 key components needed before you can start using a new language:

1. The environment for the language - e.g. PHP, Perl, Python, Ruby, Java, R etc. interpreter and libraries installed on the PostgreSQL server box
2. The compiled call handler function - this is a C-compiled function that does the transfer between the PostgreSQL environment and the language environment.
3. The language registered in the database you wish to use it in.

Registering a language in a Database

For pl/pgsql items 1 and 2 are already done if you have a working PostgreSQL install. In order to accomplish item 3, you may need to do the following from psql or PgAdmin III query window.

 CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
  HANDLER plpgsql_call_handler
  VALIDATOR plpgsql_validator;

Alternatively you can run createlang plpgsql somedb from commandline. Note createlang is a command line program that is located in the bin folder of your PostgreSQL install.

To see a list of procedural languages that you already have call handlers registered for in PostgreSQL. These are the languages you can register in your specific database - do a

A Flavor of the Procedural Languages (PLs)

In this section, we'll show a brief sampling of what functions look like written in various PLs. These are not to suggest they are the only ones that exist. For these examples, I'm going to use the $ quoting syntax introduced in PostgreSQL 8.0 which allows for not having to escape out single quotes.

SQL - the not PL language

For basic CRUD stuff,selects and simple functions, nothing hits the spot like just plain old SQL. Since this is such a common choice and often the best choice - here are 3 examples.

CREATE OR REPLACE FUNCTION cp_simpleupdate(thekey integer, thevalue varchar(50))
  RETURNS void AS
	$BODY$
		UPDATE testtable SET test_stuff = $2 WHERE test_id = $1
	$BODY$
  LANGUAGE 'sql' VOLATILE;


--Example use
SELECT cp_simpleupdate(1, 'set to this'); 

--Here is a simple example to simulate the MySQL 5.0 function
CREATE OR REPLACE FUNCTION from_unixtime(unixts integer)
  RETURNS timestamp without time zone AS
$BODY$SELECT CAST('epoch' As timestamp) + ($1 * INTERVAL '1 second') $BODY$
  LANGUAGE 'sql' IMMUTABLE;
  
  --Example use
  SELECT from_unixtime(1134657687); 
  SELECT from_unixtime(tbl.fromsomefield) FROM tbl;

CREATE OR REPLACE FUNCTION cp_test(subject varchar)
  RETURNS SETOF testtable AS
  $BODY$
	SELECT * FROM testtable where test_stuff LIKE $1;
	$BODY$
  LANGUAGE 'sql' VOLATILE;
  --Example use
  SELECT * FROM cp_test('%stuff%');

  CREATE OR REPLACE FUNCTION cp_testusingoutparams(subject varchar, out test_id int, out test_stuff varchar)
  RETURNS SETOF record AS
$BODY$
	SELECT test_id, test_stuff FROM testtable where test_stuff LIKE $1;
$BODY$
  LANGUAGE 'sql' VOLATILE;

--Example use - Note the subtle difference - the second syntax with out parameters is newer
-- It allows you to get around the messy issue of when you are returning a record type
--That a record type has no specific type.
SELECT * FROM cp_usingoutparams('%stuff%');

For details on using out parameters, check out Robert Treat's out parameter sql & plpgsql examples

PLPGSQL - a real PL Language

For more complex logic and massaging of results before sending back. You need something more powerful than standard SQL. Below are some examples using PLPGSQL.

CREATE OR REPLACE FUNCTION cp_harderupdate(thekey integer, thevalue varchar)
RETURNS void AS
$BODY$
BEGIN
	IF EXISTS(SELECT test_id FROM testtable WHERE test_id = thekey) THEN 
		UPDATE testtable SET test_stuff = thevalue WHERE test_id = thekey;
	ELSE
		INSERT INTO testtable(test_id, test_stuff) VALUES(thekey, thevalue);
	END IF;
     RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

--Example use
SELECT cp_harderupdate(1, 'this is more stuff');

Using PL/Perl

CREATE OR REPLACE FUNCTION get_neworders() RETURNS SETOF orders AS $$
    my $rv = spi_exec_query('select * from orders where processed IS NULL;');
    my $status = $rv->{status};
    my $nrows = $rv->{processed};
    foreach my $rn (0 .. $nrows - 1) {
        my $row = $rv->{rows}[$rn];
        return_next($row);
    }
    return undef;
$$ LANGUAGE plperl;

Using PL/R a language and environment for statistics

One of my favorite PL languages to program is PL/R. The reason for this is that the R statistical environment is such a rich environment for doing statistical processing. It now is also supported on windows as well as Mac and Linux.

To learn more about R and installing PL/R. Check out our Boston GIS article PLR Part 1: Up and Running with PL/R (PLR) in PostgreSQL: An almost Idiot's Guide

Below is the classic median aggregate function in R. It uses the native median function in the R environment to create a PostgreSQL aggregate median function

CREATE or REPLACE FUNCTION r_median(_float8) 
	returns float as $BODY$ median(arg1) $BODY$ language 'plr';

CREATE AGGREGATE median (
  sfunc = plr_array_accum,
  basetype = float8,
  stype = _float8,
  finalfunc = r_median
);

--Example use
SELECT median(age) As themedian_age, period_year 
        FROM crimestats GROUP BY period_year ORDER BY period_year;

We will be covering PLR in greater detail in another article.