Thread: Stored procedures vs Functions
Tom, I've switched topics so I started a new thread, but your last comment about 7.2 made me think of a lingering question I've had. I've seen posts about stored procedures and functions not being able to return result sets, etc. In my last job I did e-commerce on a win2k platform with SQL 7.0. We used stored procedures extensively and found them to be very advantageous (especially on the occasions we need to return 2 different result sets from 2 different queries within the stored proc). My question then: are there stored procedures that execute like SQL 7.0 stored procs and if so are they functions and if so do I need to use a specific language or syntax? Thanks, Mike Shelton
On Friday 21 December 2001 12:27 pm, you wrote:
> Tom,
>
> I've switched topics so I started a new thread, but your last comment about
> 7.2 made me think of a lingering question I've had. I've seen posts about
> stored procedures and functions not being able to return result sets, etc.
>
> In my last job I did e-commerce on a win2k platform with SQL 7.0. We used
> stored procedures extensively and found them to be very advantageous
> (especially on the occasions we need to return 2 different result sets from
> 2 different queries within the stored proc). My question then: are there
> stored procedures that execute like SQL 7.0 stored procs and if so are they
> functions and if so do I need to use a specific language or syntax?
Stored procedures/functions are not able to return result sets. You might be
able to use a combination of functions and views to accomplish your task in a
similar way. Functions and stored procedures are interchangable when refering
to postgres. You can write a stored procedure in any of several built in
languages (PL == procedural language): PL/PgSQL, PL/perl, PL/python, PL/tcl;
alternatively, you can make your own language (although that involves a
considerable amount of effort).
Hope this helps,
Jeff Davis
> Stored procedures/functions are not able to return result sets. You might be > able to use a combination of functions and views to accomplish your task in a > similar way. Functions and stored procedures are interchangable when refering > to postgres. You can write a stored procedure in any of several built in > languages (PL == procedural language): PL/PgSQL, PL/perl, PL/python, PL/tcl; > alternatively, you can make your own language (although that involves a > considerable amount of effort). Some people return results in temp tables, 7.2 will allow cursors to be returned. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> Some people return results in temp tables, 7.2 will allow cursors to be
> returned.
Oh great! I missed that addition. That should help out a lot.
I was trying it out and I had no problem creating the function. However, if I
have a function that returns a reference to a cursor, how would I got about
fetching form that cursor reference? I guess my question is: with SQL, how do
I turn the cursor reference into an actual cursor from which I may fetch
records? Below is what I tried:
jdavis=# begin;
BEGIN
jdavis=# select f1();
f1
--------------------
<unnamed cursor 5>
(1 row)
jdavis=# fetch 1 from f1();
ERROR: parser: parse error at or near "("
ERROR: parser: parse error at or near "("
jdavis=# fetch 1 from (select f1());
ERROR: parser: parse error at or near "("
ERROR: parser: parse error at or near "("
jdavis=# declare cursor2 cursor f1();
ERROR: parser: parse error at or near "f1"
ERROR: parser: parse error at or near "f1"
jdavis=# declare cursor2 cursor (select f1());
ERROR: parser: parse error at or near "("
ERROR: parser: parse error at or near "("
jdavis=#
Thanks!
Jeff
Jeff Davis <list-pgsql-general@dynworks.com> writes:
> I was trying it out and I had no problem creating the
> function. However, if I have a function that returns a reference to a
> cursor, how would I got about fetching form that cursor reference? I
> guess my question is: with SQL, how do I turn the cursor reference
> into an actual cursor from which I may fetch records?
I don't think you can do it in pure SQL; the facility is really designed
for passing back a cursor name to another plpgsql function, or an
application that is able to substitute the returned cursor name into
a query. Given
> jdavis=# begin;
> BEGIN
> jdavis=# select f1();
> f1
> --------------------
> <unnamed cursor 5>
> (1 row)
you need to do
fetch 1 from "<unnamed cursor 5>";
psql has a primitive variable-substitution facility, but I don't think
that's quite smart enough to handle this. It'd be easy enough in almost
any application programming language, however.
regards, tom lane