Re: cursors from pl/pgsql

On Tue, Apr 6, 2010 at 9:58 PM, Marc Menem <marc(dot)menem(at)m4x(dot)org> wrote:
> Hi all,
>
> I'm trying to use a cursor returned by a function from another function. But
> I can't seem to get it working correctly. The error message is:
>   ERROR:  cursor FOR loop must use a bound cursor variable
> I am not sure how to bind it;

refcursor handles must be accesed via the fetch mechanism as pavel noted.

the three basic ways to pass sets between pl/pgsql functions are:
*) cursors
*) temp tables
*) arrays

cursors I find to be the most limiting and rarely use. I just don't
like the iterative style of coding they push you into. Also the
'fetch' syntax is IMNSHO, completely orthogonal to regular dml and
stupid (this is not postgres's fault, but the sql language).

temp tables are the most flexible but can also be a headache. you have
to work around the fact the temp table is not truly a function local
variable and does not clean up with the function's scope. temp tables
also have annoying interactions with the function plan cache.

arrays require a defined type and a fairly new (8.3 minimum) postgres,
aren't really suitable for anything but fairly small sets (say <=10k
rows), but can truly be passed around and manipulated as variables,
and die with the function. when dealing with problems of this nature,
I tend to pull the data in chunks, hold in array, and pass around,
using 8.4 unnest to give me sql dml semantics when I need them.

merlin