Friday, March 06. 2009
Printer Friendly
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.
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 PL\Python cheat sheet. This is by no means comprehensive, but are the features we thought may be useful to know.
PDF Portrait version 8.5 x 11" of this cheatsheet is available at PL/Python Cheatsheet 8.5 x 11 and also available in
PDF A4 format and HTML.
Also check out our accompanying tutorials on the topic of PL/Python:
- Quick Intro to PLPython
- PLPython Part 2: Control Flow and Returning Sets
- PLPython Part 3: Using custom classes, pulling data from PostgreSQL
- PLPython Part 4: PLPython meets aggregates
Thursday, March 05. 2009
Printer Friendly
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:
Continue reading "How to create multi-column aggregates"
Thursday, February 19. 2009
Printer Friendly
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.
Continue reading "Using Microsoft SQL Server to Update PostgreSQL Data"
Thursday, February 05. 2009
Printer Friendly
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.
Continue reading "PLPython Part 4: PLPython meets aggregates"
Wednesday, February 04. 2009
Printer Friendly
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/
Continue reading "PLPython Part 3: Using custom classes, pulling data from PostgreSQL"
Friday, January 30. 2009
Printer Friendly
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
PL/Python.
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.
Continue reading "PLPython Part 2: Control Flow and Returning Sets"
Thursday, January 22. 2009
Printer Friendly
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.
Continue reading "Quick Intro to PLPython"
Wednesday, January 07. 2009
Printer Friendly
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.
Tuesday, January 06. 2009
Printer Friendly
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
Continue reading "SQL Coding Standards To Each His Own Part II"
Sunday, January 04. 2009
Printer Friendly
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?
Continue reading "How to require all checked conditions are met by a result"
Saturday, January 03. 2009
Printer Friendly
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:
- A drop-down list to allow the user to pick the kind of chart to display the data in (Bar, column, funnel etc)
- A drop-down list that allows the user to pick the metric to explore -- e.g. Cholestrol, Vitamin K, Caffeine etc.
- 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.
Continue reading "Fusion Charts and PostgreSQL Part 3: PHP Dashboard"
Wednesday, December 31. 2008
Printer Friendly
I was reading Josh Berkus last blog post and was intrigued by his last post
Writing Maintainable Queries Part I.
He is right that lots has been said about coding standards in other languages and even right out holy wars have been launched on the
subject, but as far as SQL goes, not quite enough has been said on the subject for us to have a great war to end all wars.
I was also happy to see that we agreed with all his points except his first one. Yes I felt dissed, and thought hmm
if someone as important as Josh thinks our aliases should be very descriptive and we should use the table name rather than
the alias where possible, surely there must be something wrong with me for not believing in this fundamental philosophy.
In the rest of this excerpt I shall make fun of Josh and also set forth some of our own SQL Coding guidelines. Hopefully
Josh won't take too much offense at this small jibe.
Continue reading "SQL Coding Standards To Each His Own"
Saturday, December 20. 2008
Printer Friendly
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.
Below is just a regurgitation of the news item http://archives.postgresql.org/pgsql-novice/2008-12/msg00043.php
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
Friday, December 19. 2008
Printer Friendly
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:
- A drop downlist to allow the user to pick the kind of chart to display the data in (Bar, column, funnel etc)
- A drop downlist that allows the user to pick the metric to explore -- e.g. Cholestrol, Vitamin K, Caffeine etc.
- 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.
Continue reading "Fusion Charts and PostgreSQL Part 2: ASP.NET Dashboard"
Printer Friendly
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
|