8.3 - 3   

In our PLPython Part 4: PLPython meets aggregates we demonstrated the power of PostgreSQL in combination with PLPython to create a sequence of ascii plots by using the power of aggregation. Our friend Simon Greener over at Spatial Db Advisor told me the example was clever but ascii plots was so 70ish and ugly, why didn't I do SVG plots? He felt people being predominantly visual would be caught up in the ugliness of Ascii plots and miss the point. At least Bob found them neat and can appreciate the elegance in simplicity, thanks Bob..

To make it up to Simon, we shall demonstrate 2 features we left out in our prior article.

1. Pretty SVG graphs -- so I don't have to hear more about SVG from Simon and how I'm caught in the 70s.
2. Ability to plot a schedule -- schedule item, start date, end date using a multi-column aggregate function. Here is a good use for multi-column aggregates or at least I think so. (Yeh for Multi-column aggregates)

If you are into spatial processing in the database or how many ways you can play with XML particularly Oracle Locator, Oracle Spatial, PostGIS and SQL Server 2008, we highly recommend Simon's articles.

PostgreSQL 8.2 and above has this pretty neat feature of allowing you to define aggregate functions that take more than one column as an input. First we'll start off with a rather pointless but easy to relate to example and then we'll follow up with something a bit more interesting.

For more examples of creating aggregates in PostgreSQL, check out our other articles:

• Aug/Sept 2008: Build Median Aggregate Function in SQL
• Aug/Sept 2008: More Aggregate Fun: Who's on First and Who's on Last
• Feb/March 2009: PLPython Part 4: PLPython meets aggregates

This article is a bit of a companion to our article on Setting up PostgreSQL as a Linked Server in Microsoft SQL Server 64-bit

In this article we shall demonstrate using Microsoft SQL Server 2005/2008 OPENQUERY AND OPENROWSET to add, delete and update data in PostgreSQL.

First we must start by saying there are a number of ways of copying data between databases. While OPENROWSET is not necessarily the fasted, in certain cases such as when you are wrapping this in a stored procedure, it is one of the most convenient ways of doing this.

Why on earth would you want to copy data back and forth between 2 servers and 2 disparate DBMS systems for that matter? We all would like to think we are an island and live in a world with one DBMS system, but we don't. There are many reasons for having multiple DBMS providers in an organization. Some are better for some things than others, some are more integrated in an environment -- (for example in a windows shop the SQL Server drivers are already loaded on all windows machines, but PostgreSQL provides the advantage of being able to run on more platforms such a FreeBSD/Unix/Linux box and with cheaper cost and more options for PL programming so is often better for a front-facing DMZ accessible database), and there are numerous other reasons that are too hard to itemize. The other question of why triggering from SQL Server instead of PostgreSQL is because its just a little easier from Microsoft SQL Server. The OPENROWSET and OPENQUERY logic that SQL Server provides is just simply better and easier to use than the dblink provided for PostgreSQL. Anyrate with that said lets move on with the show.

Although this example is focused on using PostgreSQL with Microsoft SQL Server, the same technique applies when copying retrieving updating data from other databases such as MySQL or Oracle or DB II.

In our August 2008/ September 2008 issue we demonstrated the power of PostgreSQL to create median and MS Access-like first and last aggregate functions in SQL language. In this article we shall demonstrate how to create aggregates with Python. We shall call this function agg_plot. What it will do is plot each grouping of data and return a plot for each grouping. The steps we covered in those articles can be applied here.

For this part, we shall use the same library we discussed in PLPython Part 3: Using custom classes, pulling data from PostgreSQL.

One of the great things about Python is the plethora of free libraries around to do very cool things from Engineering, statistical modeling, to BioInformatics.

In this Part we shall play around with a simple but pretty neat package called ASCII Plotter - we found at Python Package Index which appears to be kind of a CPAN except for Python instead of Perl.

For more details on using PL/Python check out PostgreSQL docs PostgreSQL 8.3 PL/Python docs

The ASCII Plotter package can also be dowloaded directly from http://www.algorithm.co.il/blogs/index.php/ascii-plotter/

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.

Problem 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?

