On 13 Aug 2009, at 12:51, Sim Zacks wrote:
What I am trying to do is sum a quantity field, but it also has units
that need to be converted.
4 meter
400 mm
100 cm
I want to sum it all, my function decides to use meter (based on the
requirements) and should return 4.00104 (or something like that) and
then I have a second aggregate function which just chooses which
unit to
use, so in my query I use 2 aggregate functions, one gives me the
sum of
converted quantity and the other gives me which unit it is in.
Is there a better way?
It's probably easiest to decide on an internal unit to use in your
aggregate and only convert it to the desired unit once you're done
summing them. I'd probably convert all measurements to mm in the
function and summarise those.
The final unit conversion can be taken out of the aggregate that way
too, so I'd also have separate functions for converting units to and
from other units - those functions will likely come in handy anyway.
Your query would then be something like:
SELECT convert_unit(sum_mm(field), 'mm', 'meter') FROM table;
In general, don't put multiple operations in one function but split
them into separate functions. You're much more flexible that way.
Alban Hertroys
--
Screwing up is the correct approach to attaching something to the
ceiling.
!DSPAM:737,4a83fca210137297812668!
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
