Re: [GENERAL] weird sum() results - Mailing list pgsql-general

You have to create a function to cast money to float.
Try the attached functions.

José

Hitesh Patel wrote:

>  I can understand that.  What's weird is that I can do a sum(amount) on
>  the whole table and the value comes out to only My only problem is that
>  the amount field is the money type and doesn't like the casting now.  A
>
>          SELECT SUM(amount::float8) FROM table where state = 'CA';
>
>  returns
>
>          ERROR: No such function 'float8' with the specified attributes
>
>  And again i'm stuck :-)
>
>  Thanks in advance for any help.
> --
> |---------------------------------|----------------------------|
> | Hitesh Patel                    |  Voice: (541) 759-3126     |
> | Facilities Development Manager  |  Fax:   (541) 759-3214     |
> | Preferred Systems               |  Email: hitesh@presys.com  |
> |---------------------------------|----------------------------|
>
> ************
--cast text to money...

drop function money(unknown);
create function money(unknown) returns money as
'
begin
    return $1;
end;
' language 'plpgsql';
--cast text to money...

drop function money(text);
create function money(text) returns money as
'
begin
    return $1;
end;
' language 'plpgsql';

--cast int4 to money...

drop function money(int4);
create function money(int4) returns money as
'
begin
    return money(float8($1));
end;
' language 'plpgsql';

--cast money to text...

drop function text(money);
create function text(money) returns text as
'
begin
    return $1;
end;
' language 'plpgsql';


--cast float8 to money...

drop function money(float8);
create function money(float8) returns money as
'
declare
    f2 float8;
    m money;
    i2 int2;
    i1 int4;
    txt text;
begin
    if $1 isnull then
        return NULL;
    end if;
    --integer part...
    i1:= dtrunc($1);
    -- decimal part...
    i2:= dround(datetime_part(''millisecond'',$1));
    -- cut 3th digit...
    txt:= dround(i2/10.0);
    if textlen(txt) = 1 then
        txt:= ''0'' || txt;
    end if;
    m:= i1 || (''.'' || txt);
    return m;
end;
' language 'plpgsql';
create table a ( f4 float, a money);
insert into a values ('1','1.23');
select cast(89123 as money);
select cast(f4 as money) from a;
select * from a;
select *,cast(f4 as money),cast(a as text) from a;
select cast(float8(-12.3) as money);
select cast( 123/10.0 as money);
select cast( 3.4e+38 as money);
select money( 12.3 );
select money( '12.3'::text );
select money( '12.3' );
select money( 12 );
drop table a;
-- +++++++ crea operatori per money...

drop function cash_pl(float8,money);
drop operator + (float8,money);

create function cash_pl(float8,money) returns money as
'
declare
    m money;
begin
    m:= $1;
    return $2 + m;
end;
' language 'plpgsql';

create operator + (
        leftarg=float8,
        rightarg=money,
        procedure=cash_pl,
        commutator='+',
        negator='-',
        restrict=eqsel,
        join=eqjoinsel
        );

drop function cash_pl(money,float8);
drop operator + (money,float8);

create function cash_pl(money,float8) returns money as
'
declare
    m money;
begin
    m:= $2;
    return $1 + m;
end;
' language 'plpgsql';

create operator + (
        leftarg=money,
        rightarg=float8,
        procedure=cash_pl,
        commutator='+',
        negator='-',
        restrict=eqsel,
        join=eqjoinsel
        );

select sal,comm+1.0 from emp;
select sal,1.0+comm from emp;
-----------crea operatori per money...

drop function cash_mi(float8,money);
drop operator - (float8,money);

create function cash_mi(float8,money) returns money as
'
declare
    m money;
begin
    m:= $1;
    return m - $2;
end;
' language 'plpgsql';

create operator - (
        leftarg=float8,
        rightarg=money,
        procedure=cash_mi,
        commutator='-',
        negator='+',
        restrict=eqsel,
        join=eqjoinsel
        );

drop function cash_mi(money,float8);
drop operator - (money,float8);

create function cash_mi(money,float8) returns money as
'
declare
    m money;
begin
    m:= $2;
    return $1 - m;
end;
' language 'plpgsql';

