Postgres function for NVL



  • I'm trying to have 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...
    

    Would love some help fixing this.



  • The errors results from a misunderstanding of the use of the anyelement type. Read in https://www.postgresql.org/docs/current/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC :

    Each position (either argument or return value) declared as anyelement 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 https://stackoverflow.com/a/40300936/1995738 :

    create or replace function nvl (anyelement, anyelement)
    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 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)
    $$;
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2