Example of a Postgre Stored Procedure

Stored Procedures seem to stump almost everyone trying to migrate from M$SQL to Postgre. In all actuality it is realitively simple.


I will start by creating a simple Employee table. Then i will create a sequence to create autonumbers for my EmployeeID column (Same as M$SQL IDENTITY(1,1). After which i will create a function that will insert my defined variables into the tblemployees table.


lets start:

[color red]CREATE TABLE tblemployees (
empID smallint not null,
empssn char(11),
firstname varchar(20),
lastname varchar(30),
PRIMARY KEY (empID)
)[/color]

Next is the sequence generator for our autonumber...

[color red]CREATE SEQUENCE seq_tblemployees INCREMENT 1 MINVALUE 1 MAXVALUE 1000 CACHE 1;
SELECT setval('seq_tblemployees', 100);[/color]

KEEP READING--

Next is to create a function that will simulate our beloved M$SQL Stored Procedures.

[color red]CREATE FUNCTION fn_addemp(varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar) RETURNS void AS '

insert into tblemployees values (nextval(''seq_tblemployees''), $1, $2, $3, $4, $5, $6, $7,$8);

' LANGUAGE 'sql' VOLATILE;[/color]

Simple Break Down--

Create Function fn_addemp(varchar, etc...)

This preps the function by telling it that i will be passing it 8 variables of the type varchar.

RETURNS void as '

This tell the function that I do not want any values returned (which i don't since this is a simple insert statement).

Next is your ANSI SQL standard INSERT command.

insert into tblemployees values (nextval(''seq_tblemployees''), $1, $2, $3, $4, $5, $6, $7,$8);

The (nextval('seq_tblemployees') tells the function to call the sequence I defined earlier which will add 1 to the value of the last insert.

The $1, $2, $3, $4 etc... are Postgre varible definitions.

these hold space for variables of type varchar that i will be passing from my application.


And that's all...

I tested that above example with VB.NET

To call the function simply type:

[color red] SELECT fn_addemp('add','your',info','like','this')[/color]

Please keep in mind that i am a newbee and may not have the terminology exact. I am simply giving a starting point to the wonderful world of Postgre

I hope this helps...

NHNYJETSFAN

GO JETS!!!!