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

Wednesday, May 6, 2015, Gunnar "Nick" Bluth <gunnar(dot)bluth(at)pro-open(dot)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.