Search Postgresql Archives

Unexpected function behaviour with NULL and/or default NULL parameters

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



-----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 die AGB 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@xxxxxxxxxxx
_____________________________________________________________
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: 0x3289338C.asc
Description: application/pgp-keys

Attachment: 0x3289338C.asc.sig
Description: Binary data

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux