标签云

微信群

扫码加入我们

WeChat QR Code

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).Instead of dumping here the source code of my 2nd stored function, I have prepared 3 simple test functions below -CREATE OR REPLACE FUNCTION test1() RETURNS void AS$func$BEGINIF NOT check_user(42, 'user1', '56db1046fa7b664c9b3d05bf7413552a') THENRAISE NOTICE 'invalid user';ELSERAISE 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$BEGINIF NOT check_user(42, 'user2', '56db1046fa7b664c9b3d05bf7413552a') THENRAISE NOTICE 'invalid user';ELSERAISE 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$BEGINIF NOT check_user(42, 'user1', NULL) THENRAISE NOTICE 'invalid user';ELSERAISE 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?


In the 2nd statement (for the STRICT function) is it mandatory to use is not true part? Can't I just use if NOT check_user(42, 'user1', null) then raise notice 'invalid user'; end if; ? Thank you

2019年04月20日25分31秒

AlexanderFarber: expression is not true will return true for both false and null while not expression will only return true for false not for null which is your original problem.

2019年04月20日25分31秒