multiple paramters in aggregate function

Is there a better way?

I think you could use a User Data Type.
Then pass that as parameter to your aggregate function.

That is: you would pass

(4, 'meter')

(400, 'mm')

(100, 'cm')

to your aggregate function.

Each one is a user datatype:

CREATE TYPE mytype AS (
v double precision,
t varchar(10)
);

See

http://www.postgresql.org/docs/8.4/static/rowtypes.html

This is the example based on a custom data type of complex numbers:

http://www.postgresql.org/docs/8.4/static/xaggr.html

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!

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.

That could work in some cases, however in our case it would not produce
desirable results. If the user put in meters, he is expecting to see
meters. My problem comes in only when the user put in values in multiple
unit types, which does not happen very often. It is generally a mistake,
but we would prefer to let them make the mistake and then see an
irrational result and correcting it, rather then telling them they
probably made a mistake.

I think Scara's solution makes the most sense. It is slightly cleaner
then using an array and comes up with the same result.

Sim

That could work in some cases, however in our case it would
not produce
desirable results.

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.

Sim Zacks wrote:

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.

Have you seen Martijn van Oosterhout's tagged types?

http://svana.org/kleptog/pgsql/taggedtypes.html

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

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!

Hello all,

Inspired by the original discussion on aggregating quantities of
different units I made a start at a unit conversion database and the
result is here: http://solfertje.student.utwente.nl/documents/units.sql

This is not a complete implementation, I just thought I'd show you
what I got so far and hope you have some ideas about a few problems
I'm facing.

What it can do is convert a bunch of units to and fro, including to
the same unit, using a conversion factor and a pair of offsets to
adjust for zero-point differences (�C to �F or K for example).
By default it installs itself in a schema named 'units'. At the end of
the script is a query that converts '23' (which happened to be the
temperature here while I was testing) from every known unit to every
other known unit. That's meant as a test, but it also makes verifying
correctness fairly easy.

Problem areas are:
- It doesn't contain every possible conversion yet.
Some units are probably just plain wrong too. I don't know every unit
in the list, that's why. I'm especially unfamiliar with imperial units
and some of the more esoteric units. Corrections and additions are
welcome.

- It can't handle unit scaling yet ('mm' to 'm' for example).
There are some units in there that are scaled by default ('kg' is the
standard unit for mass and not 'g'), and some units seem to be not
scalable at all (ever heard of 'mK' - 'milliKelvin'?). This may be
solved by adding a base_scale column which could be NULL if not
applicable.

- Some units are combinations of multiple base-units that would
require parsing the combined unit to determine how to scale or convert
parts of it. I haven't found a good way of handling that yet, maybe I
just shouldn't... I have a feeling that at the very least parsing
units should only happen if the unit isn't a base-unit, which can
simply be flagged.

The latter two issues seem to require a unit parser, which seems a bit
heavy-weight. Or I should just drop all the combined units and only
deal with base-units. Suggestions or even code are welcome.

On 18 Aug 2009, at 13:22, Alban Hertroys wrote:

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;

Alban Hertroys

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

!DSPAM:737,4a8aec0910131445318212!

On 18 Aug 2009, at 19:59, Alban Hertroys wrote:

Hello all,

Inspired by the original discussion on aggregating quantities of
different units I made a start at a unit conversion database and the
result is here: http://solfertje.student.utwente.nl/documents/units.sql

I just uploaded an updated version that handles scaling of units
properly for at least the base-units. For example:

development=> select convert_unit(28, '�C', '�F');
convert_unit
-------------------------
82.39999999999999999960
(1 row)

development=> select convert_unit(28, 'mg', 'gr');
convert_unit
------------------------
0.43210603388236005822
(1 row)

development=> select convert_unit(28, 'lb', 'kg');
convert_unit
---------------------
10.4507682048000000
(1 row)

development=> select convert_unit(28, 'kg', 'lb');
convert_unit
---------------------
75.0184086601319546
(1 row)

