Re: multiple paramters in aggregate function - Mailing list pgsql-general

From Sim Zacks
Subject Re: multiple paramters in aggregate function
Date
Msg-id 4A8CFD05.3030802@compulab.co.il
Whole thread Raw
In response to Re: multiple paramters in aggregate function  (Alban Hertroys <dalroi@solfertje.student.utwente.nl>)
Responses Re: multiple paramters in aggregate function  (Sim Zacks <sim@compulab.co.il>)
Re: multiple paramters in aggregate function  (Karsten Hilbert <Karsten.Hilbert@gmx.net>)
List pgsql-general
>> That would be true if all units were always convertible to mm, but we
>> have volume also, we also have feet etc.. So that the easiest and
>
> How did you plan on solving that in your multiple-argument aggregate?
> Fake their value by adding 0? That's no different for my suggested
> solution.
Wow. I didn't think this would become such a monster. Awesome work on
your unit conversion system, that will be tons of help.
I apologize for being slow on the response, I am way over-busy right now.

However, getting back to where I was, there are only 2 differences
between what I want to do and what you are suggesting:
1) You need 2 functions, a sum and a conversion, while I wrote the
conversion function in the sum.
2) You need to know before hand which measurement you want in the end
and I don't.

I either need 2 sum functions, one which will just return the final
value and the other will return the unit used, or I need my aggregate to
return a composite type, which is less desirable in my case as I want my
results to be include one value per field (qty, unitid)

In other words, I have a conversion table of all different units. If
there is no conversion between 2 units (such as volume and area) then
the sum returns null.
My unit sum function works now. It takes 2 arguments, a numeric and a
unitid. The state variable is of composite type, with a numeric and an int.
In the aggregate function, it first converts one of the values to the
other (according to the business rules, such that I always go to the
lower measurement, if comparing mm and m, it will convert to mm, in and
cm it will convert to cm).

My query contains select ..,
sum_unitvalues(qty,unitid),sum_units(unitid),...
then the units returned do not have to be known in advance, which is
important in this specific project.

pgsql-general by date:

Previous
From: Gerhard Heift
Date:
Subject: unique index for periods
Next
From: Sim Zacks
Date:
Subject: Re: multiple paramters in aggregate function