Re: Fun with Cursors- how to rewind a cursor

Thanks for the pointer.  According to the Postgres docs:

"The portal name used for a cursor can be specified by the programmer
or automatically generated. To specify a portal name, simply assign a
string to the refcursor variable before opening it."

So I added the following code:

<before opening cursor>     ref_entry = 'c_entry';
<after looping thru cursor>  MOVE Backward All In c_entry;

Which compiles- progress!  But when I try to execute it, this error is returned:

SPI_execute_plan failed executing query "MOVE Backward All In
c_entry": SPI_ERROR_CURSOR

No luck finding any info on this error, except for a few bug reports.
Any ideas?  I've pasted my complete code below:


DECLARE
    ref_entry refcursor;

    rec record;
    v_list varchar = '';
    i integer = 0;

BEGIN

     -- assign name to cursor
     ref_entry = 'c_entry';

     OPEN ref_entry FOR
     SELECT * FROM big_select_statement

     LOOP
       FETCH ref_entry INTO rec;
       EXIT WHEN NOT FOUND;

       i = i + 1;
       IF v_list != '' THEN
          v_list = v_list || ', ';
       END IF;
       v_list = v_list || rec.entry_id::varchar;

     END LOOP;
     MOVE Backward All In c_entry;

END;

On 3/1/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Postgres User" <postgres.developer@gmail.com> writes:
> > Is there any way to 'rewind' the cursor to the first row?
>
> plpgsql doesn't have any command for that (though I think someone is
> working on improving its cursor command set).  You should be able to
> work around it by EXECUTE'ing a MOVE BACKWARD ALL command, though.
> You just need to know the real name of the cursor --- see 'Returning
> Cursors' in the plpgsql docs for discussion.
>
>                        regards, tom lane
>