Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B604A47613C for ; Wed, 18 Sep 2002 07:23:17 -0400 (EDT) Received: from hotmail.com (dav32.law15.hotmail.com [64.4.22.89]) by postgresql.org (Postfix) with ESMTP id 0AECB476059 for ; Wed, 18 Sep 2002 07:23:16 -0400 (EDT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Wed, 18 Sep 2002 04:23:18 -0700 X-Originating-IP: [64.231.154.250] From: "david williams" To: "Richard Huxton" Cc: Subject: Re: Returning a reference to a cursor from a function Date: Wed, 18 Sep 2002 07:23:19 -0400 MIME-Version: 1.0 X-Mailer: MSN Explorer 7.00.0021.1900 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0000_01C25EE4.431A0B90" Message-ID: X-OriginalArrivalTime: 18 Sep 2002 11:23:18.0546 (UTC) FILETIME=[C9D52720:01C25F05] X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/203 X-Sequence-Number: 9480 ------=_NextPart_001_0000_01C25EE4.431A0B90 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thanks again Richard. I did find that dev note and I do have a version of this working but of cou= rse it does not return up to the ASP layer. Since I need to integrate Postgresql ( or something else ) into an existing= application using COM as the middle and ASP as the upper layer I must crea= te a function similar to how MS SQL Server handles it. Ah well.... Thankgs again for you help and I will keep watch on 7.3. Do you know if the= ODBC Driver will also be updated to accomodate this function or is the DEC= LARE/FETCH setting enough. Thanks Dave ----- Original Message ----- From: Richard Huxton Sent: Wednesday, September 18, 2002 6:32 AM To: david williams Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Returning a reference to a cursor from a function On Tuesday 17 Sep 2002 7:12 pm, you wrote: > Richard, > > Thanks for the information. I've made some modifications to your code here > so that it does a RAISE NOTICE in each loop returning simply the value of= n > and then when the loop is finished it again returns n. > > This works fine at the psql level but after it passes through ODBC to the > ASP layer all I get is the final RETURN value. Yep - the NOTICE is really a type of error message (you can use RAISE to=20= =20 generate errors too) and isn't part of your data-stream. > I have tried using the RETURN function in the loop but it terminates the > loop. Indeed it does. > I really need to return each record up to the ASP layer. The solution to this sort of thing in version 7.3 is something called table= =20=20 functions, but I think they're limited to C at the moment, not plpgsql. With 7.2 you need to return the cursor from the function and then FETCH fro= m=20=20 it. An example was missed out from the 7.2.1 docs but you can see one in th= e=20=20 developer's docs (bottom of page): http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html To hack our example a bit more the below takes a cursor-name and table name= =20=20 and defines a cursor for you. Note that when using it, you need to be within BEGIN...COMMIT (an explicit= =20=20 transaction) since the cursor returned from the function only lasts until t= he=20=20 end of a transaction. HTH - Richard Huxton DROP FUNCTION foo_count(refcursor, text); CREATE FUNCTION foo_count(refcursor, text) RETURNS refcursor AS ' DECLARE curs ALIAS FOR $1; tbl_name ALIAS FOR $2; BEGIN RAISE NOTICE ''cursor on table: %'',tbl_name; OPEN curs FOR EXECUTE ''SELECT * FROM '' || tbl_name; RETURN curs; END; 'language 'plpgsql'; richardh=3D> BEGIN; BEGIN richardh=3D> SELECT foo_count('fake_cursor','companies'); NOTICE: cursor on table: companies foo_count ------------- fake_cursor (1 row) richardh=3D> FETCH 3 FROM fake_cursor; co_id | co_name | co_postcode | co_lastchg -------+--------------------------+-------------+--------------------------= ----- 56 | Acme Associates GmBH | unknown | 2002-06-12=20=20 14:04:43.123408+01 57 | Imperial Investments Inc | unknown | 2002-06-12=20=20 14:04:43.123408+01 58 | Universal Associates USA | unknown | 2002-06-12=20=20 14:04:43.123408+01 (3 rows) richardh=3D> COMMIT; ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)Get= more from the Web. FREE MSN Explorer download : http://explorer.msn.com ------=_NextPart_001_0000_01C25EE4.431A0B90 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Thanks again Ric= hard.
 
I did find that dev note and I do have a= version of this working but of course it does not return up to the ASP lay= er.
 
Since I need to integrate Postgresql ( or = something else ) into an existing application using COM as the middle = and ASP as the upper layer I must create a function similar to how MS SQL S= erver handles it.
 
Ah well....
 = ;
Thankgs again for you help and I will keep watch on 7.3. Do yo= u know if the ODBC Driver will also be updated to accomodate this function = or is the DECLARE/FETCH setting enough.
 
Thanks=
 
Dave
 
----= - Original Message -----
From: Richard Huxton
Sent: Wednesday, September 18, 2002 6:32 AM
To: david williams
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Returning a reference to a cursor= from a function
 
On Tuesday 17 Sep 2002 7:12 pm, you= wrote:
> Richard,
>
> Thanks for the information. I've m= ade some modifications to your code here
> so that it does a RAISE NO= TICE in each loop returning simply the value of n
> and then when the= loop is finished it again returns n.
>
> This works fine at th= e psql level but after it passes through ODBC to the
> ASP layer all = I get is the final RETURN value.

Yep - the NOTICE is really a type o= f error message (you can use RAISE to
generate errors too) and isn't pa= rt of your data-stream.

> I have tried using the RETURN function = in the loop but it terminates the
> loop.

Indeed it does.
<= BR>> I really need to return each record up to the ASP layer.

The= solution to this sort of thing in version 7.3 is something called table functions, but I think they're limited to C at the moment, not plpgsql.
With 7.2 you need to return the cursor from the function and then FET= CH from
it. An example was missed out from the 7.2.1 docs but you can s= ee one in the
developer's docs (bottom of page):
http://developer.po= stgresql.org/docs/postgres/plpgsql-cursors.html

To hack our example = a bit more the below takes a cursor-name and table name
and defines a c= ursor for you.
Note that when using it, you need to be within BEGIN...CO= MMIT (an explicit
transaction) since the cursor returned from the funct= ion only lasts until the
end of a transaction.

HTH

- Rich= ard Huxton

DROP FUNCTION foo_count(refcursor, text);
CREATE FUNCT= ION foo_count(refcursor, text) RETURNS refcursor AS '
DECLARE
 &= nbsp;  curs ALIAS FOR $1;
    tbl_name ALIAS FOR $2;=
BEGIN
    RAISE NOTICE ''cursor on table: %'',tbl_nam= e;
    OPEN curs FOR EXECUTE ''SELECT * FROM '' || tbl_na= me;
    RETURN curs;
END;
'language 'plpgsql';
<= BR>richardh=3D> BEGIN;
BEGIN
richardh=3D> SELECT foo_count('fak= e_cursor','companies');
NOTICE:  cursor on table: companies
&nbs= p; foo_count
-------------
fake_cursor
(1 row)

richardh=3D&= gt; FETCH 3 FROM fake_cursor;
co_id |      = ;   co_name         = | co_postcode |          co_l= astchg
-------+--------------------------+-------------+----------------= ---------------
    56 | Acme Associates GmBH  =    | unknown     | 2002-06-12
14:04:43.12= 3408+01
    57 | Imperial Investments Inc | unknown =     | 2002-06-12
14:04:43.123408+01
   = ; 58 | Universal Associates USA | unknown     | 2002-06= -12
14:04:43.123408+01
(3 rows)

richardh=3D> COMMIT;
---------------------------(end of broadcast)---------------------------<= BR>TIP 2: you can get off all lists at once with the unregister command
=     (send "unregister YourEmailAddressHere" to majordomo@pos= tgresql.org)

Get more fro= m the Web. FREE MSN Explorer download : http://explorer.msn.com

------=_NextPart_001_0000_01C25EE4.431A0B90--