Declaring cursors in pl/pgsql and using with FOR/LOOP - Mailing list pgsql-general

From Fernando Papa
Subject Declaring cursors in pl/pgsql and using with FOR/LOOP
Date
Msg-id F1DC5B511E2D1C499E5E20FC6D74160D02BA1834@exch2000.buehuergo.corp.claxson.com
Whole thread Raw
List pgsql-general
Hi everybody!

I have a lot of defined cursor in one function, so it's more clear for
the code to declare those cursors like this in the DECLARE section:

[...]
curr_spc CURSOR FOR SELECT cat.id_sbc from cont_sbc, cat where
cont_sbc.id_cat = cat.id_cat;
row_spc  RECORD;
curr_cat CURSOR FOR SELECT cat.id_cat from cont_sbc, cat where
cont_sbc.id_cat = cat.id_cat;
row_cat  RECORD;
[...]

So, now I trying to loop through these cursors, and I do this:

[...]
OPEN curr_spc;
fetch curr_spc INTO row_spc;
WHILE FOUND = true LOOP
 (blablabla...)
 fetch curr_spc INTO row_spc;
END LOOP;
[...]

Everything go ok with this... but now I want to do the same thing with
FOR Loop, but I can't do it.

I try with this:

FOR row_spc IN curr_spc LOOP
 (blablabla...)
END LOOP;

And this (with the open sentence at first):

OPEN curr_spc;
FOR row_spc IN curr_spc LOOP
 (blablabla...)
END LOOP;

But in both cases I get:
ERROR during compile near line...
parse error at or neat "curr_spc".

Can I use declared cursors (in the DECLARE section) with FOR loops?
Actually I have more than 20 cursors declaration with big SQL sentences
so it's a real pain to put the "select" sentences in the middle of
FOR..LOOP.

Thanks in advance!

--
Fernando O. Papa
DBA

pgsql-general by date:

Previous
From: "scott.marlowe"
Date:
Subject: Re: ERROR: Memory exhausted in AllocSetAlloc(188)
Next
From: nolan@celery.tssi.com
Date:
Subject: Re: sql and timestamp variable