Quick Intro to PLPython   

Installing PostgreSQL 8.4 beta

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

• Before you can run the Linux .bin installs, you must make them executable by either chmod 777 thebinfile or in explorer GUI right-click and mark as executable. Windows users can skip this step.
• If you are running another PostgreSQL on your box, give this a different port when the wizard asks, say 5434 or 5433.

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.

• Install Python 2.5 by getting from http://www.python.org/download/releases/ or using Linux distro. It must be Python 2.5 since that is what the PostgreSQL 8.4 beta builds are compiled against. As of this writing Python 2.5.4 is the latest of the 2.5 series.

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

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.

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