×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

Example of a Postgre Stored Procedure by nhjyjetsfan
Posted: 6 Apr 03

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:

SELECT fn_addemp('add','your',info','like','this')

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

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