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

On 3/2/16, Alexander Farber <alexander(dot)farber(at)gmail(dot)com> wrote:
> Good morning,
>
> with the following stored function I would like to validate user data:
>
> 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;
>
> I am going to call it while looping through a JSON array of objects in
> another stored functions - and will RAISE EXCEPTION if it returns FALSE for
> any of the JSON objects (and thus rollback the whole transaction).
>
> I have prepared 3 simple test functions below -
>
> <overquoting>
>
> 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.
For more information see[1].

> COALESCE could be wrapped around the check_user() call in the
> IF-statement... but is there maybe a nicer way to solve this problem?
>
> Thank you
> Alex
>

[1]https://en.wikipedia.org/wiki/Null_(SQL)#Comparisons_with_NULL_and_the_three-valued_logic_.283VL.29
--
Best regards,
Vitaly Burovoy