Thread: Fun with Cursors- how to rewind a cursor

Hi,
I'm opening a refcursor in Postgres to return a dataset to the client.
 However, before returning the cursor, I'd like to iterate thru the
rows.  Here's the code:

  DECLARE
     ref_entry  refcursor;
     rec  record;
     i  integer = 0;
     v_list varchar = '';

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

     Return next ref_entry;

  END;

There's one slight and obvious problem- the cursor returns nothing to
the client because I've already fetched all the rows.  (Remove the
LOOP, and the cursor returns all rows as expected).

Is there any way to 'rewind' the cursor to the first row?  I realize
that I can simply execute the full query and open another cursor to
return to the client, but I'll take a performance hit that I'd like to
avoid.

I've tried a few FETCH BACKWARD and other statements but only get
syntax errors returned by the comiler.  Anyone done this before?

"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

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
>

"Postgres User" <postgres.developer@gmail.com> writes:
> So I added the following code:

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

You have to use EXECUTE for the latter.

            regards, tom lane

I had tried several variations of MOVE Backward inside an Execute
statement earlier.  And now, I'm seeing this error appear again:

ERROR: 0A000: cannot manipulate cursors directly in PL/pgSQL

I updated the Postgres function below with this replacement line:
Execute 'MOVE Backward All In c_entry';

Any other thoughts?


On 3/1/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Postgres User" <postgres.developer@gmail.com> writes:
> > So I added the following code:
>
> > <before opening cursor>     ref_entry = 'c_entry';
> > <after looping thru cursor>  MOVE Backward All In c_entry;
>
> You have to use EXECUTE for the latter.
>
>                        regards, tom lane
>