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 <mmonc...@gmail.com> wrote: > On Tue, Apr 6, 2010 at 9:58 PM, Marc Menem <marc.me...@m4x.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
