Hey, when using a for you implicitly use a cursor (I think), so this is the same, use FOR if you like it more. It should be *very* fast to write !
As I wrote, relational algebra can handle it, but it is not practically feasible : If you just execute 3 times the query I wrote, you will have your answer. It is 3 times because the biggest sequence is A A A A. That's the problem, your number of execution depends on the max size of sequence. The problems boils down to this : the answer for one row depends on the answer of the previous row, the row before , etc. You could succeed with ordering by id in a windows function, and in this window function order by new_id and putting null to the end, but such nested windows functions calls are not allowed. Nevertheless if you find something purely relational please keep me posted ! Cheers, Rémi-C 2013/10/22 Robert James <srobertja...@gmail.com> > On 10/22/13, Rémi Cura <remi.c...@gmail.com> wrote: > > But it is immensely easier and sometimes mandatory to use instead > > a plpgsql function using cursor (or cursors). > > > > It would be something like that in plpgsql : > > > > cursor on table of letter ordered > > accum = 0; > > loop on rows of table ordered > > > > if letter = previous letter, new_id = accum > > else accum ++ ; new_id = accum > > > > old letter = new_letter > > new letter = next letter; > > > > end of loop, > > Shouldn't it be possible to do that with a FOR loop without a cursor? > > It might be that procedural is the way to go. But I still believe > that relational algebra can handle this, even without a window > function. Something like: > > SELECT event e, COUNT( > SELECT event oe ... WHERE oe.event_time > e.event_time AND NOT EXISTS ( > SELECT event te WHERE te.event_time > e.event_time AND > te.event_time < oe.event_time)) > > . >
