I am facing an absolutely weird problem that feels much like a Postgres bug than an algorithm problem.
I have this function:
CREATE FUNCTION sp_connect(mail character varying, passwd character varying, role character varying) RETURNS json LANGUAGE plpgsql STABLE AS $$ DECLARE user_info record; BEGIN IF role = 'Role1' THEN SELECT u.id, r.name INTO user_info FROM users u INNER JOIN users_roles ur ON ur.user_id = u.id INNER JOIN roles r ON ur.role_id = r.id WHERE u.email = mail AND u.password = encode(digest(CONCAT(passwd, u.password_salt), 'sha512'), 'hex') AND r.name = 'Role1'; ELSIF role = 'Role2' THEN SELECT h.id, 'Role1' AS name INTO user_info FROM history h WHERE h.email = mail AND h.password = encode(digest(CONCAT(passwd, h.password_salt), 'sha512'), 'hex'); ELSE RAISE 'USER_NOT_FOUND'; END IF; IF NOT FOUND THEN RAISE 'USER_NOT_FOUND'; ELSE RETURN row_to_json(row) FROM (SELECT user_info.id AS id, user_info.name AS role) row; END IF; END; $$;
The problem I'm facing is when I use this function to log in with a Role1-user, then when I use it with a Role2-user, I get this error message:
type of parameter 7 (character varying) does not match that when preparing the plan (unknown)
Which is... well, I just don't understand where does it come from. If you wipe the database and change the login order (i.e. Role2 then Role1), this time, Role1 gets the error.
Strange issue, strange solutions... If I just use
ALTER FUNCTION sp_connect but without modify anything inside the function, then magically, the two roles can login without any problem. I also tried this solution:
IF NOT FOUND THEN RAISE 'USER_NOT_FOUND'; ELSE IF role = 'Seeker' THEN RETURN row_to_json(row) FROM (SELECT user_info.id AS id, user_info.name AS role) row; ELSE RETURN row_to_json(row) FROM (SELECT user_info.id AS id, user_info.name AS role) row; END IF;
And by adding an
ELSE that is absolutely useless and use the same RETURN-clause, this does not trigger any error.
I know DBA StackExchange is not for developers but this kind of problem seems to be more like a caching problem or whatever. Can somebody can tell me if I am doing something wrong with PostgreSQL functions? Or where I may get help with this weird problem?