Can anybody help me with this? (sorry for posting on hackers)
 
I need to be able determine the next row based on a non unique key
(index).  I have solved this problem, but I would like to know if there
is a simpler solution.  For those of you who have ever dealt with COBOL,
this is an on the fly sql construction of a 'READ NEXT' statement
following a START.  Very similar to cursors, but because of the
transactional limitations of cursors they cannot be used in this
context.
 
Example: 
I have a table t with columns a, b, c.  I have values a1, b1, c1 for
those columns and would like to know the next value in the table when
ordered by a, b.  I have values a1, b1, and oid1 and would like to find
the very next record in the table (essentially looking for the next
record in the index).
 
I have two solutions: one with 'or' logic and one with 'and' logic.
Note: if the index we are scanning has the unique constraint, the oid
part of the logic (and the index) can be left out.
 
create index t_idx on t(a, b, oid);
 
*or* logic:
select * from t 
where  
    a > a1 OR
    (a = a1 and b > b1) OR
    (a = a1 and b = b1 and oid > oid1)
    order by a, b, oid
    
*and* logic
select * from t
where 
            a >= a1 AND
            (a > a1 or b >= b1) AND
            (a > a1 or b > b1 or oid > oid1)
            order by a, b, oid
 
I think, of the two, the or logic is much better.  The problem with both
approaches is that when we have a 4 column based key (common in COBOL)
our index is based on a,b,c,d,o and the number of comparisons (and our
select statement) becomes large, and performance is very important!  If
some logical genius knows how to reduce the above logic into a more
direct approach, feel free to comment.
 
Postgres properly optimizes both cases, and uses the key even for a
table with 1 million + records in it, the answer comes back right away.
 
My question is: is there a simpler way to do this? AFIK there is no way
in sql to directly find the 'next' or 'previous' record in an ordered
index (in other words, I have oid n, what is the next oid in the index?)
without using the above logic.  In other words, I am missing the ability
to deal with a multi column index value in a comparison as a single
entity.
 
p.s.
the above queries are 'sliding window' queries similar to cursors.  If
your table traversal can be defined by an (unique) index, you can use
the above templates to slide over the tables without the use of a
cursor.
 
Merlin














Can anybody help me with this? (sorry
for posting on hackers)
 
I need to be able determine the next row based on a non
unique key (index).  I have solved
this problem, but I would like to know if there is a simpler solution.  For those of you who have ever dealt
with COBOL, this is an on the fly sql construction of a 'READ NEXT' statement
following a START.  Very similar to
cursors, but because of the transactional limitations of cursors they cannot be
used in this context.
 
Example: 
I have a table t with columns a, b, c.  I have values a1, b1, c1 for those
columns and would like to know the next value in the table when ordered by a,
b.  I have values a1, b1, and oid1
and would like to find the very next record in the table (essentially looking
for the next record in the index).
 
I have two solutions: one with 'or' logic and one with 'and'
logic.  Note: if the index we are
scanning has the unique constraint, the oid part of
the logic (and the index) can be left out.
 
create index t_idx on t(a, b, oid);
 
*or* logic:
select * from t 
where  
    a
> a1 OR
    (a
= a1 and b > b1) OR
    (a
= a1 and b = b1 and oid > oid1)
    order by a, b, oid
    
*and* logic
select * from t
where 
            a
>= a1 AND
            (a
> a1 or b >= b1) AND
            (a
> a1 or b > b1 or oid > oid1)
            order by a, b, oid
 
I think, of the two, the or logic
is much better.  The problem with
both approaches is that when we have a 4 column based key (common in COBOL) our
index is based on a,b,c,d,o and the number of
comparisons (and our select statement) becomes large, and performance is very
important!  If some logical genius
knows how to reduce the above logic into a more direct approach, feel free to
comment.
 
Postgres properly optimizes both cases, and uses the key
even for a table with 1 million + records in it, the
answer comes back right away.
 
My question is: is there a simpler way to do this? AFIK
there is no way in sql to directly find the 'next' or 'previous' record in an
ordered index (in other words, I have oid n, what is
the next oid in the index?) without using the above
logic.  In other words, I am missing
the ability to deal with a multi column index value in a comparison as a single
entity.
 
p.s.
the above
queries are 'sliding window' queries similar to cursors.  If your table traversal can be defined
by an (unique) index, you can use the above templates to slide over the tables
without the use of a cursor.
 
Merlin
Fri, 15 Aug 2003 13:42:23 -0400
"Merlin Moncure" <merlin.moncure@rcsonline.com>
best way to retreive the next record in a multi column index
On Fri, Aug 15, 2003 at 13:42:23 -0400,
  Merlin Moncure <merlin.moncure@rcsonline.com> wrote:
>  
> Example: 
> I have a table t with columns a, b, c.  I have values a1, b1, c1 for
> those columns and would like to know the next value in the table when
> ordered by a, b.  I have values a1, b1, and oid1 and would like to find
> the very next record in the table (essentially looking for the next
> record in the index).
>  
> I have two solutions: one with 'or' logic and one with 'and' logic.
> Note: if the index we are scanning has the unique constraint, the oid
> part of the logic (and the index) can be left out.
>  
How about something like the following:
select * from t
where  a >= a1 and b >= b1
            order by a, b limit 1 offset 1;

Fri, 15 Aug 2003 15:15:03 -0500
Bruno Wolff III <bruno@wolff.to>
Re: best way to retreive the next record in a multi column index