Thursday, June 24, 2010

Function in PL/PgSQL

before creating function, check your database has PL/PgSQL here

* create function

simple pl/pgsql function, adding row to the test table

CREATE OR REPLACE FUNCTION pg_function_insert(
eno INTEGER) RETURNS VARCHAR AS $$
DECLARE
eno_end INTEGER := eno + 5;
BEGIN
FOR num IN eno.. eno_end LOOP
insert into test values (num,'solai');
END LOOP;
RETURN 'rows success fully inserted' ;
END;
$$ LANGUAGE plpgsql;

* execute function

SELECT pg_function_insert(100) AS function_output

we created function with function name of pg_function_insert,input parameter is Integer and function returns String.
table object test has two column eid with integer and ename with varchar data type.
in for loop initial value is input value given by user while executing function ( in our example 100 ), loop will iterate five times because loop end value is eno+5.

in create function statement we have added $$, 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. more about $$

* now you can run a query to view the result

select * from test

eid|ename

100|solai
101|solai
102|solai
103|solai
104|solai
105|solai

| is column separator

Setting up PL/PgSQL

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 is PL/pgSQL, 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 etc.. for more details in this blog we are going to learn how adding PL/PgSQL to your Database

Installing PL/pgSQL to Database


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 as postgres superuser

$ createlang plpgsql database_name
or
cd /usr/local/pgsql/bin/
$ createlang plpgsql -U postgrers_super_uesrname 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 write and execute function in your pg client.
NOTE : above we just added PL/PgSQL to particular database only, not for entire server.

To create function in postgres can go here