As far as I know functions can not return recordsets. Try functions returning cursors (then FETCH ALL IN..), or temporary tables. regards Joseph
----- Original Message ----- From: "Philip Van Hoof" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly