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

No comments: