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