development=> select convert_unit(28, 'dm', 'mm');
convert_unit
-----------------------
2800.0000000000000000
(1 row)

Problem areas are:
- It doesn't contain every possible conversion yet.
Some units are probably just plain wrong too. I don't know every
unit in the list, that's why. I'm especially unfamiliar with
imperial units and some of the more esoteric units. Corrections and
additions are welcome.

I added several units from http://en.wikipedia.org/wiki/Conversion_of_units
(my original source was Binas, an old book I had left over from high-
school days). Imperial units should be more complete now.
Still, if you notice any obvious errors or omissions, let me know.

- It can't handle unit scaling yet ('mm' to 'm' for example).
There are some units in there that are scaled by default ('kg' is
the standard unit for mass and not 'g'), and some units seem to be
not scalable at all (ever heard of 'mK' - 'milliKelvin'?). This may
be solved by adding a base_scale column which could be NULL if not
applicable.

This is working now.

- Some units are combinations of multiple base-units that would
require parsing the combined unit to determine how to scale or
convert parts of it. I haven't found a good way of handling that
yet, maybe I just shouldn't... I have a feeling that at the very
least parsing units should only happen if the unit isn't a base-
unit, which can simply be flagged.

These are flagged now, but that's about it. No conversions for such
units have been entered yet.
One change is that the formatting of the units has changed to
something less likely to give ambiguous results (eg. 'ms-1' has been
changed to 'm.s^-1').

I think this database is fairly usable in its current state. Any more
development on it warrants its own project page somewhere and taking
it off-list, I'll no longer pester you with updates on this ;)

Have a nice day!

Alban Hertroys

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

!DSPAM:737,4a8c220b10137643883901!

Alban,

I think having an installable schema for units of measure with
definitions and functions would be a great addition to PostgreSQL.

I for one know we would use it in GNUmed (wiki.gnumed.de).

A few points:

Would these guys be of use as a source for reference data ?

http://unitsofmeasure.org/

You may want to think about whether there's use in combining
units with tagged types:

http://svana.org/kleptog/pgsql/taggedtypes.html

There's also a Debian package which comes with a text format
units database:

http://packages.debian.org/source/sid/units

The original source for that:

This package was put together by me, James Troup <james@nocrew.org>,
from the GNU sources, which I obtained from
sunsite.doc.ic.ac.uk:/pub/gnu/units-1.54.tar.gz.

The current version of the package was obtained from
ftp://ftp.gnu.org/gnu/units
by John Hasler, the current Debian maintainer.

I think this database is fairly usable in its current state. Any more
development on it

Yes please ! :-)

warrants its own project page somewhere and taking
it off-list, I'll no longer pester you with updates on this ;)

Ah, no problem. Please keep posting release announcements. Maybe
on -announce if so.

Karsten
--
Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3 -
sicherer, schneller und einfacher! http://portal.gmx.net/de/go/chbrowser

On 19 Aug 2009, at 19:20, Karsten Hilbert wrote:

Alban,

I think having an installable schema for units of measure with
definitions and functions would be a great addition to PostgreSQL.

Karsten,

Thanks for the praise and the links.

I for one know we would use it in GNUmed (wiki.gnumed.de).

A few points:

Would these guys be of use as a source for reference data ?

http://unitsofmeasure.org/

That looks certainly interesting, especially the fact that they
provide a source of units and conversions in an XML format. Although
their conversion formulas don't look all that easy to parse.

I've run into a few of the problems they mention already; for example
the slight differences between imperial and US units of measurement
with the same names and abbreviations...

You may want to think about whether there's use in combining
units with tagged types:

http://svana.org/kleptog/pgsql/taggedtypes.html

Yes, I've been thinking the same thing. I had it bookmarked already
for the very purpose of checking it out and see how I could use tagged
types with units.

There's also a Debian package which comes with a text format
units database:

http://packages.debian.org/source/sid/units

