Postgres Pro
Downloads
Home   >   mailing lists

Thread: cursors and for loops?

cursors and for loops?

From
"Dennis"
Date:
Hello, 

I am wondering if I can use a cursor in a for loop. I haven't been able to 
get it to work. I am just beginning plpgsql and I am struggling here. 

I am trying to do this: 


create or replace function ttest(varchar)
RETURNS varchar AS '
DECLAREparId    ALIAS FOR $1;      dennis varchar;      tmp_xvalues RECORD; 
attrVals        CURSOR (thePar varchar)    IS select '' '' || name ||''="''|| value ||''"'' as rval        from attbl
whereidcol = thePar;
 
BEGIN 
OPEN attrVals(parId); 
      for tmp_xvalues in fetch all from attrVals loop          dennis := dennis || tmp_xvalues.rval;      end loop; 
return dennis;
END;
' language plpgsql; 

If I try to use this function like so: 

select ttest('blah') 

I get:
Error: ERROR:  syntax error at or near "all" at character 15 

I guess I am confused what I can put in "for var in <expression> loop" and 
what "fetch next" or "fetch all" evaluates to. 

Dennis
pg-user@calico-consulting.com

Re: cursors and for loops?

From
Tom Lane
Date:
"Dennis" <pg-user@calico-consulting.com> writes:
> I am wondering if I can use a cursor in a for loop.

Something like
LOOP    FETCH ...;    EXIT WHEN NOT found;    ...END LOOP;

should do it.
        regards, tom lane

Re: cursors and for loops?

From
"Dennis"
Date:
Tom Lane writes: 

> Something like 
> 
>     LOOP
>         FETCH ...;
>         EXIT WHEN NOT found;
>         ...
>     END LOOP;

Thank you! I tried finding documentation on "found" in this context and 
didn't come up with anything. Can you point me to where it is documented? 

Also, I am not getting the results I think I should be getting. Is there any 
kind of debug setting, or if not that, a way to output text (i.e. printf) 
from plpgsql? 

Thanks, 

Dennis
pg-user@calico-consulting.com

Re: cursors and for loops?

From
Richard Huxton
Date:
On Sunday 11 April 2004 19:46, Dennis wrote:
> Tom Lane writes:
> > Something like
> >
> >     LOOP
> >         FETCH ...;
> >         EXIT WHEN NOT found;
> >         ...
> >     END LOOP;
>
> Thank you! I tried finding documentation on "found" in this context and
> didn't come up with anything. Can you point me to where it is documented?

In my 7.3.x docs, at the end "Basic statements" section of the "plpgsql" 
chapter. There is only one sentence on it.

> Also, I am not getting the results I think I should be getting. Is there
> any kind of debug setting, or if not that, a way to output text (i.e.
> printf) from plpgsql?

RAISE NOTICE ''var1 = %, var2 = %'', var1, var2;

Note - you need this format, you can't mix and match expressions etc.

--  Richard Huxton Archonet Ltd

Re: cursors and for loops?

From
Jan Wieck
Date:
Just FYI, recent versions of PG use cursors internally for PL/pgSQL FOR 
loops. So there is no danger for a procedure to run out of memory when 
looping over a huge result set ... at least not because of that.


Jan

Dennis wrote:

> Tom Lane writes: 
> 
>> Something like 
>> 
>>     LOOP
>>         FETCH ...;
>>         EXIT WHEN NOT found;
>>         ...
>>     END LOOP;
> 
> Thank you! I tried finding documentation on "found" in this context and 
> didn't come up with anything. Can you point me to where it is documented? 
> 
> Also, I am not getting the results I think I should be getting. Is there any 
> kind of debug setting, or if not that, a way to output text (i.e. printf) 
> from plpgsql? 
> 
> Thanks, 
> 
> Dennis
> pg-user@calico-consulting.com
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #