Table Of Contents

We decided to continue with a Part 2 to this PL/Python series given the surprising popularity of the first. In our first article appearing in our January 2009 issue Quick Intro to PLPython we quickly went over installing PL/Python, doing a function that calls out to the operating system, and doing a quick encoder decoder. In this article we will provide examples of basic Python controls structures and how to return sets in PL/Python.

Keep in mind that while you may love Python, some things are just better done in SQL language or PL/PGSQL language so just because you can and you feel more of a comfort level with Python, doesn't mean you should forget about the other languages. SQL and PL/PGSQL are still more intimately connected to the PostgreSQL architecture and don't have an additional environment dependency to rely on. That said -- Python has a rich environment and is a much richer language in many respects so is ideal for certain kinds of problems.

PL/Python Control Flow

CREATE OR REPLACE FUNCTION readfile (filepath text)
  RETURNS text
AS $$
 import os
 if not os.path.exists(filepath):
  return "file not found"
 return open(filepath).read()
$$ LANGUAGE plpythonu;

--Example use
SELECT readfile('/test.htm');

This is the same exercise as above but using exceptions

CREATE OR REPLACE FUNCTION readfile_te (filepath text)
  RETURNS text
AS $$
 import os
 try:
  return open(filepath).read()
 except (IOError, OSError):
   return "file not found"
$$ LANGUAGE plpythonu;

## Example Use
SELECT readfile_te('/test.htm');

Sadly even in the 8.4 incarnation, it appears you can not pass in arrays as arguments in Pl/Python. If you do they get silently cast to text. So to compensate you can pass in text with some sort of delimeter and convert it to a Python array as needed.

However, you can return sets as an output of Pl/Python. Below is a very pointless trivial example that takes a | delimited string and converts it to a set of integers representing the length of each string.

CREATE OR REPLACE FUNCTION dosomething_withtext (strstuff text)
  RETURNS SETOF integer
AS $$
 astrstuff= strstuff.split('|');
 alens = [];
 for x in astrstuff:
  alens.append(len(x));
 return alens;
$$ LANGUAGE plpythonu;

One of the great things introduced in 8.4 was the ability to use any set returning function in the SELECT -- not just SQL and C functions. So Now you can do something like the below which would have required what we called a trojan hack in prior versions.

-- Example use
CREATE TABLE test_strings(test_id serial PRIMARY KEY NOT NULL, test_string text);
INSERT INTO test_strings(test_string)
VALUES ('abc|def|ghijkl'),('john|jack|jill');

SELECT test_id, dosomething_withtext(test_string)
FROM test_strings;


 test_id | myword
---------+--------
       1 | 3
       1 | 3
       1 | 6
       2 | 4
       2 | 4
       2 | 4

Here we see the while loop in action with the classic fibonacci function in Python

CREATE OR REPLACE FUNCTION pyfib(n bigint)
 RETURNS SETOF bigint
 AS $$
  a, b = 0, 1
  fibnum = []
  while b < n:
   fibnum.append(b)
   a, b = b, a+b
  return fibnum
 $$ LANGUAGE plpythonu;


-- Example in action
SELECT i
  FROM pyfib(10) As i;
 i
---
 1
 1
 2
 3
 5
 8
 
 
 --Which also allows us to do wtf
 SELECT wtf.test_id, SUM(wtf.fib) As tot_fib
 FROM (SELECT test_id, pyfib(dosomething_withtext(test_string)) As fib
    FROM test_strings) As wtf
 GROUP BY wtf.test_id;
 
  test_id | tot_fib
---------+---------
       2 |      21
       1 |      20

In our next exploration into PL/Python we will cover pulling data from postgresql and writing triggers and aggregate functions.

One of the great things about Python is the plethora of free libraries around to do very cool things from Engineering, statistical modeling, to BioInformatics.

In this Part we shall play around with a simple but pretty neat package called ASCII Plotter - we found at Python Package Index which appears to be kind of a CPAN except for Python instead of Perl.

For more details on using PL/Python check out PostgreSQL docs PostgreSQL 8.3 PL/Python docs

The ASCII Plotter package can also be dowloaded directly from http://www.algorithm.co.il/blogs/index.php/ascii-plotter/

Install ASCII Plotter classes

