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.