--------------

CREATE FUNCTION getallusers() RETURN in= teger AS'

DECLARE

Liahona CURSOR F= OR Select * from users;

BEGIN

FETCH= FORWARD ALL IN Liahona;

CLOSE Liahona;

END;

'language 'plpgsql';

----= -----------

Followed by 'select getallusers();' for testing purp= oses.

I get the error:

ERROR during compile of getallusers near line 5

Parse erro= r at or near "FORWARD"

I have tried creating R= ECORD type cursors but no luck.

Can anyone sug= gest how to do this.

I was referenced to this = this through my discussion at dbforums.com.

http://dbforums.com/t499825.html=

Thanks

!--------------------------------= -----!
David Williams

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

------=_NextPart_001_0002_01C25A66.729533E0-- From dev@archonet.com Tue Sep 17 15:44:50 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 177704760CC for ; Tue, 17 Sep 2002 11:44:50 -0400 (EDT) Received: from anchor-post-33.mail.demon.net (anchor-post-33.mail.demon.net [194.217.242.91]) by postgresql.org (Postfix) with ESMTP id 5D15C476048 for ; Tue, 17 Sep 2002 11:44:49 -0400 (EDT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-33.mail.demon.net with esmtp (Exim 3.35 #1) id 17rKWz-0000vN-0X; Tue, 17 Sep 2002 16:44:54 +0100 Received: from client.archonet.com (unknown [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id 7C2A916798; Tue, 17 Sep 2002 16:31:00 +0100 (BST) Content-Type: text/plain; charset="iso-8859-1" From: Richard Huxton Organization: Archonet Ltd To: "david williams" , Subject: Re: Returning a reference to a cursor from a function Date: Tue, 17 Sep 2002 16:29:56 +0100 User-Agent: KMail/1.4.1 References: In-Reply-To: MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200209171629.57041.dev@archonet.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/195 X-Sequence-Number: 9472 On Thursday 12 Sep 2002 7:12 pm, david williams wrote: > To anyone who can help me, > > I am new at Postgresql and am having some problems. > I went a stage further attempt to put this query into a function as such > CREATE FUNCTION getallusers() RETURN integer AS' > DECLARE > Liahona CURSOR FOR Select * from users; > BEGIN > > FETCH FORWARD ALL IN Liahona; > > CLOSE Liahona; > END; > 'language 'plpgsql'; Number of issues here - it's RETURNS on the first line, you haven't OPENed = the=20 cursor and I don't think you can use that form of FETCH in the function. Lo= ok=20 at the Programmers Guide, ch 23.7 "Cursors" for details on how to do it. To get you going, here's one that returns a count of how many records were= =20 fetched from an indicated table. -- BEGIN function -- DROP FUNCTION foo_count(text); CREATE FUNCTION foo_count(text) RETURNS integer AS ' DECLARE my_name ALIAS FOR $1; csr1 refcursor; dummy RECORD; n int4; BEGIN n:=3D0; RAISE NOTICE ''counting table: %'',my_name; OPEN csr1 FOR EXECUTE ''SELECT * FROM '' || my_name; FETCH csr1 INTO dummy; WHILE (FOUND) LOOP n:=3Dn+1; FETCH csr1 INTO dummy; END LOOP; CLOSE csr1; RETURN n; END; 'language 'plpgsql'; -- END function -- Put this in a text-file and use \i filename from psql to import the definit= ion=20 - makes it easier to debug. From dev@archonet.com Wed Sep 18 10:29:53 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C4C1C47658C for ; Wed, 18 Sep 2002 06:29:53 -0400 (EDT) Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by postgresql.org (Postfix) with ESMTP id 10AB34765A0 for ; Wed, 18 Sep 2002 06:29:53 -0400 (EDT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-32.mail.demon.net with esmtp (Exim 3.35 #1) id 17rc5i-0008pO-0W; Wed, 18 Sep 2002 11:29:54 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 3851016B59; Wed, 18 Sep 2002 11:22:28 +0100 (BST) Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id 74B8F16B58; Wed, 18 Sep 2002 11:22:27 +0100 (BST) Content-Type: text/plain; charset="iso-8859-1" From: Richard Huxton Organization: Archonet Ltd To: "david williams" Subject: Re: Returning a reference to a cursor from a function Date: Wed, 18 Sep 2002 11:21:25 +0100 User-Agent: KMail/1.4.1 References: In-Reply-To: Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200209181121.25213.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/202 X-Sequence-Number: 9479 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 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 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 it. An example was missed out from the 7.2.1 docs but you can see one in th= e=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 and defines a cursor for you. Note that when using it, you need to be within BEGIN...COMMIT (an explicit= =20 transaction) since the cursor returned from the function only lasts until t= he=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 14:04:43.123408+01 57 | Imperial Investments Inc | unknown | 2002-06-12=20 14:04:43.123408+01 58 | Universal Associates USA | unknown | 2002-06-12=20 14:04:43.123408+01 (3 rows) richardh=3D> COMMIT; From dw_remote@hotmail.com Wed Sep 18 11:23:17 2002 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