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

Search Discussions

  • Scara Maccai at Aug 13, 2009 at 11:07 am
    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
  • Alban Hertroys at Aug 13, 2009 at 11:44 am

    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!
  • Sim Zacks at Aug 13, 2009 at 12:51 pm

    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
  • Alvaro Herrera at Aug 13, 2009 at 2:26 pm

    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedAug 13, '09 at 10:52a
activeAug 13, '09 at 2:26p
posts5
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase