Since the function manager in pg has been rewritten to correctly handle nulls, I think we should consider creating equivalents for nvl and decode. Here is a test case that I tried for nvl:
drop table test;
create table test (
int_var integer,
ch_var char(1),
fl_var float,
vc_var varchar(20),
tx_var text
);
insert into test values (null,'t',1.0,'uno','dos');
insert into test values (1,null,2.0,'foo','bar');
insert into test values (2,'f',null,'ocho','siete');
insert into test values (3,'f',3.0,null,'foo');
insert into test values (4,'t',4.0,'baz',null);
insert into test values (5,'f',5.0,'goodbye','hello');
drop function nvl(varchar,varchar);
create function nvl(varchar,varchar) returns varchar as '
declare
inv alias for $1;
def alias for $2;
begin
return coalesce(inv,def);
end;' language 'plpgsql';
drop function nvl(float,float);
create function nvl(float,float) returns float as '
declare
inv alias for $1;
def alias for $2;
begin
return coalesce(inv,def);
end;' language 'plpgsql';
drop function nvl(integer,integer);
create function nvl(integer,integer) returns integer as '
declare
inv alias for $1;
def alias for $2;
begin
return coalesce(inv,def);
end;' language 'plpgsql';
select nvl(int_var,0) as nvl_integer,
nvl(ch_var,'NULLVAR') as nvl_char,
nvl(fl_var,0.0) as nvl_float,
nvl(vc_var,'NULLVAR') as nvl_varchar,
nvl(tx_var,'NULLVAR') as nvl_text from test order by nvl_integer;
when i run this example I get the following output:
openacs4=# i example.sql
DROP
CREATE
INSERT 66955 1
INSERT 66956 1
INSERT 66957 1
INSERT 66958 1
INSERT 66959 1
INSERT 66960 1
DROP
CREATE
DROP
CREATE
DROP
CREATE
nvl_integer | nvl_char | nvl_float | nvl_varchar | nvl_text
-------------+----------+-----------+-------------+----------
0 | t | 1 | uno | dos
1 | NULLVAR | 2 | foo | bar
2 | f | 0 | ocho | siete
3 | f | 3 | NULLVAR | foo
4 | t | 4 | baz | NULLVAR
5 | f | 5 | goodbye | hello
(6 rows)
openacs4=#
As you can see, it seems to work correctly. I had tried a similar test with decode before, but once I overloaded the decode for more than one type, pg couldn't resolve the type correctly without me explicitly adding a cast to the decode arguements. That now longer seems to be a problem with pg 7.1. I'll see if I can work up a test case for decode and post the results tomorrow.
