What is the function of postgresql nvl?
I'm trying to have an NVL function in postgres.
create or replace function nvl (anyelement, anyelement)
returns anyelement language sql as $$
select coalesce(cast( $1 as decimal), cast( $2 as decimal))
$$;
however this fails on me for the following examples:
testdb=> select nvl(1,2);
ERROR: return type mismatch in function declared to return integer
DETAIL: Actual return type is numeric.
CONTEXT: SQL function "nvl" during inlining
testdb=> SELECT nvl( sum(balance), 0 ) as b FROM db.bank WHERE user = 123;
ERROR: function nvl(numeric, integer) does not exist
LINE 1: SELECT nvl( sum(balance), 0 ) as b FROM db.bank...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
When I change it to:
create or replace function nvl (anyelement, anyelement)
returns anyelement language sql as $$
select case when $1 is null then $2 else $1 END
$$;
The first example works. But I still have failures with:
testdb=> SELECT nvl( sum(balance), 0 ) as b FROM db.bank WHERE user = 123;
ERROR: function nvl(numeric, integer) does not exist
LINE 1: SELECT nvl( sum(balance), 0 ) as b FROM db.bank...
The errors result from a misunderstanding of the use of the any element type. Read in the documentation:
Each position (either argument or return value) declared as an element is allowed to have any specific actual data type, but in any given call they must all be the same actual type.
So, you should use the function in the form it was proposed:
create or replace function nvl (any element, any element)
returns anyelement language sql as $$
select coalesce($1, $2)
$$;
and make sure that the actual type of arguments match exactly. If the column balance is numeric, the second argument also has to be numeric:
select nvl(sum(balance), 0.0)
-- or
select nvl(sum(balance), 0::numeric)
Update. The OP says:
I can not change the SQLs. only adjust the function.
In this case you cannot use anyelement arguments. You need to create function with numeric arguments:
drop function if exists postgresql nvl (anyelement, anyelement);
create or replace function nvl (numeric, numeric)
returns numeric language sql as $$
select coalesce($1, $2)
$$;
The drawback is that the function will work only for numeric and/or integer arguments. Postgres allows to overload functions, so you can additionally create functions for other types, e.g.:
create or replace function nvl (text, text)
returns text language sql as $$
select coalesce($1, $2)
$$;