Re: overloaded functions and NULL

Tom Lane wrote:
 > Kris Jurka <books@ejurka.com> writes:
 >
 >>The below code creates overloaded
 >>functions that do not produce this error when called with a NULL argument.
 >
 >
 >>jurka=# CREATE FUNCTION g(int) RETURNS int AS 'SELECT 1;' LANGUAGE sql;
 >>CREATE FUNCTION
 >>jurka=# CREATE FUNCTION g(float) RETURNS int AS 'SELECT 2;' LANGUAGE sql;
 >>CREATE FUNCTION
 >
 >
 > float (a/k/a float8) is the preferred type in the numeric hierarchy,
 > so it will win in a tug-of-war against int.  There are other cases
 > where it would lose (eg, had you declared g(text)).  The objective
 > of the type rules is most certainly not to fail in any ambiguous
 > situation --- if we did, we'd have a completely unusable system.

I have to had that in normal cases the g argument type is a known type
so all is automagically solved:


CREATE OR REPLACE FUNCTION sp_bar ( INTEGER )
RETURNS INTEGER AS'
BEGIN
   RAISE NOTICE ''INTEGER'';
   return 0;
END;
' LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_bar ( FLOAT )
RETURNS INTEGER AS'
BEGIN
   RAISE NOTICE ''FLOAT'';
   return 0;
END;
' LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_foo (  )
RETURNS INTEGER AS'
DECLARE
   my   INTEGER := 4;
BEGIN
   perform sp_bar( my );
   my = NULL;
   perform sp_bar( my );

   return 0;
END;
' LANGUAGE 'plpgsql';



# select sp_foo();
NOTICE:  INTEGER
CONTEXT:  PL/pgSQL function "sp_foo" line 4 at perform
NOTICE:  INTEGER
CONTEXT:  PL/pgSQL function "sp_foo" line 6 at perform
  sp_foo
--------
       0
(1 row)




AS the OP can see even calling sp_bar with a null value then
the correct function is called.



Regards
Gaetano Mendola