Обсуждение: 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