Re: Count of records in a row

Thanks again for the precision !

I still don't understand perfectly. We call the aggregate n times, and each
time we compute the aggregate, using (potentially) n rows, thus becoming
(at most) O(n*n).

With a standard loop, I loop n times, and each times I only need the
current row plus the previous row which I put in memory, thus O(n).

I posted a lot about my issue, but only about the fraction of the problem I
was blocked by, and I get no conclusive answer.

My problem was to find a good way to have a plpgsql function taking set of
rows as input and returning a set of rows.
I worked on range, and I wanted a polymorphic function (working with any
range).

Aggregates only returns one row at most, array are dangerous with big data,
temp table have to be created/deleted and have to be used in the same
session, cursors arn't well supported by accessing library, view can't be
written, mat view weren't available.

Anyway I solved it using cursors, not optimal but works !

(
http://stackoverflow.com/questions/18873797/plpgsql-expert-set-of-record-input-and-output-for-a-function/18874283#18874283
)

Cheers,

Rémi-C

2013/10/22 Merlin Moncure <mmoncure(at)gmail(dot)com>

> On Tue, Oct 22, 2013 at 9:09 AM, Rémi Cura <remi(dot)cura(at)gmail(dot)com> wrote:
> >
> > Thanks for this good example Merlin !
> >
> > I didn't know you could use variable inside custom aggregates, and this
> > allow to solve the problem!
> >
> > In my own problem I couldn't use aggregates because
> > _as it output at most one row, it would have mean a lots of useless
> > computation (as in this example I guess, (please correct me if it's not
> the
> > case) :
>
> That is not the case. With the approach above what you 'pay' vs
> standard loop is basically one pl/pgsql function call per output row.
> (you can do it in straight sql, but when with pl/pgsql to leverage
> cached function parsing). What you 'get' is a more general function
> because the loop structure is in the query itself as well as the
> output structure. This cleanly separates action from the data.
> Usually, the mechanics of executing the aggregate are not a huge part
> of query execution time. Actually, the worst case is when the
> aggregate is trivial but no matter what it's O(n).
>
> I'm not clear what on the issue is with your particular case, since
> you didn't post it :-). Maybe post some extra detail?
>
> merlin
>