Skip site navigation (1) Skip section navigation (2)

Re: Fun with Cursors- how to rewind a cursor

From: "Postgres User" <postgres(dot)developer(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Fun with Cursors- how to rewind a cursor
Date: 2007-03-02 03:39:01
Message-ID: b88c3460703011939j72ddd36fv35721f7d352e86b2@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
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
>

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2007-03-02 05:29:59
Subject: Re: Compilation errors
Previous:From: Jim C. NasbyDate: 2007-03-02 03:36:18
Subject: Re: Revitalising VACUUM FULL for 8.3

pgsql-general by date

Next:From: Postgres UserDate: 2007-03-02 04:48:00
Subject: Re: Add items to a record variable
Previous:From: Andrew MaduDate: 2007-03-02 03:22:03
Subject: SOLVED: Re: PostgreSQL primary (sequence) key issue (Ruby/Rails)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group