Declaring cursors in pl/pgsql and using with FOR/LOOP

From: "Fernando Papa" <fpapa(at)claxson(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Declaring cursors in pl/pgsql and using with FOR/LOOP
Date: 2003-05-15 16:22:29
Message-ID: F1DC5B511E2D1C499E5E20FC6D74160D02BA1834@exch2000.buehuergo.corp.claxson.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Browse pgsql-general by date

  From Date Subject
Next Message nolan 2003-05-15 16:55:43 Re: sql and timestamp variable
Previous Message scott.marlowe 2003-05-15 16:08:32 Re: ERROR: Memory exhausted in AllocSetAlloc(188)