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?
