Re: Calculated values

Greetings, and thanks for your reply!

Emmanuel Charpentier <charpent@bacbuc.dyndns.org> writes:

> Camm Maguire wrote:
> >
> > Greetings!  What is the 'best way' or 'guiding philosophy' if there is
> > one for dealing with calculated values in a pg database?  For example,
> > say you have a table with a column of floats, and you want to make a
> > view showing this column as a percent of the total across the column.
> > Should you
> >
> > a) create another table with the total, and put on triggers on insert,
> > update, and delete to modify the right total or
> >
> > b) create the view with a specific subselect to recalculate the total
> > at select time.  This has the disadvantage that the total seems to be
> > recalculated for each row.  Is there any sql syntax which can merge a
> > dynamically generated aggregate, *calculated only once*, into each
> > output row?
>
> Hmmm ... You want to avoid "remerging", if I follow you ...
>
> Coud you try :
>
> CREATE VIEW my view AS
>     SELECT id, partialsum, (partialsum/totalsum) AS percentage
>     FROM (SELECT id, SUM(item) AS partialsum GROUP BY id)
>         JOIN (SELECT SUM(item) AS totalsum); -- Note : *no* "ON" clause
>

I can't seem to get this syntax to work with pg.  No subselects seem
to be accepted in the from clause, and join doesn't seem to be a
reserved word at all.  But this kind of thing is exactly what I'm
looking for.

Another idea is to write a plpgsql function which returns tuples of a
given table.  Only problem here appears to be that to access the
columns of the result, one has to do something like 'select
id(function),percentage(function),...' which then calls the function
multiple times for each column!

Maybe this is better done outside the db.

Any advice most appreciated!

> That way, totalsum should be computed once.
>
> "Caching" computations in a secondary table is a (somewhat hidden) form
> of redundancy, and therefore a nice way to ask for trouble ...
>
> Hope this helps ...
>
> --
> Emmanuel Charpentier
>
>

--
Camm Maguire                             camm@enhanced.com
==========================================================================
"The earth is but one country, and mankind its citizens."  --  Baha'u'llah