pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReturning a reference to a cursor from a function
4+ messages / 2 participants
[nested] [flat]
* Returning a reference to a cursor from a function
@ 2002-09-12 18:12 david williams <dw_remote@hotmail.com>
2002-09-17 15:29 ` Re: Returning a reference to a cursor from a function Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 4+ messages in thread
From: david williams @ 2002-09-12 18:12 UTC (permalink / raw)
To: pgsql-sql
To anyone who can help me,
I am new at Postgresql and am having some problems.
I am trying to return a rowset from a function to the calling procedure.
Outside a function I have gotten this to work at the psql level.
-------------
BEGIN Work;
DECLARE Liahona CURSOR FOR Select * from users;
FETCH FORWARD ALL IN Liahona;
CLOSE Liahona;
COMMIT Work;
--------------
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';
---------------
Followed by 'select getallusers();' for testing purposes.
I get the error:
ERROR during compile of getallusers near line 5
Parse error at or near "FORWARD"
I have tried creating RECORD type cursors but no luck.
Can anyone suggest how to do this.
I was referenced to this this through my discussion at dbforums.com.
http://dbforums.com/t499825.html
Thanks
!-------------------------------------!
David WilliamsGet more from the Web. FREE MSN Explorer download : http://explorer.msn.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Returning a reference to a cursor from a function
2002-09-12 18:12 Returning a reference to a cursor from a function david williams <dw_remote@hotmail.com>
@ 2002-09-17 15:29 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2002-09-17 15:29 UTC (permalink / raw)
To: david williams <dw_remote@hotmail.com>; pgsql-sql
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
cursor and I don't think you can use that form of FETCH in the function. Look
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
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:=0;
RAISE NOTICE ''counting table: %'',my_name;
OPEN csr1 FOR EXECUTE ''SELECT * FROM '' || my_name;
FETCH csr1 INTO dummy;
WHILE (FOUND) LOOP
n:=n+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 definition
- makes it easier to debug.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Returning a reference to a cursor from a function
@ 2002-09-18 10:21 Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2002-09-18 10:21 UTC (permalink / raw)
To: david williams <dw_remote@hotmail.com>; +Cc: pgsql-sql
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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Returning a reference to a cursor from a function
@ 2002-09-18 11:23 david williams <dw_remote@hotmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: david williams @ 2002-09-18 11:23 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
Thanks again Richard.
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 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 create 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 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 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;
---------------------------(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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-09-18 11:23 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-12 18:12 Returning a reference to a cursor from a function david williams <dw_remote@hotmail.com>
2002-09-17 15:29 ` Richard Huxton <dev@archonet.com>
2002-09-18 10:21 Re: Returning a reference to a cursor from a function Richard Huxton <dev@archonet.com>
2002-09-18 11:23 Re: Returning a reference to a cursor from a function david williams <dw_remote@hotmail.com>
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