When it comes to data issues (too many rows, no data found, etc), in Oracle stored procedures, I am used to having exceptions raised that I then handle. PL/pgSQL does not raise exceptions for the same conditions in the same way. The Postgres docs are pretty complete though and through some reading this weekend, I discovered a new keyword.
For example, assuming that I have this table (which is empty) in both Oracle and Postgres:

CREATE TABLE empty_table
(
  empty_col integer
);

In Oracle this procedure:

CREATE OR REPLACE PROCEDURE no_data_found_test
AS
  v_int_field INTEGER;
BEGIN

  SELECT empty_col
    INTO v_int_field
    FROM empty_table;

END;

When run:

BEGIN
  no_data_found_test;
END;

Produces an error:

Error starting at line 1 in command:
BEGIN
  no_data_found_test;
END;
Error report:
ORA-01403: no data found
ORA-06512: at "HR.NO_DATA_FOUND_TEST", line 6
ORA-06512: at line 2
01403. 

I recently wrote a blog entry (on my Postgres blog) about hiding SQL in a stored procedure, Hiding SQL in a Stored Procedure. I decided to see if I could convert that same concept to a MySQL stored procedure.



It doesn’t work exactly the same. For one, the syntax is a little different. I expected that and the syntax differences really aren’t that bad. Minor tweaks really.



The second issue is the major one. While I could write the proc and return a result set, I am not, as far as I can tell, able to treat the procedure as a table.

I read about the Postgres LiveCD at the Blue Gnu.
That’s such a good idea! I don’t know why we don’t see more LiveCDs. Even commercial software should be taking advantage of such an easy marketing opportunity.
The entry says:

Well, what can I say? The CD is simply Xubuntu with PostgreSQL loaded up and ready to run. And once loaded, you can actually play with PostgreSQL at least three different ways. The Xubuntu CD offers three icons that launch the Command-Line client, PGAdmin3 or phpPGAdmin. Now you can play with the server for as long as you like, goof it up and reboot for a clean, shiny new server.

Yesterday, Postgres made it to Mashable, today it got a mention in E-Commerce Times, Open Source for Business: Now More Than Ever, Part 1.
The article isn’t about Postgres specifically. It’s about business targeted OSS uptake in the enterprise. There’s an “open source roundup” “based on the recommendations of business leaders”.
The Postgres entry:

Postgresql: PostgreSQL is an open source relational database system. It runs on all major operating systems, including Linux, Unix (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64) and Windows. It is fully ACID (Atomicity, Consistency, Isolation, Durability) compliant, has full support for foreign keys, joins, views, triggers and stored procedures (in multiple languages).

Bob Zurek of EnterpriseDB posted a blog entry today titled, “We slammed into a brick wall with MySQL“. If you read his blog entry, the information he is referencing is in this press release, FortiusOne Migrates GeoCommons Intelligent Mapping Website to EnterpriseDB Advanced Server.
If you read that press release, it says:

“We slammed into a brick wall with MySQL,” said Chris Ingrassia, chief technology officer, FortiusOne. “As an example, MySQL’s rather limited and incomplete spatial support dramatically impacted performance. We were looking for an affordable database solution, but we required enterprise-class features and performance that MySQL simply couldn’t deliver.

Do you do GEO? If you do, you’ve probably at least heard of PostGIS, the Postgres GIS extension. PostGIS just gave EnterpriseDB a big win over its open source competition.

FortiusOne leads the market towards the next generation of Web mapping. Its breakthrough Intelligent Mapping technologies offer rich information visualization on maps and unprecedented access to geographic data. FortiusOne innovations include: high-speed Web-based geographic analysis tools, a flexible and scalable Web services platform supporting the special needs of geographic data, and an innovative application of social networking techniques to geographic knowledge creation.

FortiusOne’s main product is GeoCommons.

If you are not familiar with tablespaces you may be wondering what the big deal about them is. Tablespaces are a logical addition to a database that helps maintenance, and potentially, can improve performance.



In Oracle and MySQL, a tablespace is a logical unit meant to store segments (i.e. tables and indexes). In Postgres, a tablespace is a physical unit. It is a symbolic link to a directory. Postgres does not allow tablespaces on operating systems that do not support symbolic links (such as windows).



The data file is the actual physical storage mechanism in Oracle and MySQL. Postgres stores tables in individual files.

I’m sure you’ve heard that it is a bad practice to embed SQL in your applications and that all direct SQL access should be encapsulated (or hidden) in stored procedures. I’ve had people ask me exactly what that means so below is a very simple example of that.
I am encapsulating a single table. This is really not that beneficial. Where this gets very powerful is when you are joining many tables or performing complex logic. You can use this method to hide schema complexity from any application that can query data.

A good article on Motley Fool, discusses Oracle’s outstanding financial performance. I still think Oracle needs to do more to counter open source offerings but numbers like these are hard to argue with.

The numbers continue to speak for themselves at Oracle. First-quarter sales advanced 26% and earnings grew 28%. Better yet, new software license revenue grew 35%, well ahead of the 23% growth in license updates and product support and the 25% improvement in services revenue. Management explained it by noting that its installed base is growing, which only leads to lucrative product updates and related services down the road.

It looks like buying out the competition is having a doubly good effect.

This is just a quick overview of the availability of programming for Postgres. With Oracle, your choice in the database is Java or PL/SQL. In general, PL/SQL is the preferred language.
One of the things that attracted me to Postgres was the ability to choose one of many languages to program in. I usually choose PL/pgSQL but if I can’t do something with that, it’s nice to be able to fall back to TCL, Perl or one of the many other languages supported by Postgres.
Here is a (incomplete, I’m sure) list of supported languages:

• PL/pgSQL – Standard Procedural Language for Postgres
• PL/TCL – TCL, Tool Command Language, easy to learn but powerful
• PL/Perl – Practical Extraction and Report Language, has grown way beyond its origins
• PL/Java – Java, meh
• PL/PHP – PHP: Hypertext Preprocessor, over hyped language
• PL/Python – Object Oriented, dynamic language.

Do you remember Foxpro? xBase? Clipper? I’m an old clipper head from way back. Anyway, I ran across something in my recent internet travels that caught my fancy. It may have the longest name of any utility I have ever seen.
FoxPro PostgreSQL Import, Export & Convert Software will transfer tables to and from PostgreSQL and FoxPro.
I haven’t downloaded it yet so I can say how well it works but it’s a neat idea. I’m guessing it can handle DBF files so that would make it compatible with dBase, Foxpro, Clipper and may other tools.