The function doesn’t throw any error nor does it show any return value.
CREATE OR REPLACE FUNCTION login(iemail VARCHAR,ipassword VARCHAR)
RETURNS TABLE(id INTEGER, name VARCHAR, lastName VARCHAR, age INTEGER, mobile VARCHAR,email VARCHAR)
LANGUAGE 'plpgsql'
AS $$
#variable_conflict use_variable
BEGIN
RETURN QUERY SELECT id, name, lastName, age, mobile,email from usertable WHERE email = iemail AND password = ipassword;
END;
$$;
Below query gives me a return value. So, I know my query statement is right. Also, return type of variables are also checked.
SELECT id, name, lastName, age, mobile,email from usertable
WHERE email='[email protected]' AND password ='passwords';
i am calling the function with:
SELECT * FROM login('[email protected]','passwords');
2
Answers
Looks more like you are not calling the function correctly , should be something like this to call it.
The
#variable_conflict use_variable
is the reason. As all your output variables (=columns) have the same name as the table columns, Postgres returns the output "variables" which are not initialized.Use an explicit table reference inside the function’s query to avoid the name clash:
Note that for encapsulating a simple query into a function a
language sql
function is typically the better choice – it also avoids the clash between variables and column names: