Stored Procedures in Postgre 1

[nhjyjetsfan]

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:

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

Next is the sequence generator for our autonumber...

CREATE SEQUENCE seq_tblemployees INCREMENT 1 MINVALUE 1 MAXVALUE 1000 CACHE 1;
SELECT setval('seq_tblemployees', 100);

KEEP READING--

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

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;

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!!!!

 

[nhjyjetsfan]

ooops! there is a type near the end where it states:

[COLOR=red SELECT fn_addemp('add','your','info','like,'this')

should read:

[b]SELECT fn_addemp('add','your','info','like,'this');[/b]

sorry it is 2:47 AM and i am quite tired...

GO JETS!!!

 

[Leland123]

Hi nhjyjetsfan,

You should click on the FAQs tab and add this to the postgres frequently asked questions.

LelandJ
Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox

 

[newbiepg]

you could add this part
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'