Creating Stored Procedures - Mailing list pgsql-sql

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.