Wednesday, September 5, 2007

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