Обсуждение: Stored Procedures
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
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
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
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 >
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
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