How to return data in login function in PL/pgSQL? I want to return table where email and password matches. Email is unique - Postgresql

JaySardar
January 17, 2023
236 views
0 votes
2 Answers

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');

Answers

- Jonxag
- January 17, 2023 at 9:10 am
- 0 votes
0
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.

- a_horse_with_no_name
- January 17, 2023 at 5:18 pm
- 0 votes
0
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.