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