The original source for that:

This package was put together by me, James Troup <james@nocrew.org>,
from the GNU sources, which I obtained from
sunsite.doc.ic.ac.uk:/pub/gnu/units-1.54.tar.gz.

I don't consider that tool very reliable[1]It doesn't correctly convert �C to �F or vv, that was one of the first things I tried.. A number of their
concepts are probably usable though. I have it's source in my source
tree (FreeBSD), so ample opportunity to peek.

[1]: It doesn't correctly convert �C to �F or vv, that was one of the first things I tried.
first things I tried.

The current version of the package was obtained from
ftp://ftp.gnu.org/gnu/units
by John Hasler, the current Debian maintainer.

I think this database is fairly usable in its current state. Any more
development on it

Yes please ! :-)

He he, all right then! There certainly are some things left to
improve. One thing I noticed from the links you sent is that I ignored
a few units used in medicine assuming they were deprecated ages ago -
apparently not...
Then again, encouraging their usage may not be the best thing to do,
but who am I to decide what units people use eh?

warrants its own project page somewhere and taking
it off-list, I'll no longer pester you with updates on this ;)

Ah, no problem. Please keep posting release announcements. Maybe
on -announce if so.

Yes, announce would be the right place. I dislike it when people start
using this list for announcements of new versions of their software,
so let's not start doing that myself :)

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4a8c44fa10131730049303!

Alban Hertroys wrote:

There's also a Debian package which comes with a text format
units database:

http://packages.debian.org/source/sid/units

The original source for that:

This package was put together by me, James Troup <james@nocrew.org>,
from the GNU sources, which I obtained from
sunsite.doc.ic.ac.uk:/pub/gnu/units-1.54.tar.gz.

I don't consider that tool very reliable[1]. A number of their
concepts are probably usable though. I have it's source in my source
tree (FreeBSD), so ample opportunity to peek.

[1] It doesn't correctly convert �C to �F or vv, that was one of the
first things I tried.

Seems it's easy to misuse it. You need tempF(x) and tempC notation for
converting absolute temperature differences:

You have: tempF(212)
You want: tempC
100

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Wed, Aug 19, 2009 at 8:24 PM, Alvaro
Herrera<alvherre@commandprompt.com> wrote:

[1] It doesn't correctly convert °C to °F or vv, that was one of the
first things I tried.

Seems it's easy to misuse it.  You need tempF(x) and tempC notation for
converting absolute temperature differences:

You have: tempF(212)
You want: tempC
       100

That depends on whether you're converting a temperature or a
temperature difference. If you want to know what a 100 degree C drop
in temperature equates to in Fahrenheit the answer is not 212 but
rather 180.

I think it would be useful to have a builtin data type which contained
a float and an opaque text unit. It could support linear operations
like +, -, and sum() by just throwing an error if the units didn't
match.

Then you could add an add-on function which converted one such datum
to another with a desired new units by calling out to the units
program.

That would allow people to store values with heterogenous units. So
for example you could have SMART stats in a single table where the
time values, unitless values, and temperature values are all in the
same column. As long as you only compare, say, drive temperatures to
max temperatures you never actually need to know about the units. It
would serve as an assertion check to ensure you don't compare drive
temperatures to error counts or something like that.

--
greg
http://mit.edu/~gsstark/resume.pdf

Greg Stark wrote:

On Wed, Aug 19, 2009 at 8:24 PM, Alvaro
Herrera<alvherre@commandprompt.com> wrote:

[1] It doesn't correctly convert �C to �F or vv, that was one of the
first things I tried.

Seems it's easy to misuse it. �You need tempF(x) and tempC notation for
converting absolute temperature differences:

You have: tempF(212)
You want: tempC
� � � �100

That depends on whether you're converting a temperature or a
temperature difference. If you want to know what a 100 degree C drop
in temperature equates to in Fahrenheit the answer is not 212 but
rather 180.

