skip to Main Content

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


  1. Looks more like you are not calling the function correctly , should be something like this to call it.

    SELECT * INTO some_variable FROM login('[email protected]', 'passwords');
    
    Login or Signup to reply.
  2. 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:

    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 $$
    BEGIN
      RETURN QUERY 
        SELECT u.id, u.name, u.lastname, u.age, u.mobile, u.email 
        from usertable u
        WHERE u.email = iemail 
        AND u.password = ipassword;
    END;
    $$;
    

    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:

    CREATE OR REPLACE FUNCTION login(iemail VARCHAR,ipassword VARCHAR)
      RETURNS TABLE(id INTEGER, name VARCHAR, lastName VARCHAR, age INTEGER, mobile VARCHAR, email VARCHAR)
    LANGUAGE sql
    AS $$
      SELECT id, name, lastname, age, mobile, email 
      from usertable 
      WHERE email = iemail 
      AND password = ipassword;
    $$;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search