PLPython Part 2: Control Flow and Returning Sets   

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.