Fun with Cursors- how to rewind a cursor

From: "Postgres User" <postgres(dot)developer(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Fun with Cursors- how to rewind a cursor
Date: 2007-03-02 00:29:34
Message-ID: b88c3460703011629o1dacf88bt670f42e5184be70e@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Demian Lessa 2007-03-02 00:42:11 Constraint validation
Previous Message Jorge Godoy 2007-03-02 00:28:40 Re: rpm containing pgdump

Browse pgsql-hackers by date

  From Date Subject
Next Message Sergey E. Koposov 2007-03-02 00:31:44 Re: SOC & user quotas
Previous Message FAST PostgreSQL 2007-03-02 00:19:43 Re: [HACKERS]