cursor already in use error

Sim Zacks · *nix forums addict Joined: 02 Mar 2005 Posts: 97

PostGreSQL 8.0beta1
I have a function that uses a cursor and it is giving me the error: cursor
"crsr" already in use when the parameters I pass in come from another table.

The function works fine when I call it by itself, such as select
PartNeedsReschedule(100,1) or select * from PartNeedsReschedule(100,1)
but when I try select PartNeedsReschedule(PartID,1) from Parts then I get
the error.

It seems that when I qualify the query such as PartNeedsReschedule(PartID,1)
from Parts where partid=100
then it works because it is only returning one row.

Is this a known problem with cursor based functions that they can not be
called by set based queries?
Is there a workaround?

Thank You
Sim

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Michael Fuhr · *nix forums Guru Joined: 02 Mar 2005 Posts: 674

On Wed, Mar 02, 2005 at 09:21:44AM +0200, Sim Zacks wrote:

Sim Zacks · *nix forums addict Joined: 02 Mar 2005 Posts: 97

I haven't tried 8.0.1 yet. I am planning on making the upgrade soon, but
since I haven't seen this issue discussed I did not think it had been fixed
just because I am using an older version. I did search the Internet and
found one person who had the same issue and found a workaround that won't
apply to my situation.
Here is an example of my problem:

create table test( TestID int primary key, name varchar);
insert into test(TestID,name) values(1,'Bob');
insert into test(testid,name) values(2,'Mark');
insert into test(testid,name) values(3,'Tom');

create table test2(Test2ID serial primary key, TestID int, comments
varchar);
insert into test2(TestID,Comments) values(1,'Comment 1');
insert into test2(TestID,Comments) values(1,'Comment 2');
insert into test2(TestID,Comments) values(1,'Comment 3');
insert into test2(TestID,Comments) values(2,'Comment 1');

create or replace function testcursor(thistestid int) returns varchar as
$$
declare
crs Cursor for select comments from test a join test2 b on
a.testid=b.testid where a.TestID=thistestid;
thiscomment varchar;
totalstr varchar;
begin
open crs;
fetch crs into thiscomment;
totalstr:='';
while found loop
totalstr:= totalstr || '-' || thiscomment;
fetch crs into thiscomment;
end loop;
return totalstr;
end;
$$language 'plpgsql';

select name,testcursor(testid) from test; --doesn't work
select name,testcursor(testid) from test where testid=1; -- works (as does
testid=2 or 3)

Obviously in this example I could use a for loop which would remove the
problem, but in my real life complicated example, I cannot use a for loop
and I am stuck using a cursor. My real life example uses the cursor inside
of a for loop such that it opens the cursor before the loop starts and then
based on criteria inside the loop sometimes it reads the values and fetches
the next value and sometimes it doesn't.

Thank You
Sim

"Michael Fuhr" <mike@fuhr.org> wrote in message
news:20050302080327.GA47806@winnie.fuhr.org...

Michael Fuhr · *nix forums Guru Joined: 02 Mar 2005 Posts: 674

On Wed, Mar 02, 2005 at 11:15:52AM +0200, Sim Zacks wrote:

Alban Hertroys · *nix forums Guru Wannabe Joined: 02 Mar 2005 Posts: 119

Sim Zacks wrote:

Sim Zacks · *nix forums addict Joined: 02 Mar 2005 Posts: 97

Sim Zacks · *nix forums addict Joined: 02 Mar 2005 Posts: 97

Google