Sunday, October 7, 2007

Best Way to Handle No Data Found in a Procedure?

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. 00000 -  "no data found"
*Cause:    
*Action:
However, the equivalent procedure in Postgres:
CREATE OR REPLACE FUNCTION no_data_found_test()
  RETURNS void AS
$BODY$
DECLARE
  v_int_field INTEGER;
BEGIN

  SELECT empty_col
    INTO v_int_field
    FROM empty_table;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
Does not raise an exception when run:
postgres=# select * from no_data_found_test();
 no_data_found_test
--------------------

(1 row)
I have been using the FOUND variable to check for a result and raise an exception manually if no data was found. Like this:
CREATE OR REPLACE FUNCTION no_data_found_test()
  RETURNS void AS
$BODY$
DECLARE
  v_int_field INTEGER;
BEGIN

  SELECT empty_col
    INTO v_int_field
    FROM empty_table;

  IF NOT FOUND THEN
    raise exception 'NO_DATA_FOUND';
  END IF;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
When run, I do get an exception like I was expecting.
postgres=# select * from no_data_found_test();
ERROR:  NO_DATA_FOUND
However, after a little bit more reading, I see that I can add the STRICT keyword to make the procedure behave like Oracle:
CREATE OR REPLACE FUNCTION no_data_found_test()
  RETURNS void AS
$BODY$
DECLARE
  v_int_field INTEGER;
BEGIN

  SELECT empty_col
    INTO STRICT v_int_field
    FROM empty_table;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
And now when run, it does raise the exception:
postgres=# select * from no_data_found_test();
ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function "no_data_found_test" line 5 at SQL statement
postgres=#
Very Nice! I think I prefer this method. It would be nice if STRICT were a database wide configuration parameter. LewisC

Friday, October 5, 2007

Postgres LiveCD

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. Actually, the CD includes some sample databases and Slony-I, the database replication utility. Now, I didn't notice any documentation lying around, but my guess is one could probably mount the local hard drive and have PostgreSQL create databases on it all day long without ever installing the actual database server. Xubuntu still contains (most of) the other applications normally included - the team has simply loaded PG and it's client applications.
I couldn't find any links about where to get an ISO or anything so I went searching. I found some email list messages from 2006 and two interesting links: As a bonus, Dru links to Joshua Drake's Practical PostgreSQL. This free book is getting a little old but still makes a useful reference. LewisC

Thursday, October 4, 2007

Postgres Has Exceptional Documentation

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). It includes most SQL92 and SQL99 data types, including Integer, Numeric, Boolean, Char, Varchar, Date, Interval and Timestamp. It also supports storage of binary large objects, including pictures, sounds or video. It has native programming interfaces for C/C++, Java Latest News about Java, .Net, Perl, Python, Ruby, Tcl and ODBC, among others, and exceptional documentation.

Wednesday, October 3, 2007

PostgreSQL Site Makes it to Mashable

Mashable posted a SQL TOOLBOX: 20+ Tools and Lessons About SQL. While there are a lot more links to MySQL, at least Postgres made the list. The description isn't exactly the best I have seen though, "PostgreSQL.org - An open source database system developed on SQL." I've been to some of these sites and they aren't exactly the best around. Some are old. There are a couple of really good ones though. The Comparison of different SQL implementations seems pretty well maintained and covers Oracle, Postgres, MySQL, DB2, & SQL Server. Check it out. LewisC