create operator - (
        leftarg=money,
        rightarg=float8,
        procedure=cash_mi,
        commutator='-',
        negator='+',
        restrict=eqsel,
        join=eqjoinsel
        );

select comm,comm-1.0 from emp;
select comm,1.0-comm from emp;

-- /////// crea operatori per money...

drop function float8_div_cash(float8,money);
drop operator / (float8,money);

create function float8_div_cash(float8,money) returns money as
'
declare
    f2 float8;
begin
    if $1 isnull then
        return NULL;
    end if;
    f2:= float8($2);
    f2:= $1 / f2;
    return money(f2);
end;
' language 'plpgsql';

create operator / (
        leftarg=float8,
        rightarg=money,
        procedure=float8_div_cash,
        commutator='/',
        negator='*',
        restrict=eqsel,
        join=eqjoinsel
        );

drop function cash_div_cash(money,money);
drop operator / (money,money);

create function cash_div_cash(money,money) returns money as
'
declare
    f2 float;
begin
    if $1 IS NOT NULL THEN
        f2:= float8($2);
        return cash_div_flt8($1,f2);
    else
        return NULL;
    end if;
end;
' language 'plpgsql';

create operator / (
        leftarg=money,
        rightarg=money,
        procedure=cash_div_cash,
        commutator='/',
        negator='*',
        restrict=eqsel,
        join=eqjoinsel
        );

select comm,comm/1500.00 from emp ;
select comm,comm/(comm-100) from emp ;
select comm,1601.01/comm from emp;
select comm,'$1,601.01'/comm from emp;
select comm,'$1,601.01'/comm from emp;
select 1655111.33 / comm from emp;
select '$1655111.33' / comm from emp;
-- ******* crea operatori per money...

drop function cash_mul_cash(money,money);
drop operator * (money,money);

create function cash_mul_cash(money,money) returns money as
'
declare
    f2 float;
    f3 text;
    m money;
