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
