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

[Alexander Farber <alexander.farber@xxxxxxxxx>]

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 -

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

The 1st function works as expected and prints "valid user".

        CREATE OR REPLACE FUNCTION test2() RETURNS void AS
        $func$
        BEGIN
                IF NOT check_user(42, 'user2', '56db1046fa7b664c9b3d05bf7413552a') THEN
                        RAISE NOTICE 'invalid user';
                ELSE
                        RAISE NOTICE 'valid user';
                END IF;
        END
        $func$ LANGUAGE plpgsql;

The 2nd function works as expected and prints "invalid user".

        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.

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