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.
This is a product that has nothing to do with PostgreSQL yet, so it does seem kind of odd that we are listing this in our product showcase section.
Well it is not even really a product per se, but I was just so enamored by the beauty of the layout and the usefulness of these cheat sheets, that I felt it was
worthy of being listed in our showcase section since some of these would be useful to the PostgreSQL community of programmers.
What is this product you ask? It is DZone RefCardz. When I discovered this array of cheat sheets, I became a glutton
and downloaded about 10 of them. It made me even want to start learning Ruby and also made me realize how little I know about CSS.
I was disappointed to find out that while they have an Essential MySQL, which by the way is extremely useful, they don't
also have an Essential PostgreSQL yet. Someone should really write one of these things for PostgreSQL, but I guess it would be best for that someone to be a published author
since it seems the main focus of RefCardz is as a publicity card for an author's book.
As a side note, it did get me thinking about the format of Postgres OnLine Journal cheat sheets and as many people have mentioned, perhaps we do try to cram too much information on one page.
The layout of RefCardz cheat sheets seems to provide a good balance between amount of content and brevity and made me realize that having a multi-page cheat sheet is not such a bad thing.
Their choice of colors, layout and diagrams is just mesmerizing.
Both Josh Berkus and Hubert made blog entries about our last excerpt. In general I will start off by saying that we are more or less in agreement on what is good SQL coding format.
Here are the things I think we can all agree on
SQL Keywords should be upper cased
Field names should be prefixed with their tables especially when you have a multi-join statement involved
Use JOIN syntax instead of stuffing everything in the WHERE though we are highly against just saying JOIN. We prefer INNER JOIN
The major points of contention I think are
Should you use aliases over tables and if you use aliases should you keep them short or more descriptive. Josh thinks table names should be used where possible and when aliases are used they should be longer than a few characters
and Hubert agrees with us that short aliases are fine and in fact desirable. I think we all agree aliases should be meaningful when used, but our idea of what it means to be meaningful is a little different.
In use of JOIN syntax -- we prefer using INNER JOIN instead of using just JOIN and in fact find it quite irritating that PostgreSQL rewrites our INNERs as JOIN. I suspect Hubert and Josh and many other PostgreSQL folk are on
the other side of the fence on this. The reason we feel strongly about this is there are so many kinds of JOINS - INNER JOIN, RIGHT JOIN, LEFT JOIN, CROSS JOIN, FULL JOIN, and the horrid NATURAL JOIN (that should be shot and put out of its misery). To just say JOIN to us is just confusing.
While you can write LEFT OUTER JOIN, the OUTER is kind of pointless because no one goes around writing LEFT INNER JOINS
Use well supported standards where possible. This means CURRENT_TIMESTAMP instead of now(). now() is not in all databases, but most relational databases support CURRENT_TIMESTAMP
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?