Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: "david williams" <dw_remote@hotmail.com>
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: <DAV17zROg8yJblQmlEb0000da54@hotmail.com>
In-Reply-To: <DAV17zROg8yJblQmlEb0000da54@hotmail.com>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200209181121.25213.dev@archonet.com>

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;