Re: cursors from pl/pgsql

hi guys,

i solved this particular issue using the fetch syntax, so thanks for the
tip. I agree with merlin that temp tables are a headache, also because i
can't run the same function again before processing the output;

I am not familiar with arrays, but it seems like a good solution for my
problem, so will definitely study the docs.

cheers
marc

On Wed, Apr 7, 2010 at 3:43 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> 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
>

--
Marc