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

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: (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-hackers
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:

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

     OPEN ref_entry FOR
     SELECT * FROM big_select_statement;

       FETCH ref_entry INTO rec;

       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;


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

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


pgsql-hackers by date

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

pgsql-general by date

Next:From: Demian LessaDate: 2007-03-02 00:42:11
Subject: Constraint validation
Previous:From: Jorge GodoyDate: 2007-03-02 00:28:40
Subject: Re: rpm containing pgdump

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