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

Hiding SQL in a Stored Procedure

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. First I create a simple table and populate it:

create table test_data (
 name text,
 address text,
create_date timestamp );

insert into test_data values (
         'lewis',
        '123 abc st',
        timestamp '2001-01-01 10:00:00');
insert into test_data values (
        'george',
        '456 def dr',
        timestamp '2091-01-01 10:00:00');

postgres=# select * from test_data;
 name  |  address   |     create_date
--------+------------+---------------------
lewis  | 123 abc st | 2001-01-01 10:00:00
george | 456 def dr | 2091-01-01 10:00:00
(2 rows)

Next I create a very simple function. This function accepts two timestamps and returns any records that have a creation data falling within those dates.

CREATE OR REPLACE FUNCTION get_data_by_creation(
     timestamp without time zone, 
     timestamp without time zone)
RETURNS SETOF test_data 
AS
$$ 
  SELECT name, address, create_date
    FROM test_data
    WHERE create_date >= $1
      AND create_date <= $2;
$$
LANGUAGE 'sql' VOLATILE;

And then I select from the function passing in different values as input:

postgres=# select * 
             from get_data_by_creation(
               localtimestamp - interval '10 years', 
               localtimestamp);
 name  |  address   |     create_date
-------+------------+---------------------
 lewis | 123 abc st | 2001-01-01 10:00:00
(1 row)

postgres=# select * 
             from get_data_by_creation(
               localtimestamp, 
               localtimestamp + interval '100 years');
  name  |  address   |     create_date
--------+------------+---------------------
 george | 456 def dr | 2091-01-01 10:00:00
(1 row)

As I said above, this is a very simple example. The usefulness becomes very apparent when you are trying to hide the complexities of your schema from application programmers and users.

postgresql function

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

10 Steps to Creating a Function In PostgreSQL Using PLpgSQL

It's actually fairly easy to create a function using PLpgSQL, especially if you are coming from a database background like Oracle or DB2. Both have procedural languages that look a lot like PLpgSQL. I'll go ahead and show you the code for a very basic function and then I'll explain the steps individually.

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

  RETURN local_char_var;
END;
$$ LANGUAGE plpgsql;

Ok. Now we'll go through it line by line:

1. CREATE OR REPLACE FUNCTION func_1() - This line creates and names the function. The "OR REPLACE" will let us modify the function without fir DROPping it. func_1 can be any valid PLpgSQL name. Even if you are not declaring parameters, you must include the parenthesis ().
2. RETURNS CHAR VARYING(25) - RETURNS is the keyword that signifies what data type the function will be returing. RETURN is the equivalent in PL/SQL. Notice the S. CHAR VARYING is the equivalent of a VARCHAR2 in PL/SQL. In this case it will be a VARCHAR2(25).
3. AS $$ - AS is the same as the PL/SQL AS. The $$ is the function code delimiter. You can actually use single quotes here instead of $$ but in that case you would need to double all the quotes in your code. If that doesn't make sense, just consider the $$ mandatory. PLpgSQL is a more basic language than PL/SQL. The code is stored as text and compiled as it is run. The entire function body is just a string and the $$ is the delimiter.
4. DECLARE - used to begin the variable declaration area. If you are not declaring local variables, this is an optional keyword.
5. local_char_var CHAR(30); - A locally declared variable.
6. BEGIN - Begins the body of the the function.
7. SELECT datname INTO local_char_var FROM pg_database LIMIT 1; - This select statement is selecting the database name from the PG_DATABASE data dictionary table. The database name is being stored in the local variable local_char_var. The query is limiting the result set to 1 row.
8. RETURN local_char_var; - This line returns the local variable back to the calling program.
9. END; - Ends the body of the function.
10. $$ LANGUAGE plpgsql; - The $$ is the end delimiter of the function body (think of it as ending the string). The LANGUAGE plpgsql identifies the language type to the postgres engine so that it knows which language to run the program.

That's it for a very basic function. I'll build on this in the future. LewisC

postgresql function plpgsql programming