Re: [GENERAL] multiple paramters in aggregate function
On Thu, Aug 20, 2009 at 03:30:00PM +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 ? > > I am not familiar with returning unknown. I know that according to the spec an > aggregate Oh, OK, I forgot about the aggregate part. > 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. I don't know of any. However, it seems patently wrong to let an integer sum()-something return 0 when some of its inputs are NULL. After all, the sum could truly have been 0. This should either throw an error, return NULL, or let me choose to ignore NULL input and return the sum of non-NULL input. But I have a feeling I am tip-toeing into a Holy War situation. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] multiple paramters in aggregate function
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 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.
Re: [GENERAL] multiple paramters in aggregate function
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] multiple paramters in aggregate function
> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] multiple paramters in aggregate function
>> 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] multiple paramters in aggregate function
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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] multiple paramters in aggregate function
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. > 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
Re: [GENERAL] multiple paramters in aggregate function
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] multiple paramters in aggregate function
> 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] multiple paramters in aggregate function
> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] multiple paramters in aggregate function
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
R: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] multiple paramters in aggregate function
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