1. Download aplotter.zip from above website and extract.
2. This extracts to one file called aplotter.py
3. To install do the following - copy aplotter.py to your python25/lib folder (on windows its usually C:\Python25\lib and on Linux (/usr/lib/python2.5)

Using Python contribs in PL/Python and embedding classes

Lets quickly test by writing a function:

Now there are a bunch of functions in the aplotter lib, but the easiest to use is the plot function. Unfortunately the plot function does a print instead of a return which writes to stdout. To use in our PL/Python, we need to be able to grab that text instead of outputting to screen. I found this trick that seems to work nicely which is documented at redirecting python's print statements to a file except its a bit of a misnomer in that it tells you how to output to a buffer which is just what the doctor ordered.

Using our highly advanced copy and paste skills with artistic improvisation -- we have this

CREATE OR REPLACE FUNCTION dofunkyplot (param_anum integer)
  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
 #range (param_anum) will return array
 #consisting of 0 to param_num - 1 and formula 
 # gets applied to each element
 # [1.1*x/(2 + x) for x in range(2)] -> [0 1.1/3]
 aplotter.plot([1.1*x/(2 + x) for x in range(param_anum)])
 sys.stdout = saveout
 return outbuffer.content
$$ LANGUAGE plpythonu;

And now we can impress our friends with tricks like this: The below example will draw 2 plots.

SELECT dofunkyplot(n)
FROM generate_series(5,20, 10) As n;
  |
   +0.73                                                                    *
   |                                                               ---------
   |                                                      ---------
   |                                             ---------
   |                                    ---------
   |                                ----
   |                           -----
   |                      -----
   |                 -----
   |               //
   |             //
   |           //
   |         //
   |       //
   |     //
   |   //
   | //
---+0-----------------------------------------------------------------------+---
   +0                                                                     +4

   |
   +0.96                                                               -----*
   |                                              ---------------------
   |                                    ----------
   |                         -----------
   |                    -----
   |               -----
   |             --
   |          ---
   |        //
   |      //
   |    //
   |   /
   |  /
   | /
   | /
   |/
   /
---+0-----------------------------------------------------------------------+---
   +0                                                                    +14

Reading PostgreSQL data from PL/Python

Now plotting functions is nice, but sometimes we are asked to plot data in a database. Go figure why anyone would want to do that. Below makes fake data, function reads data from a PostgreSQL table and plots it.

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


CREATE OR REPLACE FUNCTION doplotmydata (param_numitems integer)
  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()
 #lets us just plot the first 10 items
 rv = plpy.execute("SELECT pt_id, pt_observation FROM mydata",param_numitems);
 sys.stdout = outbuffer
 #make pt_id x and pt_observation y
 aplotter.plot([x['pt_id'] for x in rv], [y['pt_observation'] for y in rv]);
 sys.stdout = saveout
 return outbuffer.content
$$ LANGUAGE plpythonu;

##and now to test
SELECT doplotmydata(20);

309.87                                            |                            +
                         /|         \ 
                        / |        | \ 
                         /  |        |  \ 
                       //    |       |   \            *
                      /      |      |     \\         /
                      /      |      |      \        /
                       /        |     |       \       /
                       /        |    |         \     /
                      /         |    |          \   /
            \         \       /         |    |           \  /
             /\        / \      /          |  |             \/
            /  \      /   \    /           |  |              /
           /    \    /     \   /           |  |
           //      \  /       \ /             ||
        -----         \/         \/             ||
    ------               /          /             ||
5.5967==-----------------------------------------------|--------------------+--+
   +1                                                                    +15

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                                                      /
---+------------------------------------------------------------------------+---

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

Product Sum

We have all seen aggregate functions that take one column as argument, but what about ones that take two or three or four or more. Why would you ever want to do this? The first example we will show is one that puts me to sleep, but demonstrates the point. We'll call it the prodsum. What it does is this.

Any astitute observer will observe that this is just a lame use of something as powerful as multicolumn aggregates because you can easily simulate it with the below. Its only nice because it allows us to validate our result.

You would form the aggregate product sum by doing this

CREATE OR REPLACE FUNCTION prod_state(prev numeric, e1 numeric, e2 numeric)
    RETURNS numeric AS
$$
    SELECT COALESCE($1,0) + COALESCE($2*$3,0);
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE AGGREGATE prodsum(numeric, numeric)(
    SFUNC=prod_state,
    STYPE=numeric,
    INITCOND=0
);

SELECT k, 
    prodsum(i,j + k) As prodsum , 
    sum(i*(j + k)) As regsum
FROM generate_series(1,4) As i 
    CROSS JOIN generate_series(1,2) As j 
    CROSS JOIN generate_series(0,1) As k
GROUP BY k
ORDER BY k;


 k | prodsum | regsum
---+---------+--------
 0 |      27 |     27
 1 |      45 |     45

 --Funky agg
CREATE OR REPLACE FUNCTION funky_state(prev numeric[2], e1 numeric, e2 numeric)
    RETURNS numeric[2] AS
$$
    SELECT  ARRAY[CASE WHEN $1[2] = 0 THEN 1*$3 ELSE $1[1] + POWER($2,$1[2])*$3 END, $1[2] + 1] ;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION funky_final(last numeric[2])
    RETURNS numeric AS
$$
    SELECT $1[1];
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE AGGREGATE funky_agg(numeric, numeric) (
      SFUNC=funky_state,
      STYPE=numeric[2],
      FINALFUNC=funky_final,
      INITCOND = '{1,0}'
    );
--The foos
SELECT i,j,k FROM generate_series(0,2) As i 
    CROSS JOIN generate_series(1,2) As j  
        CROSS JOIN generate_series(0,1) As k 
        ORDER BY k,i,j;

 i | j | k
---+---+---
 0 | 1 | 0
 0 | 2 | 0
 1 | 1 | 0
 1 | 2 | 0
 2 | 1 | 0
 2 | 2 | 0
 0 | 1 | 1
 0 | 2 | 1
 1 | 1 | 1
 1 | 2 | 1
 2 | 1 | 1
 2 | 2 | 1
(12 rows)

SELECT k , 
    funky_agg(foo.i,foo.j + foo.k) As what
FROM (SELECT i,j,k FROM generate_series(0,2) As i 
    CROSS JOIN generate_series(1,2) As j  
        CROSS JOIN generate_series(0,1) As k 
        ORDER BY k,i,j) As foo
GROUP BY k
ORDER BY k;


 k |         what
---+----------------------
 0 |  84.0000000000000000
 1 | 135.0000000000000000

k = 0: SELECT 1*1 + POWER(0,1)*2 + POWER(1,2)*1 + POWER(1,3)*2 
				+ POWER(2,4)*1 + POWER(2,5)*2 ; --84
k = 1:  SELECT 1*(1+1) + POWER(0,1)*(2 + 1) + POWER(1,2)*(1 + 1) 
			+ POWER(1,3)*(2+1) + POWER(2,4)*(1+1) + POWER(2,5)*(2+1) ; -- 135