multiple paramters in aggregate function

From: Sim Zacks <sim(at)compulab(dot)co(dot)il>
To: pgsql-general(at)postgresql(dot)org
Subject: multiple paramters in aggregate function
Date: 2009-08-13 10:51:49
Message-ID: 4A83F045.4020007@compulab.co.il
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

According to the documentation, you can pass multiple parameters into an
aggregate function, but it only stores one value.

What I am trying to do is sum a quantity field, but it also has units
that need to be converted.

My function should take 2 values, the quantity and the unit, determine
which unit to use, the one in state or the passed in one and to convert
either the quantity in state or the quantity passed in and add it to the
other quantity.

In other words:

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.

Currently, the only way I can think of doing this is by keeping an array
in state.

Is there a better way?

Thank you

Sim

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scara Maccai 2009-08-13 10:53:33 Re: totally different plan when using partitions + request
Previous Message Sam Mason 2009-08-13 10:33:38 Re: Looping through string constants