Thread: is it possible to store results of aggregate calculations in table fields?

If I can do that and use table field values to do calculations once moved
it won't be necessary for me to use plr.  Earlier I tried:
max_cystalic_pressure int NOT NULL, default max(cystalic_pressure),
and psql won't create the table complaining about default and DEFAULT and
def_EXPR I intended to be max(cystalic_pressure).
Unfortunately Linux command line environment hasn't got anything able to
compete with excel in terms of excel's functions which is why I tried this
database system at all.   I hope I do not have to use Windows to get this
done.


--

--As of October 13, 2014 4:49:28 PM -0400, Jude DaShiell is alleged to have
said:

> If I can do that and use table field values to do calculations once moved
> it won't be necessary for me to use plr.  Earlier I tried:
> max_cystalic_pressure int NOT NULL, default max(cystalic_pressure),
> and psql won't create the table complaining about default and DEFAULT and
> def_EXPR I intended to be max(cystalic_pressure).
> Unfortunately Linux command line environment hasn't got anything able to
> compete with excel in terms of excel's functions which is why I tried
> this database system at all.   I hope I do not have to use Windows to get
> this done.

--As for the rest, it is mine.

You could probably get the effect with a trigger, but I have a question for
you: If their is a new max cystalic_pressure, do you want the table to
update all the values?  If so, how often do you want to check?  At that
point you are starting to get really convoluted.

Probably a better option (especially if you want to have it update anytime
the data changes) would be to create a VIEW.  Views operate like tables in
most respects to the user, but run a query instead of operating on a
pre-existing table.

On the other hand, I'm still wondering why you'd want to store that
aggregate in every entry.  It sounds like a XY[1] problem to me, so I
wonder what you are really trying to solve.  ;)

Daniel T. Staal

[1]: <http://xyproblem.info/>

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------

Jude DaShiell wrote
> If I can do that and use table field values to do calculations once moved
> it won't be necessary for me to use plr.  Earlier I tried:
> max_cystalic_pressure int NOT NULL, default max(cystalic_pressure),
> and psql won't create the table complaining about default and DEFAULT and
> def_EXPR I intended to be max(cystalic_pressure).
> Unfortunately Linux command line environment hasn't got anything able to
> compete with excel in terms of excel's functions which is why I tried this
> database system at all.   I hope I do not have to use Windows to get this
> done.

Insert into sometable (somefield)
Select max(field) from someothertable

You can try trigger functions

You can try a materialized view

You can try a simple view

You cannot use an aggregate in a default...it doesn't even make sense what
doing so would mean.

You can explain in more detail what your goal is instead and not assume you
need to store an aggregate in a field to accomplish it.

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/is-it-possible-to-store-results-of-aggregate-calculations-in-table-fields-tp5822876p5822886.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.