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.
%type and %rowtype
------------------
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';
looping syntax
---------------
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;
displaying messages
-------------------
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.
nesting and recursive functions
---------------------------------
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 this has some great functions including functions in c;
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.
%type and %rowtype
------------------
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';
looping syntax
---------------
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;
displaying messages
-------------------
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.
nesting and recursive functions
---------------------------------
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 this has some great functions including functions in c;