Thread: overloaded functions and NULL
I thought all ambiguous function calls would generate an error: ERROR: function g("unknown") is not unique HINT: Could not choose a best candidate function. You may need to add explicit type casts. but this doesn't seem to be the case. 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 jurka=# SELECT g(NULL); g --- 2 (1 row) Kris Jurka
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. regards, tom lane
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