PLPython Part 4: PLPython meets aggregates

In our August 2008/ September 2008 issue we demonstrated the power of PostgreSQL to create median and MS Access-like first and last aggregate functions in SQL language. In this article we shall demonstrate how to create aggregates with Python. We shall call this function agg_plot. What it will do is plot each grouping of data and return a plot for each grouping. The steps we covered in those articles can be applied here.

For this part, we shall use the same library we discussed in PLPython Part 3: Using custom classes, pulling data from PostgreSQL.

Creating Aggregate functions with PL/Python

Our basic approach is as follows:

• Since PL/Python does not support arrays as input, our aggregator will form a pipe delimeted list inputs as a string using plot_databuild function (which although we wrote it in python need not be and probably more efficient to write in plpgsql
• As the final step of the aggregation, this is fed to our plot_numbers function -- which converts this to an array of strings and then converts to an array of numbers to be fed to the plotter

Code is shown below and final output

##create agg
CREATE OR REPLACE FUNCTION plot_numbers(param_dataset text)
  RETURNS text AS
 $$
 import aplotter
 import sys
 class WritableObject:
	def __init__(self):
		self.content = ''
	def write(self, string):
		self.content = self.content + string
 saveout = sys.stdout
 outbuffer = WritableObject()
 sys.stdout = outbuffer
 #convert our string of | delimited numbers to an array
 adataset = param_dataset.split('|')
 #pipe in converting each string to number
 aplotter.plot([float(y) for y in adataset])
 sys.stdout = saveout
 return outbuffer.content
$$ LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION plot_databuild(param_dataset text, param_item numeric)
  RETURNS text AS
 $$
  if param_dataset is None :
	return str(param_item)
  return param_dataset + '|' + str(param_item)
$$ LANGUAGE plpythonu;

CREATE AGGREGATE agg_plot(numeric) (
  SFUNC=plot_databuild,
  STYPE=text,
  FINALFUNC=plot_numbers
);

##Plotting fake data but lets pretend it is real
CREATE TABLE mydata2(pt_id serial, pt_observation numeric(10,4), graph_id integer);
INSERT INTO mydata(pt_observation, graph_id)
	SELECT n*pi()*random()*10, generate_series(1,3)
		FROM generate_series(1,15) As n;

#and now to test
SELECT graph_id, agg_plot(pt_observation)
 FROM mydata2
 GROUP BY graph_id
 ORDER BY graph_id;

Which gives us this:

1; |
   +378.733                                                      \ 
   |                                                            /\          *
   |                                                           /  \        /
   |                                                          /   \        /
   |                                                         /     \      /
   |                                                        /      \      /
   |                                         \              /       \    /
   |                                        / \\           /        \    /
   |                                       /    \\-      //          \  /
   |                         \             /       ----//            \  /
   |                    -----\            /                           \/
   |                   /      \          /                            \/
   |                 //        \         /                             /
   |          -----//           \       /
   |         /                   \      /
   |       //                     ------
   |      /
---+0.86//------------------------------------------------------------------+---
   +0                                                                    +14

2; |
   +333.964                                                            -----*
   |                                                                  /
   |                                                                //
   |                                                               /
   |                                              \              //
   |                                    \        /\             |
   |                                   / \\    // \             |
   |                                  /    \\//    \           |
   |                                 /             \           |
   |                                /               \          |
   |                               /                \         |
   |               \              /                  \        |
   |              / \             /                  \       |
   |             /   \\      -----                    \      |
   |    -       /      \-----                         \      |
   |  -- -----//                                       -    |
   ---                                                  --  |
---+0.26--------------------------------------------------==|---------------+---
   +0                                                                    +14

3; +0                                                                    +14
   +344.31                                             -
   |                                                  | ----|
   |                                                  |     |
   |                                                 |      |
   |                                                 |      |
   |                                                 |       |
   |                                                |        |
   |                                                |        |
   |                                                |         |             *
   |                                               |          |            /
   |                                               |          |          //
   |                                    \          |           |       //
   |                    -              / \        |            |      /
   |                   / --           /   \       |            |     /
   |          \       /    --\       /     \      |             |   /
   |    ------ \\    /        \\    /       \   //              |  /
   |  --         \\//           \\//         ///                | /
   +15.4662                                                      /
---+------------------------------------------------------------------------+---