Re: How to ensure that a stored function always returns TRUE or FALSE?

On 3/2/16, Alexander Farber <alexander.farber@gmail.com> wrote:
>
>         CREATE OR REPLACE FUNCTION check_user(
>                 in_social integer,
>                 in_sid varchar(255),
>                 in_auth varchar(32))
>                 RETURNS boolean AS
>         $func$
>                 SELECT MD5('secret word' || in_social || in_sid) = in_auth;
>         $func$ LANGUAGE sql IMMUTABLE;
>
>
>         CREATE OR REPLACE FUNCTION test3() RETURNS void AS
>         $func$
>         BEGIN
>                 IF NOT check_user(42, 'user1', NULL) THEN
>                         RAISE NOTICE 'invalid user';
>                 ELSE
>                         RAISE NOTICE 'valid user';
>                 END IF;
>         END
>         $func$ LANGUAGE plpgsql;
>
> The 3rd function does NOT work as expected and prints "valid user".
>
> This happens because check_user() returns NULL instead of a boolean value.

I guess it is enough to swap blocks inside of IF statement and reverse
its condition:

        CREATE OR REPLACE FUNCTION test3() RETURNS void AS
        $func$
        BEGIN
                IF check_user(42, 'user1', NULL) THEN
                        RAISE NOTICE 'valid user';
                ELSE
                        RAISE NOTICE 'invalid user';
                END IF;
        END
        $func$ LANGUAGE plpgsql;

would give "invalid user". NULL works as FALSE at the top of IF expressions.

https://en.wikipedia.org/wiki/Null_(SQL)#Comparisons_with_NULL_and_the_three-valued_logic_.283VL.29