Thread: cursors in plpgsql

Hi
I wanted to use some select result several times in pl/pgsql function.

DECLARE  test    refcursor;  x    record;
BEGIN open test for select... fetch test into x;
while found loop  ...work...  fetch test into x;
end loop;

...rewind cursor using move... fetch test...

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

In first case I get:
ERROR:  parser: parse error at or near "$1"...
In secod:
ERROR:  unexpected error -5 in EXECUTE of query...
I tried also with "perform" - I got no error, but also no valid result.

What should I do with it?
Or maybe is it possible somehow to use cursor in FOR..IN loop?

Regards,
Tomasz Myrta

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

> 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.)
I rewrote my query to have sort in top of plan:
Sort  (cost=151.24..151.25 rows=1 width=36)   Sort Key: czas   ->  Aggregate  (cost=151.22..151.23 rows=1 width=36)
   ->  Group  (cost=151.22..151.23 rows=1 width=36)               ->  Sort  (cost=151.22..151.22 rows=1 width=36)
 
I'm not sure if it is what you were talking about, but it didn't help.

Anyway the best choice for this function would be a C function, but SPI 
scares me...

And one more question - which syntax is valid?
move backward..
or
execute ''move backward...


> Or try 7.4 beta ...
Currently stable branches are better for me...
> 
>             regards, tom lane
Regards,
Tomasz Myrta