Re: Problem with cursor

"Frank Millman" <frank(at)chagford(dot)com> writes:
> [ this doesn't work: ]
> begin ;
> declare fm scroll cursor for select a.CompanyId, b.CompanyName
> from SysUsersCompanies a, SysCompanies b
> where UserId =3D 'Greer' and a.CompanyId =3D b.CompanyId
> order by a.CompanyId ;
> move 1 in fm ;
> fetch next from fm ;
> move -2 in fm ;
> fetch next from fm ;

Yeah, there are known problems with running complex queries backwards
in existing releases. Some plan types support this, and some don't
--- in particular, mergejoin doesn't handle it, which very likely is
the plan type you are getting here.

There is a fix in place for 7.4 but that won't help you today. The only
workaround I can suggest is to force the system to adopt a plan that
involves a top-level sort. You could probably do that by adding some
additional terms to the ORDER BY clause, but some experimentation with
EXPLAIN will be called for.

regards, tom lane