From: Richard Huxton <dev@archonet.com>
To: david williams <dw_remote@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Returning a reference to a cursor from a function
Date: Wed, 18 Sep 2002 11:21:25 +0100
Message-ID: <200209181121.25213.dev@archonet.com> (raw)
In-Reply-To: <DAV17zROg8yJblQmlEb0000da54@hotmail.com>
References: <DAV17zROg8yJblQmlEb0000da54@hotmail.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
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
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 from
it. An example was missed out from the 7.2.1 docs but you can see one in the
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
and defines a cursor for you.
Note that when using it, you need to be within BEGIN...COMMIT (an explicit
transaction) since the cursor returned from the function only lasts until the
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=> BEGIN;
BEGIN
richardh=> SELECT foo_count('fake_cursor','companies');
NOTICE: cursor on table: companies
foo_count
-------------
fake_cursor
(1 row)
richardh=> FETCH 3 FROM fake_cursor;
co_id | co_name | co_postcode | co_lastchg
-------+--------------------------+-------------+-------------------------------
56 | Acme Associates GmBH | unknown | 2002-06-12
14:04:43.123408+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=> COMMIT;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, dw_remote@hotmail.com
Subject: Re: Returning a reference to a cursor from a function
In-Reply-To: <200209181121.25213.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox