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;