Right -- and there's a different interface for that.

You have: 100 degC
You want: degF
* 180
/ 0.0055555556

I think it would be useful to have a builtin data type which contained
a float and an opaque text unit. It could support linear operations
like +, -, and sum() by just throwing an error if the units didn't
match.

This sounds very much like Martijn's tagged types.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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.

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.

To give an example of my required result set:

unitid
1 = mm
2 = inch
3 = ft
4 = gram

create table test(id serial primary key, qty numeric(12,4), unitid int);
insert into test(qty,unitid)
values(100,2),(200,2),(5,3),(20,1),(800,4)

select sum_unitvalues(qty,unitid) as qty, sum_units(unitid) as unitid
from test where unitid<>4;

qty | unitid
----------------------------
9124 | 1

select sum_unitvalues(qty,unitid) as qty, sum_units(unitid) as unitid
from test where unitid not in (1,4);

qty | unitid
----------------------------
360 | 2

select sum_unitvalues(qty,unitid) as qty, sum_units(unitid) as unitid
from test;
qty | unitid
----------------------------
NULL | NULL

On 2009-08-19, Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote:

On 19 Aug 2009, at 19:20, Karsten Hilbert wrote:

Alban,

I think having an installable schema for units of measure with
definitions and functions would be a great addition to PostgreSQL.

Karsten,

Thanks for the praise and the links.

I for one know we would use it in GNUmed (wiki.gnumed.de).

A few points:

Would these guys be of use as a source for reference data ?

http://unitsofmeasure.org/

That looks certainly interesting, especially the fact that they
provide a source of units and conversions in an XML format. Although
their conversion formulas don't look all that easy to parse.

I've run into a few of the problems they mention already; for example
the slight differences between imperial and US units of measurement
with the same names and abbreviations...

You may want to think about whether there's use in combining
units with tagged types:

http://svana.org/kleptog/pgsql/taggedtypes.html

Yes, I've been thinking the same thing. I had it bookmarked already
for the very purpose of checking it out and see how I could use tagged
types with units.

There's also a Debian package which comes with a text format
units database:

http://packages.debian.org/source/sid/units

The original source for that:

This package was put together by me, James Troup <james@nocrew.org>,
from the GNU sources, which I obtained from
sunsite.doc.ic.ac.uk:/pub/gnu/units-1.54.tar.gz.

I don't consider that tool very reliable[1]. A number of their
concepts are probably usable though. I have it's source in my source
tree (FreeBSD), so ample opportunity to peek.

the licence is GPL2 though so that may restrict it's use in some
contexts.

[1] It doesn't correctly convert °C to °F or vv, that was one of the
first things I tried.

what is "vv"
for °C to °F RTFM:

units 'tempC(37)' 'tempF'

it handles units (and arbitrary derived units) that are linked by a ratio
It does that very well.

units "mi water/kWh" "mm hg/btu"

Offset units like centigrade and farenheit pose a problem in many
contexts.

if the temperature just dropped 9 °F what's that in °C ?

yet the answer can be coerced from units.

units 'tempF(0)-tempF(9)+tempC(0)' 'tempC'

On Thu, Aug 20, 2009 at 10:36:37AM +0300, Sim Zacks wrote:

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.

Shouldn't that return NULL IOW unknown ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html style="direction: ltr;">
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body style="direction: ltr;" bgcolor="#ffffff" text="#000000">
<br>
<blockquote cite="mid:20090820122224.GA5532@merkur.hilbert.loc"
type="cite">
<blockquote type="cite">
<pre wrap="">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.
</pre>
</blockquote>
<pre wrap=""><!---->
Shouldn't that return NULL IOW unknown ?

Karsten
</pre>
</blockquote>
I am not familiar with returning unknown. I know that according to the
spec an aggregate should only return null if all of its values of the
aggregate are null. If there is a way to return unknown in a non-NULL
way, then that would be preferred.<br>
</body>
</html>