Re: cursors in plpgsql

Tomasz Myrta <jasiek@klaster.net> writes:
> I wanted to use some select result several times in pl/pgsql function.

> Neither move backward 10000 in test;
> nor
> execute ''move backward 10000 in test'';
> doesn't work.

Releases before 7.4 are spotty about supporting backwards scan of
complex queries --- if you have a join or aggregate in the query,
it likely won't work, yielding either strange errors or wrong answers.

It will work if the top plan node in the query is a SORT, though, so
a possible workaround is to add an explicit ORDER BY to the cursor's
query.  (You will need to do some investigation with EXPLAIN to make
sure you are getting a suitable plan for the cursor.)

Or try 7.4 beta ...
        regards, tom lane