Thread: Creating Stored Procedures
Hi there, We are developing a large application which uses up to 500 Stored Procedures. Because we need a good but cheap database for when we sell our application to customers we decided to try porting our applications Database Management System to PostgreSQL. Our application is writting in .NET and uses ADO.NET, odbc for accessing the database. We have already ported our DBMS from MS SQL to Oracle in which we succeeded. So the next major step is the port to PostgreSQL and the PL/SQL to PLpg/SQL or SQL procedural language. Because we want to hide our database stuff from our business logics we decided to make use of Stored Procedures. I have already noticed that PostgreSQL only knows about Functions. The support for Stored Procedures can done by using functions. Am I right on this? The problem that I am having is that I have not find a way to return a Tuple or a RecordSet. Our Stored Procedures return for example the result of 'SELECT * FROM TABLE_X'. Our application uses a SqlConnection and a SqlDataAdaptor to fill a DataView (that last information is very .NET specific, I know. This might indeed be a .NET question to but I hope that, because we all are professionals in here, this will not create a "I like this Programming Language more then your stupid .NET" flamewar). We dislike changing sources and prefer changing/manipulating Stored Procedures or Functions so that the sources need less or no changes at all. Some people have advised us to start using Views instead of Stored Procedures. But that would mean to much SourceCcode changes. At this moment we have a source that works pretty good. We are satisfied with the speed and performance. So only PostgreSQL cannot be a good reason for us to redesign a lot of the Database Issues (Remember that we have +500 of such Stored Procedures to convert and not VERY much time to do this port -we have one month, thats it-). Is there a PostgreSQL version that can do Stored Procedures and return Tuples or RecordSets like MSSQL and Oracle can? And/or is there a way to create a function that returns a Tuple and/or a RecordSet that we can use in .NET (convert the result to a DataView)? Regretfully our commercial guys are not very pro Free Software products. They would prefer using MSDE as primary 'cheap' DBMS. We, the developers, dislike MSDE because then we are still stuck in the Microsoft MS SQL world. And there will be no way to get out of it. Also is MSDE not very usable for more then three users and will MSDE make our customer pay a lot for MS SQL when he or she wants to upgrade to more users -> that sucks. Maybe the use of Stored Procedures that return RecordSets actually 'was' a design problem of ours but we have now reached a point that we cannot go back and start changing such stuff ... The person who pays us would not like it I fear :-). So how much you guys would like us to start using Views, it will probably not happen then. I guess, if that would be the only option, MSDE will be used; period. :-) Which would suck... *ahum* Note that I have searched A LOT on google about this subject and I do know that this probably is a FAQ. But I also have not yet found any answer that we can actually use :). We have no problem with using beta versions. There is one requirement with betas : the version must run on both Linux and Windows NT (using CygWin if that is required, is no problem for us). ps. If I am posting to the wrong mailinglist or persons, feel free to send me pointers to the right mailinglist. -- Philip van Hoof aka freax (http://www.freax.eu.org) irc: irc.openprojects.net mailto:me at freax dot org Go not to the Elves for counsel, for they will say both no and yes.
On 29 Oct 2002, Philip Van Hoof wrote: > Note that I have searched A LOT on google about this subject and I do > know that this probably is a FAQ. But I also have not yet found any > answer that we can actually use :). We have no problem with using beta > versions. There is one requirement with betas : the version must run on > both Linux and Windows NT (using CygWin if that is required, is no > problem for us). You might want to check out 7.3beta3 since it has support for functions that return datasets. Since you don't want to have any source changes, I'm not sure if the interface to such is the same as in the other two dbs mentioned. It should run with cygwin as well with a little bit of work (see recent messages on hackers)
As far as I know functions can not return recordsets. Try functions returning cursors (then FETCH ALL IN..), or temporarytables. regards Joseph ----- Original Message ----- From: "Philip Van Hoof" <spamfrommailing@freax.org> To: <pgsql-sql@postgresql.org> Cc: <pgsql-cygwin@postgresql.org> Sent: Tuesday, October 29, 2002 7:23 PM Subject: [SQL] Creating Stored Procedures > > > Hi there, > > We are developing a large application which uses up to 500 Stored > Procedures. Because we need a good but cheap database for when we sell > our application to customers we decided to try porting our applications > Database Management System to PostgreSQL. > > Our application is writting in .NET and uses ADO.NET, odbc for accessing > the database. We have already ported our DBMS from MS SQL to Oracle in > which we succeeded. So the next major step is the port to PostgreSQL and > the PL/SQL to PLpg/SQL or SQL procedural language. > > Because we want to hide our database stuff from our business logics we > decided to make use of Stored Procedures. I have already noticed that > PostgreSQL only knows about Functions. The support for Stored Procedures > can done by using functions. Am I right on this? > > The problem that I am having is that I have not find a way to return a > Tuple or a RecordSet. Our Stored Procedures return for example the > result of 'SELECT * FROM TABLE_X'. Our application uses a SqlConnection > and a SqlDataAdaptor to fill a DataView (that last information is very > .NET specific, I know. This might indeed be a .NET question to but I > hope that, because we all are professionals in here, this will not > create a "I like this Programming Language more then your stupid .NET" > flamewar). We dislike changing sources and prefer changing/manipulating > Stored Procedures or Functions so that the sources need less or no > changes at all. > > Some people have advised us to start using Views instead of Stored > Procedures. But that would mean to much SourceCcode changes. At this > moment we have a source that works pretty good. We are satisfied with > the speed and performance. So only PostgreSQL cannot be a good reason > for us to redesign a lot of the Database Issues (Remember that we have > +500 of such Stored Procedures to convert and not VERY much time to do > this port -we have one month, thats it-). > > Is there a PostgreSQL version that can do Stored Procedures and return > Tuples or RecordSets like MSSQL and Oracle can? And/or is there a way to > create a function that returns a Tuple and/or a RecordSet that we can > use in .NET (convert the result to a DataView)? > > Regretfully our commercial guys are not very pro Free Software products. > They would prefer using MSDE as primary 'cheap' DBMS. We, the > developers, dislike MSDE because then we are still stuck in the > Microsoft MS SQL world. And there will be no way to get out of it. Also > is MSDE not very usable for more then three users and will MSDE make our > customer pay a lot for MS SQL when he or she wants to upgrade to more > users -> that sucks. Maybe the use of Stored Procedures that return > RecordSets actually 'was' a design problem of ours but we have now > reached a point that we cannot go back and start changing such stuff ... > The person who pays us would not like it I fear :-). So how much you > guys would like us to start using Views, it will probably not happen > then. I guess, if that would be the only option, MSDE will be used; > period. :-) Which would suck... *ahum* > > Note that I have searched A LOT on google about this subject and I do > know that this probably is a FAQ. But I also have not yet found any > answer that we can actually use :). We have no problem with using beta > versions. There is one requirement with betas : the version must run on > both Linux and Windows NT (using CygWin if that is required, is no > problem for us). > > > ps. If I am posting to the wrong mailinglist or persons, feel free to > send me pointers to the right mailinglist. > > > > -- > Philip van Hoof aka freax (http://www.freax.eu.org) > irc: irc.openprojects.net mailto:me at freax dot org > Go not to the Elves for counsel, for they will say both no and yes. > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
> From: Stephan Szabo <sszabo@megazone23.bigpanda.com> > To: Philip Van Hoof <spamfrommailing@freax.org> > Cc: pgsql-sql@postgresql.org, pgsql-cygwin@postgresql.org > Subject: Re: [SQL] Creating Stored Procedures > Date: 29 Oct 2002 10:36:17 -0800 > > > On 29 Oct 2002, Philip Van Hoof wrote: > > > Note that I have searched A LOT on google about this subject and I do > > know that this probably is a FAQ. But I also have not yet found any > > answer that we can actually use :). We have no problem with using beta > > versions. There is one requirement with betas : the version must run on > > both Linux and Windows NT (using CygWin if that is required, is no > > problem for us). > > You might want to check out 7.3beta3 since it has support for functions > that return datasets. Since you don't want to have any source changes, > I'm not sure if the interface to such is the same as in the other two dbs > mentioned. It should run with cygwin as well with a little bit of work > (see recent messages on hackers) > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > I installed 7.3beta3 but how do I do it now? How do I make a function that returns a dataset Greets Zertox
> I installed 7.3beta3 but how do I do it now? > How do I make a function that returns a dataset > > Greets > Zertox He is an email I received from Joe Conway on the subject a month or so ago > Adam Witney wrote: >> There have been a few emails recently concerning using functions. However I >> am a little confused as to their use with composite types. I can see how to >> return a whole row from a table, but is it possible to return multiple >> fields that do not originate from the same table? > > Sure. But you either need a named composite type that matches the row you want > to return, or you can use a record datatype and specify the column definitions > in the sql statement at run time. > > A composite type exists for each table and view in your database, as well as > any stand-alone composite types you define. So, for example: > > test=# create table foo (f1 int,f2 text); > CREATE TABLE > test=# create table bar (f3 int,f4 text); > CREATE TABLE > test=# create view foobar as select f1,f2,f4 from foo, bar where f1=f3; > CREATE VIEW > test=# insert into foo values(1,'a'); > INSERT 1105496 1 > test=# insert into foo values(2,'b'); > INSERT 1105497 1 > test=# insert into bar values(1,'c'); > INSERT 1105498 1 > test=# insert into bar values(2,'d'); > INSERT 1105499 1 > > -- This uses a named composite type based on the view > test=# create function getfoobar1() returns setof foobar as 'select f1,f2,f4 > from foo, bar where f1=f3' language sql; > CREATE FUNCTION > test=# select * from getfoobar1(); > f1 | f2 | f4 > ----+----+---- > 1 | a | c > 2 | b | d > (2 rows) > > -- This uses an anonymous composite type specified at runtime > test=# create function getfoobar2() returns setof record as 'select f1,f2,f4 > from foo, bar where f1=f3' language sql; > CREATE FUNCTION > test=# select * from getfoobar2() as (f1 int,f2 text,f4 text); > f1 | f2 | f4 > ----+----+---- > 1 | a | c > 2 | b | d > (2 rows) > > HTH, > > Joe > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.