Re: Unexpected function behaviour with NULL and/or default NULL parameters

On Wednesday, May 6, 2015, Gunnar "Nick" Bluth <gunnar(dot)bluth(at)pro-open(dot)de>
wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello,
>
> I'm experiencing odd behaviour with a function I wrote yesterday.
>
> Background: function is supposed to deliver some "terms and
> conditions" from a table; when the "locale" is found, deliver the
> highest version of that, otherwise, deliver the highest version of the
> "default" locale.
>
> CREATE OR REPLACE FUNCTION
> public.get_current_tac(userid bigint, sessionid uuid, locale character
> varying, OUT current_tac json)
> RETURNS json
> LANGUAGE sql
> IMMUTABLE STRICT SECURITY DEFINER
> AS $function$
>
>
> Then I realised I don't really need those first two parameters and
> applied default values (NULL). As $1 and $2 are not used, it should
> still work, right? Well, it returns one empty (!) row (behaviour is
> the same when declaring NULL as default values in the function header,
> can't show as there's another version with only "locale" as parameter):
>
> # select get_current_tac(userid:=null, sessionid:=null::uuid,
> locale:='en');
> get_current_tac
> - ------------------------
> <NULL>
> (1 row)
>
>
If you want to allow null to be passed to a function you shouldn't declare
it as STRICT...

David J.