How to write stored procedures in Postgresql

newbiepg (Programmer) Apr 17, 2003

[color #ff0000]Starting off:[/color]
Before you start using plpgsql you need to initialize the call handler with this command

create function plpgsql_call_handler()
returns opaque as '/usr/lib/pgsql/plpgsql.so' language 'c'


if you are not sure where the plpgsql.so file is
type locate plpgsql.so at the command line

after initializing the call handler you need to add the plpgsql language . This can be done by typing

create function plpgsql_call_handler()
returns opaque
as '/usr/local/pgsql/plpgsql.so'
language 'c'



the basic function syntax is
create function function_name() returns int4 as'
declare
abc integer ; --declare a variable without assigning a value
def varchar ;=''mystring''; --declare a variable with a value

begin

insert into some_table (col1) values (10);
return 10;
end;
'language 'plpgsql';


a function always has to return a value
you can return any normal data type boolean, text, varchar, integer, double
date, time , void etc
a trigger normally returns opaque

you can also pass parameters with functions, a single function can have
upto 16 parameters.

aliases are used to call parameters values passed. The first parameter is $1, the second is $2
and so on.

you may also declare constant variables and variables with default values

this is an example of using functions with parameters

create function sec_func(int4,int4,int8,text,varchar) returns int4 as'
begin
myint constant integer := 5;
mystring char default ''T'';
firstint alias for $1;
secondint alias for $2;
third alias for $3;
fourth alias for $4;
fifth alias for $5;
ret_val int4;

select into ret_val employee_id from masters where code_id = firstint and dept_id = secondint;
return ret_value;
end;
'language 'plpgsql';

this function need to be called by
select sec_func(3,4,cast(5 as int8),cast('trial text' as text),'some text');

note that numbers passed as parameters are by default int4
so we need to cast 5 int int8 or bigint.

[color #ff0000]%type and %rowtype:[/color]

sometimes we need to declare a variable according to the datatype of a column
this can be achoieved using %type

create function third_func(text) returns varchar as'
declare
fir_text alias for $1;
sec_text mytable.last_name%type;
--here in the line above will assign the variable sec_text the datatype of
--of table mytable and column last_name.
begin

--some code here

end;
'language 'plpgsql';

sometimes we need to get the structure of a table's row
we use %rowtype then

e.g.

create function third(int4) returns varchar as'

begin

myvar alias for $1;
mysecvar mytable%rowtype;
mythirdvar varchar;

begin

select into mysecvar * from mytable where code_id = myvar;
--now mysecvar is a recordset
mythirdvar := mysecvar.first_name|| ' '|| mysecvar.last_name;
--|| is the concatenation symbol
--first_name and last_name are columns in the table mytable
return mythirdvar;
end;
'language 'plpgsql';


[color #ff0000]looping syntax:[/color]

there is the if/then/end if loop

if some condition then
do something;
end if;

otherwise
if some condition then
do something;
else
some more code;
end if;

there is also the loop /end loop statement

loop
some code;
end loop;


the while- loop statement is like this
while myvar<10 loop
some more code;
end loop;


[color #ff0000]displaying messages:[/color]


wee can display messages in the output we can use the raise debug ,raise notice and raise exception
statements

the differences are mostly in the log files and severity.
raise debug can be viewed by the client and ignored by the database in production mode.
raise notice gets a mention in the postgresql log and is seen by the client. raise exception causes both
mentioned before and also causes a transaction to rollback.

a simple example is given below

create function mess() returns varchar as'
declare
myret :=''done'';
begin
raise notice ''hello there'';
raise debug ''this is the debug message'';
raise exception ''this is the exception message'';

return myret;
end;
'language 'plpgsql';


call the function
select mess();
you will get some messages shown at the console too.

[color #ff0000]nesting and recursive functions:[/color]


as of 7.1.3 version the database did not support recursive functions
however it is possible to call a function inside another without returning a value

to do this we need to use

e.g.

create function test(int4,int4,int4) returns int4 as'
declare
first alias for $1;
sec alias for $2;
third alias for $3;

perform another_funct(first,sec);
return (first + sec);

end;
'language 'plpgsq';

not confused yet?

go to this link http://www.brasileiro.net:8080/postgres/cookbook/
this has some great functions including functions in c;