Postgres Pro
Downloads
Home   >   mailing lists

Thread: Stored Procedures

Stored Procedures

From
bugbug@shaw.ca
Date:
Hi Im new to postgre...and Im trying to make the leap from ms sql(was kinda new there too! )

How do I implement ms sql style stored procedures in postgre ? I have ms sql stored procedures like the following to
insertdata into a db that is called from a web form: 

CREATE PROCEDURE pr_Wellogs_Insert
 @iWELL_NUM int = NULL,
 @sOWNER nvarchar(60) = NULL,
 @siWELL_LOT smallint = NULL,
 @stFrom smallint = NULL,
 @stTO smallint = NULL

AS

INSERT WELLOGS
(
 [WELL_NUM],
 [OWNER],
 [WELL_LOT]
)
VALUES
(
 @iWELL_NUM,
 @sOWNER,
 @siWELL_LOT

if (@stFrom <> ' ')
begin insert stratigraphy3
(
 [well_num],
 [from],
 [to]
)
values
(
 @iWELL_NUM,
 @stFrom,
 @stTO
)
end
return

------
In pgadmin I cant even find anything called stored procedures. Also...how do I implement @@identity in postgre ?

Thanks!
Dennis

Re: Stored Procedures

From
Josh Berkus
Date:
Dennis,

> How do I implement ms sql style stored procedures in postgre ? I have ms sql
stored procedures like the following to insert data into a db that is called
from a web form:

Stored procedures are called "Functions" in PostgreSQL.  See "Create
Function".

> In pgadmin I cant even find anything called stored procedures. Also...how do
I implement @@identity in postgre ?

PostgreSQL has a much more powerful construct called "Sequences".  See "CREATE
SEQUENCE".

Better yet, buy a PostgreSQL intro book.   There are several, and they are all
pretty good.   You're in for a world of pain if you try to figure out
everything piecemeal from the online docs.


--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Re: Stored Procedures

From
Steve Crawford
Date:
search for "functions" and also check out "triggers" and "rules".

Cheers,
Steve


On Thursday 10 April 2003 3:38 pm, bugbug@shaw.ca wrote:
> Hi Im new to postgre...and Im trying to make the leap from ms sql(was kinda
> new there too! )
>
> How do I implement ms sql style stored procedures in postgre ? I have ms
> sql stored procedures like the following to insert data into a db that is
> called from a web form:
>
> CREATE PROCEDURE pr_Wellogs_Insert
>  @iWELL_NUM int = NULL,
>  @sOWNER nvarchar(60) = NULL,
>  @siWELL_LOT smallint = NULL,
>  @stFrom smallint = NULL,
>  @stTO smallint = NULL
>
> AS
>
> INSERT WELLOGS
> (
>  [WELL_NUM],
>  [OWNER],
>  [WELL_LOT]
> )
> VALUES
> (
>  @iWELL_NUM,
>  @sOWNER,
>  @siWELL_LOT
>
> if (@stFrom <> ' ')
> begin insert stratigraphy3
> (
>  [well_num],
>  [from],
>  [to]
> )
> values
> (
>  @iWELL_NUM,
>  @stFrom,
>  @stTO
> )
> end
> return
>
> ------
> In pgadmin I cant even find anything called stored procedures. Also...how
> do I implement @@identity in postgre ?
>
> Thanks!
> Dennis
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

Re: Stored Procedures

From
bugbug@shaw.ca
Date:
Porting my simple stored procedures to functions seems a little too daunting for me. There doesnt seem to be much
directtranslation. Stuff about having to supply a return value...I want to return records like a simple select
query...ori just want to insert a row into my table with a dozen parameters...I dont want to return anything.  

Dennis


----- Original Message -----
From: Steve Crawford <scrawford@pinpointresearch.com>
Date: Thursday, April 10, 2003 4:54 pm
Subject: Re: [NOVICE] Stored Procedures

> search for "functions" and also check out "triggers" and "rules".
>
> Cheers,
> Steve
>
>
> On Thursday 10 April 2003 3:38 pm, bugbug@shaw.ca wrote:
> > Hi Im new to postgre...and Im trying to make the leap from ms
> sql(was kinda
> > new there too! )
> >
> > How do I implement ms sql style stored procedures in postgre ? I
> have ms
> > sql stored procedures like the following to insert data into a
> db that is
> > called from a web form:
> >
> > CREATE PROCEDURE pr_Wellogs_Insert
> >  @iWELL_NUM int = NULL,
> >  @sOWNER nvarchar(60) = NULL,
> >  @siWELL_LOT smallint = NULL,
> >  @stFrom smallint = NULL,
> >  @stTO smallint = NULL
> >
> > AS
> >
> > INSERT WELLOGS
> > (
> >  [WELL_NUM],
> >  [OWNER],
> >  [WELL_LOT]
> > )
> > VALUES
> > (
> >  @iWELL_NUM,
> >  @sOWNER,
> >  @siWELL_LOT
> >
> > if (@stFrom <> ' ')
> > begin insert stratigraphy3
> > (
> >  [well_num],
> >  [from],
> >  [to]
> > )
> > values
> > (
> >  @iWELL_NUM,
> >  @stFrom,
> >  @stTO
> > )
> > end
> > return
> >
> > ------
> > In pgadmin I cant even find anything called stored procedures.
> Also...how> do I implement @@identity in postgre ?
> >
> > Thanks!
> > Dennis
> >
> >
> > ---------------------------(end of broadcast)--------------------
> -------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
>
> ---------------------------(end of broadcast)----------------------
> -----
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

Re: Stored Procedures

From
"paul butler"
Date:
Date sent:          Fri, 11 Apr 2003 15:04:34 -0600
From:               bugbug@shaw.ca
Subject:            Re: [NOVICE] Stored Procedures
To:                 Steve Crawford <scrawford@pinpointresearch.com>
Copies to:          pgsql-novice@postgresql.org

Its hard to know what you want but,
> Porting my simple stored procedures to functions seems a little too daunting for me.
If they are simple you should be able to write the functions in SQL, you can get quite far
with a few case statements

 There doesnt seem to be much direct translation.
Stuff about having to supply a return value...I want to return records like a simple select
query...

Check out views for this sort of thing

or i just want to insert a row into my table with a dozen parameters...

I dont want to return anything.
>
> Dennis

Cheers

Paul Butler
>
>
> ----- Original Message -----
> From: Steve Crawford <scrawford@pinpointresearch.com>
> Date: Thursday, April 10, 2003 4:54 pm
> Subject: Re: [NOVICE] Stored Procedures
>
> > search for "functions" and also check out "triggers" and "rules".
> >
> > Cheers,
> > Steve
> >
> >
> > On Thursday 10 April 2003 3:38 pm, bugbug@shaw.ca wrote:
> > > Hi Im new to postgre...and Im trying to make the leap from ms
> > sql(was kinda
> > > new there too! )
> > >
> > > How do I implement ms sql style stored procedures in postgre ? I
> > have ms
> > > sql stored procedures like the following to insert data into a
> > db that is
> > > called from a web form:
> > >
> > > CREATE PROCEDURE pr_Wellogs_Insert
> > >  @iWELL_NUM int = NULL,
> > >  @sOWNER nvarchar(60) = NULL,
> > >  @siWELL_LOT smallint = NULL,
> > >  @stFrom smallint = NULL,
> > >  @stTO smallint = NULL
> > >
> > > AS
> > >
> > > INSERT WELLOGS
> > > (
> > >  [WELL_NUM],
> > >  [OWNER],
> > >  [WELL_LOT]
> > > )
> > > VALUES
> > > (
> > >  @iWELL_NUM,
> > >  @sOWNER,
> > >  @siWELL_LOT
> > >
> > > if (@stFrom <> ' ')
> > > begin insert stratigraphy3
> > > (
> > >  [well_num],
> > >  [from],
> > >  [to]
> > > )
> > > values
> > > (
> > >  @iWELL_NUM,
> > >  @stFrom,
> > >  @stTO
> > > )
> > > end
> > > return
> > >
> > > ------
> > > In pgadmin I cant even find anything called stored procedures.
> > Also...how> do I implement @@identity in postgre ?
> > >
> > > Thanks!
> > > Dennis
> > >
> > >
> > > ---------------------------(end of broadcast)--------------------
> > -------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> >
> >
> > ---------------------------(end of broadcast)----------------------
> > -----
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Stored Procedures

From
Josh Berkus
Date:
Dennis,

> Porting my simple stored procedures to functions seems a little too
> daunting for me. There doesnt seem to be much direct translation.

There isn't *any* direct translation.    Transact-SQL, the language your
procedures are written in, is a DIFFERENT LANGUAGE from SQL, PL/pgSQL, and
the other languages available for PostgreSQL.   There is no direct
conversion, any more than there is between Tcl and Visual Basic.

You're either going to have to re-write your procedures, or you should
re-evaluate the cost of keeping MS SQL Server.  Or perhaps migrate to SyBase
SQLAnywhere, which is based on the same code and is more directly compatible.

--
Josh Berkus
Aglio Database Solutions
San Francisco