PLPython Part 2: Control Flow and Returning Sets

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

The If Then statement

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');

Try Exception

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');

For loops and returning sets

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

While Loops

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.