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.
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
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.