Thursday, January 22. 2009

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.

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

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.

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 tha

Weblog: Postgres OnLine JournalTracked: Jan 30, 14:03

PL Python Cheatsheet Overview
PL/Python is the procedural language for PostgreSQL that allows you to write database stored functions and triggers in Python. Python has proved to be a charming language and when used for where it excels, enhances the power of PostgreSQL quite nicely.

Weblog: Postgres OnLine JournalTracked: Mar 06, 20:59

PostGIS, PL/Pyton, Events, Mapserver XML Mapfiles
PostGIS 1.4 out soon
PostGIS 1.4 will be out soon, which will be good because it feels like forever we've had this release baking in the oven. The key changes are as follows:
ST_IsValidReason() -- requires GEOS 3.1 -- will tell you why a geometry i

Weblog: BostonGIS BlogTracked: Mar 20, 03:56

What is new in PostgreSQL 8.4
PostgresQL 8.4 beta will be out any day and 8.4 official release will hopefully not be too far behind. As we wait patiently for the official release,
Robert Treat has summarized nicely all the new features you can expect in 8.4.
PostgreSQL 8.4 is what

I have written a trigger that calls a PLPython function after an event. I have read that the data changes from an event are visible to the trigger procedure, if it follows the event, but how do I access it? I want the id of the row entered into the database and use it in plpython.

Jai,
Is your trigger function written in Python or are you just calling another python function from another function?

Basically in a trigger function there are objects called

NEW (available in insert,update triggers)
OLD (avaialble in insert,update, delete triggers)

And those have the same structure as the record being updated and contain data before (OLD) data was changed and NEW (after) data was changed. If you called a plpython function, just need to pass the relevant data from NEW or OLD to it. That is the name in plpgsql.

Like

SELECT mypy(NEW.field1, NEW.field2)

etc.

However if your trigger function is written in python then you use the TD dictionary variable. Something like this

E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.Enter the string from the spam-prevention image above: