Re: problem with using fetch and a join.

Tom,
Many thanks for the tip. Yes adding the order by (id) to the statement
allowed the FETCH BACKWARD to work. Is there any reason why the FETCH
BACKWARD statement for such a join not to be supported in the future?

Once again,
Many Thanks
And have a great holiday season :D

Noel Faux
Department of Biochemistry and Molecluar Biology
Monash University
Clayton 3168
Victoria
Australia

----- Original Message -----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Date: Saturday, December 21, 2002 1:47 am
Subject: Re: [NOVICE] problem with using fetch and a join.

> Noel <noel(dot)faux(at)med(dot)monash(dot)edu(dot)au> writes:
> > begin;
> > declare test cursor for
> > select protein.accession
> > from protein, region
> > where protein.accession like '%1245%'
> > and protein.accession = region.accession
> > and region.protein_database = 1;
> > fetch forward 2 in test;
> > fetch backward 1 in test;
>
> FETCH (or MOVE) BACKWARD doesn't work with most join plan types (or
> indeed anything much more complex than a simple seqscan or indexscan).
> There ought to be some logic in there to detect and complain about the
> non-working cases, but right now I fear you just get the wrong answer
> when any given routine doesn't pay attention to the direction flag
> :-(
>
> I believe it will work when the top plan node is a Sort, so one
> possibleworkaround is to add an explicit ORDER BY to the query.
>
> regards, tom lane
>