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

postgresql no_data_found

Programming Postgres

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. Almost as over hyped as PHP
• PL/R - Statistical Language, never used it.
• PL/Ruby - Great language. Over hyped maybe but is my current favorite open source language
• PL/Scheme - Lispy language, never used it
• PL/sh - Access to your OS shell language

Adding a new language to your existing database is very easy. You can get the details from the Postgres CREATE LANGUAGE syntax docs. The way you will most often add a language is: CREATE LANGUAGE For example: CREATE LANGUAGE pltcl; The language must already be installed in the OS. You will use the dynamic libraries from the language to create and run your programs. You also need the Postgres component for your language. PL/pgSQL, PL/TCL and PL/Perl come with the standard distribution of Postgres. You can get other languages at http://pgfoundry.org/. Run a search on pgFoundry to see what they have.

postgresql programming PL/pgSQL PL/TCL PL/Perl PL/Java language postgres