Postgres column does not exist in function

Question

In my query I am getting error message as below:

ERROR: column "pw" does not exist

LINE 1: SELECT (pw = $2) FROM user ^ QUERY: SELECT (pw = $2) FROM user WHERE user_name = $1

CONTEXT: PL/pgSQL function public.check_password(text,text) line 4 at SQL statement

SQL state: 42703

CREATE OR REPLACE FUNCTION check_password(i VARCHAR, pwd VARCHAR)
RETURNS BOOLEAN AS $$
DECLARE passed Boolean;
    BEGIN
    
        SELECT (u.pw = pwd) into passed
        From public.user u
        where u.user_name = i;
        return passed;
        
    END;
    
$$ LANGUAGE plpgsql;

select check_password('userf','12345');

Is it supposed to be u.pwd? If not add to your question the table definition for public.user. — Adrian Klaver
– Adrian Klaver, Commented May 9, 2022 at 4:22

jian · Accepted Answer · 2022-05-09 04:34:40Z

0

Your function works, but You may need consider the null case. demo

CREATE OR REPLACE FUNCTION check_password(i text, pwd text)
RETURNS BOOLEAN AS $$
DECLARE passed Boolean;
    BEGIN
    
        SELECT (u.pw = pwd) into passed
        From public.user u
        where u.user_name = i;
        
        if passed is null then  passed := false; end if;
        
        return passed;
        
    END;
    
$$ LANGUAGE plpgsql;

answered May 9, 2022 at 4:34

jian

5,0131 gold badge29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

Or simpler: return passed is true

Collectives™ on Stack Overflow

Postgres column does not exist in function

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related