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

-----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(dot)bluth(at)pro-open(dot)de <mailto: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

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(dot)bluth(at)pro-open(dot)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-----