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@xxxxxxxxxxxxx> wrote:
"Postgres User" <postgres.developer@xxxxxxxxx> 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
