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
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.
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.
You are developing a consultant search application where by a user looking for a consultant can check all the items in the list they require a consultant to have.
You have 2 tables. consultant and consultant_qual. The consultant_qual has primary key formed by 2 fields consultant_id and qual_id.
qual_id contains a constrained list
with things like 'OpenLayers', 'PostGIS', 'Python', 'C#' etc.
How do you devise a query that given a list of checked options by the user, only returns consultants with not 1 but all of those qualifications?