Re: cursor already in use error - Mailing list pgsql-general

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...
> On Wed, Mar 02, 2005 at 09:21:44AM +0200, Sim Zacks wrote:
>
> > PostGreSQL 8.0beta1
>
> That's pretty old.  Have you tried 8.0.1?
>
> > 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.
>
> Could you post a simple, self-contained example that demonstrates
> the problem?  Please show definitions of Parts and PartNeedsReschedule
> and some sample data -- enough that somebody could copy what you
> post into a test database and duplicate the problem.  It'll be
> easier to help if we can see exactly what you're doing.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>