Re: Count of records in a row

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 <srobertjames(at)gmail(dot)com>

> On 10/22/13, Rémi Cura <remi(dot)cura(at)gmail(dot)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))
>
> .
>