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

Calling a Procedure or Function in Postgres

I think PL/SQL programmers who move to Postgres all run into the same thing, how do I run the procedure or function once I've created it?

Obviously, it's easy when you are calling it from another stored procedure or function.  What most mean, is how do I call it from the command line?  That's easy too.

Using my function and procedure from earlier posts, i.e. 10 Steps to Creating a Function In PostgreSQL Using PLpgSQL and Creating a Procedure In PostgreSQL Using PLpgSQL.

To call either the function or the procedure, you can run:

SELECT func_1();

Or

SELECT * FROM func_1();

You must include the ().

Same for the procedure.

SELECT proc_1();

Or

SELECT * FROM proc_1();

Of course, if you are using a GUI tool like PgAdmin III, it's a somewhat different story.  You need to open the query window but the syntax will be the same.

Technorati Tags: postgres, postgresql, procedure, create, declare, plpgsql, function, code, develop

Creating a Procedure In PostgreSQL Using PLpgSQL

I recently wrote about creating a function in PL/pgSQL, 10 Steps to Creating a Function In PostgreSQL Using PLpgSQL.

Today, I am going to show you how to create a procedure.  You don't really create a procedure, you use the same basic syntax as you do for a function.  The RETURNS keyword can get a little tricky as that keyword requirement changes based on what you are trying to do.  To start, I will create a procedure that functions the same as the function in the previous entry.  Then I will make some changes.

CREATE OR REPLACE FUNCTION proc_1(
  OUT out_parameter CHAR VARYING(25) )
AS $$
DECLARE
BEGIN
  SELECT datname
    INTO out_parameter
    FROM pg_database
    LIMIT 1;

  RETURN;
END;
$$ LANGUAGE plpgsql;

If you compare this to the function from the previous entry some things jump out right away.  There is no RETURNS statement.  Postgres is able to determine the return type from the OUT parameter.

The DECLARE keyword is completely optional.

The RETURN keyword has no operand.  I don't need to RETURN a variable because the OUT parameter will be the return value.  As a matter of a fact, if I try to RETURN a value, I will get an error.

Below is another procedure that does basically the same thing but does not return a value.  I am showing you this just to show you how to declare a procedure that truly acts as a procedure (that does not return any values).

CREATE OR REPLACE FUNCTION proc_3()
RETURNS void
AS $$
DECLARE
  local_char_var CHAR(30);
BEGIN
  SELECT datname
    INTO local_char_var
    FROM pg_database
    LIMIT 1;

  RETURN;
END;
$$ LANGUAGE plpgsql;

Notice in this procedure that I am declare the return value as RETURNS void.  If you are a C, Java or C# programmer this should be very familiar.

For a PL/SQL programmer, just consider that a FUNCTION RETURNS VOID is the same as a PROCEDURE in PL/SQL.

That's it for this post.  If you have anything specific that you would like me to cover, please leave a comment or drop me an email.

Thanks,

LewisC

 

Technorati Tags: postgres, postgresql, procedure, create, declare, plpgsql, function, code, develop