-----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-----