In the first part of this series Fusion Charts and PostgreSQL Part 1: Database Analysis of USDA DB in our November/December 2008 issue, we did some back-seat analysis of a database we had never seen before and formulated some thoughts of things that would be useful to see in a dashboard as well as starting to develop some views to support our Dashboard. In the second part of our Fusion Charts series, we covered creating a Dashboard application in ASP.NET that demonstrated both VB.NET and C# variants using the database we loaded and prepped in Part 1.

In this part three of our series, we shall conclude by demonstrating the same application we did in ASP.NET in PHP.

We are going to create a simple dashboard that has the following features:

1. A drop-down list to allow the user to pick the kind of chart to display the data in (Bar, column, funnel etc)
2. A drop-down list that allows the user to pick the metric to explore -- e.g. Cholestrol, Vitamin K, Caffeine etc.
3. 2 charts -- one chart showing the top 5 food groups for our metric and another showing the top 5 foods for our metric

Our final product will look like this:

You can see the app in action - USDA Food Stats and discover some interesting things about the food you eat or were considering eating.

This question was asked on the PostgreSQL novice newsgroup recently and Tom Lane fielded the question. Its a common thing to want to know if you are using the new 8.3 Enum feature. So we felt it useful to restate it.

Question: Given an enum, is there a query you can run to list out all the elements allowed by the enum?

Answer: Yes.

CREATE TYPE myenum as enum ('red','green','blue');
SELECT enumlabel 
   FROM pg_enum 
   WHERE enumtypid = 'myenum'::regtype 
   ORDER BY oid;

 enumlabel
-----------
 red
 green
 blue
(3 rows)

http://www.postgresql.org/docs/8.3/static/catalog-pg-enum.html

In the first part of this series Fusion Charts and PostgreSQL Part 1: Database Analysis of USDA DB in our November/December 2008 issue, we did some back-seat analysis of a database we had never seen before and formulated some thoughts of things that would be useful to see in a dashboard as well as starting to develop some views to support our Dashboard.

In this part, we start the fun off by building an ASP.NET app in both VB and C#. In the next part of this series, we shall perform the same feat with PHP.

We are going to create a simple dashboard that has the following features:

1. A drop downlist to allow the user to pick the kind of chart to display the data in (Bar, column, funnel etc)
2. A drop downlist that allows the user to pick the metric to explore -- e.g. Cholestrol, Vitamin K, Caffeine etc.
3. 2 charts -- one chart showing the top 5 food groups for our metric and another showing the top 5 foods for our metric

Our final product will look like this:

You can see the app in action - USDA Food Stats and discover some interesting things about the food you eat or were considering eating.

PSQL is the command line administration/query tool for PostgreSQL. It is simple, light-weight and packed with a lot of charm. Unfortunately we can't quite capture all its charm in a single pager cheat sheet, but hopefully this will give you a sense of its usefulness.

Below is a Thumbnail view of the PSQL cheat sheet. This is by no means comprehensive, but are the features we use most often. It is also pretty much what you get from the help screen with just some added color.

PDF Portrait version 8.5 x 11" of this cheatsheet is available at PSQL8.3 Cheatsheet 8.5 x 11 and also available in PDF A4 format and HTML.

Hubert has some neat tips for making more effective use of psql

• keyboard shortcuts in psql
• Suppressing CONTEXT lines in psql

To finish off our PL/PGSQL tutorial series, we are providing a PL/PGSQL cheat sheet.

Below is a Thumbnail view of the PostgreSQL 8.3 PL/PGSQL cheat sheet that covers both 8.3 new features and past core PL/PGSQL features. This is by no means comprehensive, but are the features we use most often.

PDF Portrait version 8.5 x 11" of this cheatsheet is available at PostgreSQL 8.3 PL/PGSQL Cheatsheet 8.5 x 11 and also available in PDF A4 format and HTML.

We had the pleasure of doing a fresh install of PostgreSQL 8.3.5 on RedHat EL4 box and when using the Yum repository, we noticed a couple of changes from last time we did this. This could have been an oversight in our documentation before.

Changes to Yum Install for 8.3.5?

In our April 2008 issue we had An Almost Idiot's Guide to PostgreSQL YUM and that article still seems to be surprisingly popular.

In the first step we had:
yum install postgresql
and that as I recall installed the postgresql server in addition to some client libraries.

For 8.3.5 fresh install it seems they are separated and to get the postgresql server you need to do:

yum install postgresql
yum install postgresql-server