Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

PostgreSQL Inc.: PostgreSQL FAQ

Stored Procedures

How to write stored procedures in Postgresql by newbiepg
Posted: 18 Apr 03 (Edited 23 Apr 03)

newbiepg (Programmer) Apr 17, 2003

Starting off:
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'
abc integer ;  --declare a variable without assigning a value
def varchar ;=''mystring'';   --declare a variable with a value


insert into some_table (col1) values (10);
return 10;
'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'
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;
'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'
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.

--some code here

'language 'plpgsql';

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


create function third(int4) returns varchar as'


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


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;
'language 'plpgsql';

looping syntax:

there is the if/then/end if loop

if some condition then
do something;
end if;

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

there is also the loop /end loop statement

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

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'
myret :=''done'';
raise notice ''hello there'';
raise debug ''this is the debug message'';
raise exception ''this is the exception message'';

return myret;
'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


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

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

'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;


Back to PostgreSQL Inc.: PostgreSQL FAQ Index
Back to PostgreSQL Inc.: PostgreSQL Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close