Thursday, March 19, 2009

simple creating postgres functions

http://www.onlamp.com/pub/a/onlamp/2006/05/11/postgresql-plpgsql.html

Sign In/My Account | View Cart

advertisement

AddThis Social Bookmark Button


Writing PostgreSQL Functions with PL/pgSQL

by David E. Wheeler
05/11/2006

One of the most powerful features of PostgreSQL is its support for user-defined functions written in various programming languages, including pure SQL, C, Perl, Python, and PHP. Perhaps the most common programming language for PostgreSQL functions, however, is PL/pgSQL (don't ask me to pronounce it), because it comes with PostgreSQL and is easy to set up.

PL/pgSQL is a procedural language similar to Oracle's PL/SQL. It's much more powerful than pure SQL in that it supports variables, conditional expressions, looping constructs, exceptions, and the like. Because it natively supports all of PostgreSQL's SQL syntax, you can consider it a superset of PostgreSQL SQL. It also respects all data types and their associated functions and operators, and is completely safe for use inside of the server. Get all of the details in the PL/pgSQL documentation.

Installing PL/pgSQL

To get started with PL/pgSQL, first make sure it's installed in your PostgreSQL database. If it was a part of the template1 database when your database was created, it will already be installed. To see whether you have it, run the following in the psql client:

SELECT true FROM pg_catalog.pg_language WHERE lanname = 'plpgsql';

If the result row has the value true, PL/pgSQL is already installed in your database. If not, quit psql and execute the command:

$ createlang plpgsql database_name

To add a language, you must have superuser access to the database. If you've just installed PostgreSQL, then you can likely use the default postgres user by passing -U postgres to createlang. From this point, you should be able to follow along by pasting the sample functions into psql.

A First Function

To write your first PL/pgSQL function, start with something simple: a function to return the Fibonacci number for a position in the Fibonacci sequence. I know, I know; everyone uses a Fibonacci calculator to demonstrate code. Why can't I be original? Because a couple iterations of such a function will show off some of the more useful features of PL/pgSQL. It's purely pedagogical. A simple implementation is:

 1   CREATE OR REPLACE FUNCTION fib (

2       fib_for integer

3   ) RETURNS integer AS $$

4   BEGIN

5       IF fib_for < 2 THEN

6           RETURN fib_for;

7       END IF;

8       RETURN fib(fib_for - 2) + fib(fib_for - 1);

9   END;

10  $$ LANGUAGE plpgsql;

Using the function is easy:

try=% select fib(8);

fib

-----

 21

(1 row)

The first line uses PostgreSQL's CREATE OR REPLACE FUNCTION statement to create the function. The name of the function is fib. The CREATE OR REPLACE FUNCTION statement is more useful in practice than the simple CREATE FUNCTION statement, because it will drop an existing function with the same name and argument signature before creating the new one. This is very convenient while you're developing and testing a new function.

The second line declares the integer variable fib_for as the sole argument to the function, and thus constitutes its entire argument signature. The argument signature must come after the name of the function, inside parentheses. In this respect, it's not much different than function or method declarations in most programming languages. Arguments can be of any type supported by PostgreSQL, including user-created types and domains, as well as composite data types such as table row types. This article's examples will use only simple data types, but see the PL/pgSQL Declarations documentation for details.

Note that named arguments were added to PL/pgSQL in PostgreSQL 8.0. In earlier versions of PostgreSQL, you must either use the default, numbered variable names for the arguments, or declare aliases in a DECLARE block:

CREATE OR REPLACE FUNCTION fib ( integer ) RETURNS integer AS $$

DECLARE

 fib_for ALIAS FOR $1;

BEGIN

-- ...

Unless you have an older version of PostgreSQL, use named arguments. They're more convenient.

The third line closes the argument signature and specifies the function return value (integer). As with arguments, the return value of a function can be any PostgreSQL data type, including a composite type or even a cursor. The end of line three has the odd string $$. This is PostgreSQL dollar-quoting. When used in place of the usual single-quotation mark quoting ('), you don't have to escape single quotation marks within the body of the function. This makes them much easier to read.

Line four's BEGIN statement marks the start of the function body, while lines 5-8 are the function body, implenting the standard recursive algorithm for determining a Fibonacci number. Lines 5-7 use the PL/pgSQL IF-THEN conditional statement to return the sequence number itself if it is less than two. As with all blocks in PL/pgSQL, the IF-THEN conditional ends with a final END statement. Conditional expressions in PL/pgSQL can be any SQL expression that you might use in the WHERE clause of a typical SELECT statement. The nice thing here, however, is that you can use a variable (in this case, fib_for) in the expression.

Line five demonstrates the ability of PL/pgSQL to not only execute other PL/pgSQL functions, but to do so recursively. In this case, the fib() function calls itself twice in order to properly determine and return the Fibonacci number. Note that you can use the PL/pgSQL RETURN keyword anywhere in a PL/pgSQL function to terminate the execution of the function and return a value.

Line nine's END statement signals the end of the function body, while line ten closes the dollar quoting and identifies the function implementation language.

A Note on Statement Termination

At first glance, the placement of semicolons to terminate statements in the example function might appear to be somewhat ad hoc. I assure you that it is not. In PL/pgSQL, all blocks must terminate in a semicolon, as must all statements within that block. The expression that initiates the block, however, such as BEGIN on line 4 or IF fib_for <> on line five, does not end with semicolons. Line six, as a complete statement within the IF ... THEN block, ends with a semicolon, as does the statement on line eight.

Perhaps the simplest way to remember this rule is to think of statements as requiring semicolons, and block initiation expressions as not being complete statements. That is, blocks only become complete statements when they END.

Pages: 1, 2, 3

Next Pagearrow


-->
-

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

cancel script completely on ctrl-c

I found this question interesting: basically how to cancel completely a script and all child processes : You do this by creating a subro...