Quick Intro to PLPython

We have mentioned time and time again, one of the great selling points of PostgreSQL is that it has so many languages to choose from for writing database stored functions and the code you write in those stored functions is almost exactly the same as what you would write when writing in that language's environment. The reason for that is that PostgreSQL applies a thin layer around the environment the language lives in, so your code is really running in that environment. The down-side of this approach is you must have that environment installed on the server. This is a bit different from the Microsoft SQL Server model where code you write in VB.NET, C#, IronPython etc. gets translated into Common Runtime Logic (CLR) so your code is not really running in the environment it would normally breathe in and if you have dependencies you have to enable them in the SQL Server GAC which is different from the Server's .NET GAC.

In this section we shall introduce PL/Python - which is a PL language handler for Python that allows you to write PostgreSQL stored functions in Python. First of all I should start off by saying that we are not proficient Python programmer's so if anyone sees anything wrong with what we say feel free to embarass us.

We are also taking this opportunity to test-drive PostgreSQL 8.4 on both Linux (OpenSUSE) and Windows, using the EnterpriseDB PostgreSQL 8.4 beta that Dave Page recently announced on his blog. This install is great if you are running Windows, MacOSX or Linux Desktop, but sadly does not have PostGIS as part of the stack builder option.

For pure Linux Server CentOS/Redhat EL/Fedora no desktop installs or if you just feel more comfortable at the command-line, PostgreSQL Yum repository generously maintained by Devrim is the one to go for.

We haven't tested this one out, but I presume the steps are pretty much what we outlined in Using PostgreSQL Yum repository.

Installing PostgreSQL 8.4 beta

Some things to watch out for which may not be entirely obvious if Linux is new to you.

Alas the taste of the serpent: Installing Python

One thing I find very intriguing about the language Python is that it seems that every hot shot GIS programmer programs in it and prefers it to any other language. In fact it is almost a tautology, If you don't program in Python, you must not be a hot shot GIS programmer, though you could be a hot shot Spatial Database Programmer. I haven't used Python enough to figure out what these people see in this language, but there must be a reason for its strong following particularly in the GIS industry. Even ESRI applications install Python which seems kind of odd to me if you look at the strong .NET/Servlet infrastructure underneath the ESRI architecture. Their programmers must have been throwing severe temper tantrums for ESRI to allow this to happen.

Can I use PL/Python under PostgreSQL Windows?

Yes. Though in general Python is not preinstalled so you must install it.

Can I use PL/Pyton under PostgreSQL Unix

Of Course. What PostgreSQL thing can you not use under Unix? Mac OSX. In fact a lot of Linux installs have Python already loaded so your life is surprisingly easy here.

Installing PL/Python: Our gateway to the serpent

To install PLPython simply run the following on your favorite database. By either using the PgAdmin III or with psql

CREATE PROCEDURAL LANGUAGE 'plpythonu' HANDLER plpython_call_handler;

However if you get a message when installing it that it couldn't be loaded most likely you do not have Python 2.5 installed or it can not be found.

Our first PLPython stored function

PLPython is an untrusted language which means you can do dangerous things with it if you want and you should be more careful about what accounts you allow to write these functions.

Simple finding if a file exists
CREATE OR REPLACE FUNCTION fnfileexists(IN afilename text) RETURNS boolean AS
$$
    import os
    return os.path.exists(afilename)
$$
LANGUAGE 'plpythonu' VOLATILE;


--testing the function --
SELECT fnfileexists(E'C:\\test.htm')


fnfileexists
-----------
t

PLPython and default parameters

Now we shall test drive PL/Python with a new feature introduced in 8.4 called default parameters. As a side note, in an unrelated article entitled Chocolate and Peanut Butter Cross-Breeding with PostgreSQL, SQL Server 2008, and Oracle on our BostonGIS site, we griped a little bit about how PostgreSQL has no default parameters like Oracle does and alas in 8.4 it has it and better yet you can even use it in Python.

CREATE OR REPLACE FUNCTION  fndumencoder(randstring text, 
    mapfrom text DEFAULT 'abcdedfhijklmnopqrstuvwxyz', 
    mapto text DEFAULT 'bcdefghijklmnopqrstuvwxyza'  )
RETURNS text AS
$$
    import string
    mapt = string.maketrans(mapfrom, mapto)
    return randstring.lower().translate(mapt) 
$$
  LANGUAGE 'plpythonu' VOLATILE;


CREATE OR REPLACE FUNCTION  fndumdecoder(randstring text, 
    mapfrom text DEFAULT 'abcdedfhijklmnopqrstuvwxyz', 
    mapto text DEFAULT 'bcdefghijklmnopqrstuvwxyza')
RETURNS text AS
$$
    import string
    mapt = string.maketrans(mapto, mapfrom)
    return randstring.lower().translate(mapt) 
$$
  LANGUAGE 'plpythonu' VOLATILE;


--Testing the functions using default values
SELECT fndumencoder('Johnny thinks too much');
fndumencoder
------------
kpiooz uijolt upp nvdi

SELECT fndumdecoder('kpiooz uijolt upp nvdi');
fndumdecoder
------------
johnny thinks too much

--Testing using our own trivial mapping
SELECT fndumencoder('Johnny thinks too much', 'abcdefghijk', '11234567890');

fndumencode
------------
9o7nny t78n0s too mu27

SELECT fndumdecoder('9o7nny t78n0s too mu27', 'abcdefghijk', '11234567890');
fndumdecoder
------------
johnny thinks too much