On Wednesday, May 6, 2015, Gunnar "Nick" Bluth <
gunnar.bluth@pro-open.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.