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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Postgres User" <postgres(dot)developer(at)gmail(dot)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
>