Re: 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(at)candle(dot)pha(dot)pa(dot)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(at)dynworks(dot)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