pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcursors and for loops?
5+ messages / 4 participants
[nested] [flat]
* cursors and for loops?
@ 2004-04-11 04:59 Dennis <pg-user@calico-consulting.com>
0 siblings, 1 reply; 5+ messages in thread
From: Dennis @ 2004-04-11 04:59 UTC (permalink / raw)
To: pgsql-sql
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 '
DECLARE
parId ALIAS FOR $1;
dennis varchar;
tmp_xvalues RECORD;
attrVals CURSOR (thePar varchar)
IS select '' '' || name ||''="''|| value ||''"'' as rval
from attbl where idcol = 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: cursors and for loops?
@ 2004-04-11 16:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dennis <pg-user@calico-consulting.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2004-04-11 16:00 UTC (permalink / raw)
To: Dennis <pg-user@calico-consulting.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: cursors and for loops?
@ 2004-04-11 18:46 Dennis <pg-user@calico-consulting.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Dennis @ 2004-04-11 18:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: cursors and for loops?
@ 2004-04-13 08:31 Richard Huxton <dev@archonet.com>
parent: Dennis <pg-user@calico-consulting.com>
1 sibling, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2004-04-13 08:31 UTC (permalink / raw)
To: Dennis <pg-user@calico-consulting.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: cursors and for loops?
@ 2004-04-15 14:50 Jan Wieck <JanWieck@Yahoo.com>
parent: Dennis <pg-user@calico-consulting.com>
1 sibling, 0 replies; 5+ messages in thread
From: Jan Wieck @ 2004-04-15 14:50 UTC (permalink / raw)
To: Dennis <pg-user@calico-consulting.com>; +Cc: pgsql-sql
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 #
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-04-15 14:50 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-11 04:59 cursors and for loops? Dennis <pg-user@calico-consulting.com>
2004-04-11 16:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-11 18:46 ` Dennis <pg-user@calico-consulting.com>
2004-04-13 08:31 ` Richard Huxton <dev@archonet.com>
2004-04-15 14:50 ` Jan Wieck <JanWieck@Yahoo.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