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

On 18 Aug 2009, at 6:51, Sim Zacks wrote:

> 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.

> cleanest thing to do, with out having to throw the all the functions
> into a case statement is an aggregate function that takes the 2 unit
> types and then gives back a result.

I won't try to force something on you, it's your project after all,
but I think you're still seeing only part of the picture I was trying
to show you.

You have a table with quantities in different units, and you want to
summarise those. If you do that with old-fashioned pen & paper the
first thing you do is convert all your quantities to the same unit so
that you can add them properly. That's basic math.

In this case however we have far better tools, namely a computer with
a database. It's easy to create a table with units and their
conversion factor to a standard unit. If you go a bit further you'd
create a few tables linking units and how to convert them to each
other, which also solves the case where you're not dealing with just
distances (the volumes you mention above, for example).

Once you have that, it's easy to write a few (immutable!) functions:
- convert_to(quantity, unit), which converts a quantity in a given
unit to a standard unit, and
- convert_from(quantity, unit), which converts a quantity in your
standard unit to the given unit.

Then you simply write your query as:
SELECT convert_from(SUM(convert_to(quantity, unit)), 'inch') FROM table;

If you're going for the more complicated approach that can directly
convert any unit to any other (provided the record that links them
exists) the query gets even simpler. You only need one conversion
function in that case:
- convert_unit(quantity, from_unit, to_unit)
and your query would become:
SELECT SUM(convert_unit(quantity, unit, 'inch')) FROM table;

If you're worried about accuracy; the different unit styles have fixed
conversion factors with a finite accuracy. For example; 1 inch is
25.40 mm - that's accurate. If you take their accuracy into account
when defining your quantity columns/variables you won't get any
rounding errors caused by the unit conversion.

Considering you're using at least one of those functions in an
aggregate it's probably worth implementing them in C instead of for
example pl/pgsql, but the latter is easier to test the concept.

And you get the added bonus of being able to convert units anywhere
you like. If you have customers who prefer seeing their quantities
measured in imperial units
and customers preferring standard units you can serve them both. It
adds value to your project; You may recall a recent space probe that
went off in the wrong direction because it had a mix of imperial and
standard units used in its design and someone somewhere forgot to
correct for that in a piece of software...

In fact, having these tables and functions available would be useful
to many people. It would make a great pgfoundry project I think.

>
>
>> Well I don't think you got Alban's suggestion right...
>> What he was trying to say was:
>>
>> - use a regular (not aggregated) function to convert all measures
>> to mm
>> - use the normal SUM() to sum those value
>> - use another regular function to convert from mm to whatever
>>
>> select mm_to_m(sum(convert_to_mm(measure))) from a
>>
>> Which is easier than my solution
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>

Alban Hertroys

--
Screwing up is the correct approach to attaching something to the
ceiling.


!DSPAM:737,4a8a8ee410137968484637!