Stored procedures vs Functions

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