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(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

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
>

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

"Postgres User" <postgres(dot)developer(at)gmail(dot)com> writes:
> 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:
>>> <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.

> 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

Hm, you're right. This arises from the fact that _SPI_execute_plan
rejects cursor-related utility statements. While I'd never stopped
to question that before, it does seem like this restriction is a
bit pointless. Does anyone remember why it's like that?

regards, tom lane

Tom Lane wrote:
> "Postgres User" <postgres(dot)developer(at)gmail(dot)com> writes:
> > 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:
> >>> <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.
>
> > 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
>
> Hm, you're right. This arises from the fact that _SPI_execute_plan
> rejects cursor-related utility statements. While I'd never stopped
> to question that before, it does seem like this restriction is a
> bit pointless. Does anyone remember why it's like that?

Is there anything to do on this item?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Tom Lane wrote:
>> Hm, you're right. This arises from the fact that _SPI_execute_plan
>> rejects cursor-related utility statements. While I'd never stopped
>> to question that before, it does seem like this restriction is a
>> bit pointless. Does anyone remember why it's like that?

> Is there anything to do on this item?

I dug in the archives and realized that SPI was originally written by
Vadim, not Jan as I'd been thinking, so there's nobody left on the
project who has any special insight into this. I found this message
describing it:
http://archives.postgresql.org/pgsql-hackers/1997-08/msg00338.php
wherein Vadim says

>>> 4. I have some code for server programming interface (SPI)
>>> which allows to run queries from user defined C-functions.
>>> With current postgres limitations (no nested transactions, cursors
>>> inside BEGIN/END only) SPI disallows using of BEGIN/END & cursors.
>>> It's bad for procedures but it's enough for triggers!

so it seems he saw this just as an implementation restriction
rather than a fundamental property of SPI. And I don't see
why cursors being within-transaction only means SPI shouldn't
touch them --- maybe he just wasn't thinking carefully about that.

I'd venture that we should try to get rid of the restriction, but I'm
unsure whether removing the error check is sufficient or whether there
are real problems it's preventing.

regards, tom lane

I wrote:
> I'd venture that we should try to get rid of the restriction, but I'm
> unsure whether removing the error check is sufficient or whether there
> are real problems it's preventing.

I did a little experimentation and it seems that DECLARE CURSOR,
FETCH, and CLOSE work perfectly fine when executed through SPI
(after diking out the error check). So I see no reason to have
that prohibition in place --- we may as well just take it out and
save a few lines of code.

regards, tom lane