Re: Creating Stored Procedures

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" <spamfrommailing(at)freax(dot)org>
To: <pgsql-sql(at)postgresql(dot)org>
Cc: <pgsql-cygwin(at)postgresql(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>