Thread: Unexpected function behaviour with NULL and/or default NULL parameters
Unexpected function behaviour with NULL and/or default NULL parameters
From
"Gunnar \"Nick\" Bluth"
Date:
-----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$
SELECT json_agg(selected) FROM (
SELECT * FROM (
SELECT *, 1 AS locale_specific FROM terms_and_conditions WHERE
locale = $3 ORDER BY version DESC LIMIT 1
) specific
UNION
SELECT * FROM (
SELECT *, 0 AS locale_specific FROM terms_and_conditions WHERE
locale = 'default' ORDER BY version DESC LIMIT 1
) unspecific
ORDER BY locale_specific DESC
LIMIT 1
) selected;
$function$
This works fine when I give valid userid, sessionid and locale:
=# select
get_current_tac(userid:=38,sessionid:='79993643-ec3c-0359-f603-069b543ce4a8'::uuid,locale:='en');
get_current_tac
-
----------------------------------------------------------------------------------------------------------------------------------------------------------
[{"locale":"default","version":1,"updated_at":"2015-05-05T14:04:17.246684+02:00","terms_and_conditions":"Hier kommen
dieAGB rein","locale_specific":0}]
(1 row)
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)
I'm completely puzzled by this behaviour; at least it should cast the
"locale_specific" value into the JSON output, shouldn't it?
What am I missing? Any hints appreciated!
- --
Gunnar "Nick" Bluth
RHCE/SCLA
Mobil +49 172 8853339
Email: gunnar.bluth@pro-open.de
_____________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.
Ten years later they are choosing Windows over UNIX.
What part of that message aren't you getting? - Tom Payne
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.22 (MingW32)
iQEcBAEBAgAGBQJVScPtAAoJEBAQrmsyiTOMRpwIANOALdxqB3V35IaYyXg/BLJz
Vnfgz6tsq97vDPvlCq7J/3ratnsJJqB218tGWX9jNr5Jcs/Ak0ZfZFcGHBE/YFxJ
2H6+30CHFeHVdGRkAF4Lu0rDcXoABhe0vIwfpQpRileXPpukQL9+oyE7nNI5H5dn
cb6UzqjxLEu/LgEZUgh5M3P680gPWm2gx1ojBu/a1I6i7pZiBVGxH0dtcFn1Gwsh
CFb5iByrGl+ghuxge4N1Kc02RhgDhdgedV0Rfj5oD6PuGuTmFarfbdZpc057y553
eo8jllZFE1Qoj1pWvZSL+gLQVp3bzoy3XxtbDGFZXNB7wfSSCs5t61HjaoMM7lk=
=ae0S
-----END PGP SIGNATURE-----
Attachment
Re: Unexpected function behaviour with NULL and/or default NULL parameters
From
"David G. Johnston"
Date:
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.
Re: Unexpected function behaviour with NULL and/or default NULL parameters
From
"David G. Johnston"
Date:
Wednesday, May 6, 2015, Gunnar "Nick" Bluth <gunnar.bluth@pro-open.de> wrote:
-----BEGIN PGP SIGNED MESSAGE-----
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$
SELECT json_agg(selected) FROM (
SELECT * FROM (
SELECT *, 1 AS locale_specific FROM terms_and_conditions WHERE
locale = $3 ORDER BY version DESC LIMIT 1
) specific
UNION
SELECT * FROM (
SELECT *, 0 AS locale_specific FROM terms_and_conditions WHERE
locale = 'default' ORDER BY version DESC LIMIT 1
) unspecific
ORDER BY locale_specific DESC
LIMIT 1
) selected;
$function$
Also, I don't know why you would need "security definer" but defining the functions as being "immutable" is a flat out lie. Combined with your misuse of "strict" I would suggest you read up on function creation and usage in the documentation.
It also looks odd to define the OUT parameter along with "RETURNS json" - unless it is giving you some kind of output column name benefit that I cannot remember at the moment.
David J.
Re: Unexpected function behaviour with NULL and/or default NULL parameters
From
"Gunnar \"Nick\" Bluth"
Date:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Am 06.05.2015 um 09:57 schrieb David G. Johnston: Ooops, accidentaly replied to David directly... > Wednesday, May 6, 2015, Gunnar "Nick" Bluth > <gunnar.bluth@pro-open.de <mailto:gunnar.bluth@pro-open.de>> > wrote: > > -----BEGIN PGP SIGNED MESSAGE----- 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$ SELECT > json_agg(selected) FROM ( SELECT * FROM ( SELECT *, 1 AS > locale_specific FROM terms_and_conditions WHERE locale = $3 ORDER > BY version DESC LIMIT 1 ) specific UNION SELECT * FROM ( SELECT *, > 0 AS locale_specific FROM terms_and_conditions WHERE locale = > 'default' ORDER BY version DESC LIMIT 1 ) unspecific ORDER BY > locale_specific DESC LIMIT 1 ) selected; $function$ > > > Also, I don't know why you would need "security definer" but > defining Nothing in the DB is accessible to the apache user directly, thus the security definer. > the functions as being "immutable" is a flat out lie. Combined > with your misuse of "strict" I would suggest you read up on > function creation and usage in the documentation. Well, you're of course right... wrote a bunch of real immutable strict functions these last days, so that just sticked. Always think before writing... w/out the two, the function behaves as expected. I'll review the other functions as well now! > It also looks odd to define the OUT parameter along with "RETURNS > json" - unless it is giving you some kind of output column name > benefit that I cannot remember at the moment. That's how it was put into my vim when doing an "\ef"... Cheers, - -- Gunnar "Nick" Bluth RHCE/SCLA Mobil +49 172 8853339 Email: gunnar.bluth@pro-open.de _____________________________________________________________ In 1984 mainstream users were choosing VMS over UNIX. Ten years later they are choosing Windows over UNIX. What part of that message aren't you getting? - Tom Payne -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.22 (MingW32) iQEcBAEBAgAGBQJVSfdIAAoJEBAQrmsyiTOMN84IAMhgo3blO5oZqTJjyHnfznYW MvKx5NuZkTQ4xphzlD2XdEGEASzb1FEUShKw1OB7TQ7E8O0aq19oXqdVIOyL0oVi GCZgT5uDEY7WpIsP98qaO0GEZ/Tc6hUUbH6DLB6fhRdnrNQPoSssi682HgIvg83e PDjgkS4+Zi2CWquF4jDPeaMGjp/+hFUtecZaYl3XqoD3GWtbj9T3LidFBfZPj0iV V7qGvbcpu1r0bYRmA5dXiVkaFtq2xqBZn2T1S2uzd2giqCIm8L1uXTAEFt/fNlvC wyu9mzfQUA0lyPLyIbUFfSb1Csgb7uSZXVOGgc++rgps5wZ+ZssCcx+4VcdF09A= =1i6y -----END PGP SIGNATURE-----