begin
    if $1 IS NULL THEN
        return NULL;
    end if;
    f2:= float8($2);
    f3:= cash_mul_flt8($1,f2);
    if f3 = ''($./,,),,(-*.,()'' then
        return NULL;
    else
        return cash_mul_flt8($1,f2);
    end if;
end;
' language 'plpgsql';

create operator * (
        leftarg=money,
        rightarg=money,
        procedure=cash_mul_cash,
        commutator='*',
        negator='/',
        restrict=eqsel,
        join=eqjoinsel
        );

select comm,comm*'$1.0'::money from emp;
select comm,comm*1.0 from emp;
select comm,sal * (sal+sal+sal) from emp;
-- CAST money to float8:

drop function float8(money);
create function float8(money) returns float8 as
'
declare
    stringa text;
    part1 text;
    part2 text;
    i int2;
begin
    stringa:= $1;
    --checks for negative sign (if enclosed by parens)...
    if substr(stringa,1,1) = ''('' then
        i:= textlen(stringa);
        stringa:= ''-'' || substr(stringa,3,i - 3);
    else
        stringa:= substr(stringa,2); --skip currency sign
    end if;
    loop
        --strip commas...
        i:= textpos(stringa,'','');
        if i = 0 then
            return stringa;
        end if;
        part1:= substr(stringa,1,i - 1);
        part2:= substr(stringa,i + 1);
        stringa:= part1 || part2;
    end loop;
end;
' language 'plpgsql';
create table a (m money);
insert into a values('1.22');
select cast('($100.12)' as money);
select cast(m as float) from a;
select * from a;
drop table a;
-- crea operatori per money e float...

drop function cash_eq(money,float8);
drop operator = (money,float8);

create function cash_eq(money,float8) returns bool as
'
declare
    m2 money;
begin
    m2:= $2;
    return $1 = m2;
end;
' language 'plpgsql';

create operator = (
        leftarg=money,
        rightarg=float8,
        procedure=cash_eq,
        commutator='=',
        negator='<>',
        restrict=eqsel,
        join=eqjoinsel
        );

drop function cash_ne(money,float8);
drop operator <> (money,float8);

create function cash_ne(money,float8) returns bool as
'
declare
    m2 money;
begin
    m2:= $2;
    return $1 <> m2;
end;
' language 'plpgsql';

create operator <> (
        leftarg=money,
        rightarg=float8,
        procedure=cash_ne,
        commutator='<>',
        negator='=',
        restrict=eqsel,
        join=eqjoinsel
        );


drop function cash_gt(money,float8);
drop operator > (money,float8);

create function cash_gt(money,float8) returns bool as
'
declare
    m2 money;
begin
    m2:= $2;
    return $1 > m2;
end;
' language 'plpgsql';

create operator > (
        leftarg=money,
        rightarg=float8,
        procedure=cash_gt,
        commutator='>',
        negator='<=',
        restrict=eqsel,
        join=eqjoinsel
        );

-- crea operatori per float e money...

drop function cash_eq(float8,money);
drop operator = (float8,money);

create function cash_eq(float8,money) returns bool as
'
declare
    m1 money;
begin
    m1:= $1;
    return $2 = m1;
end;
' language 'plpgsql';

create operator = (
        leftarg=float8,
        rightarg=money,
        procedure=cash_eq,
        commutator='=',
        negator='<>',
        restrict=eqsel,
        join=eqjoinsel
        );

drop function cash_lt(float8,money);
drop operator < (float8,money);

create function cash_lt(float8,money) returns bool as
'
declare
    m1 money;
begin
    m1:= $1;
    return m1 < $2;
end;
' language 'plpgsql';

create operator < (
        leftarg=float8,
        rightarg=money,
        procedure=cash_lt,
        commutator='<',
        negator='>=',
        restrict=eqsel,
        join=eqjoinsel
        );

drop function cash_lt(money,float8);
drop operator < (money,float8);

create function cash_lt(money,float8) returns bool as
'
declare
    m2 money;
begin
    m2:= $2;
    return $1 < m2;
end;
' language 'plpgsql';

create operator < (
        leftarg=money,
        rightarg=float8,
        procedure=cash_lt,
        commutator='<',
        negator='>=',
        restrict=eqsel,
        join=eqjoinsel
        );

drop function cash_ne(float8,money);
drop operator <> (float8,money);

create function cash_ne(float8,money) returns bool as
'
declare
    m1 money;
begin
    m1:= $1;
    return $2 <> m1;
end;
' language 'plpgsql';

create operator <> (
        leftarg=float8,
        rightarg=money,
        procedure=cash_ne,
        commutator='<>',
        negator='=',
        restrict=eqsel,
        join=eqjoinsel
        );


drop function cash_gt(float8,money);
drop operator > (float8,money);

create function cash_gt(float8,money) returns bool as
'
declare
    m1 money;
begin
    m1:= $1;
    return m1 > $2;
end;
' language 'plpgsql';

create operator > (
        leftarg=float8,
        rightarg=money,
        procedure=cash_gt,
        commutator='>',
        negator='<=',
        restrict=eqsel,
        join=eqjoinsel
        );

drop table emp;
create table emp( comm money);
insert into emp values('1700');
insert into emp values('1600');
insert into emp values('1500');
insert into emp values('1400');
select comm,comm + comm from emp;
select comm,comm + 1 from emp;
select comm,1 + comm from emp;
select comm,comm - comm from emp;
select comm,comm - 1 from emp;
select comm,1 - comm from emp;
select comm,comm * comm from emp;
select comm,comm * -1 as meno from emp;
select comm,-1 * comm as meno from emp;
select comm,comm / comm from emp;
select comm,comm / -1 as meno from emp;
select comm,-1 / comm as meno from emp;
select * from emp where comm = 1600;
select * from emp where comm = '$1,600.00';
select * from emp where '$1,600.00' = comm;
select * from emp where 1600.00 = comm;
-------------------
select * from emp where comm <> 1600;
select * from emp where comm <> '$1,600.00';
select * from emp where 1600.00 <> comm;
select * from emp where '$1,600.00' <> comm;
-------------------
select * from emp where comm > 1600;
select * from emp where comm > '$1,600.00';
select * from emp where '$1,600.00' < comm;
select * from emp where 1600.00 < comm;
-------------------
select * from emp where comm < 1600;
select * from emp where comm < '$1,600.00';
select * from emp where 1600.00 > comm;
select * from emp where '$1,600.00' > comm;