PostgreSQL sql-state: 42601 on SELECT INTO

Question

Well, I'm trying to create a simple procedure that checks if the user has such a login, and if not, adds a new row to the users table. But stuck with an unexpected problem.

CREATE OR REPLACE FUNCTION register_user(character varying, character varying, character varying,character varying,character varying) RETURNS bigint AS $BODY$ DECLARE new_user_login ALIAS FOR $1; new_user_password ALIAS FOR $2; new_user_email ALIAS FOR $3; new_user_first_name ALIAS FOR $4; new_user_last_name ALIAS FOR $5; login_exist bigint; new_user_id bigint; emails_array character varying array; --yep, it array of emails BEGIN SELECT INTO login_exist count(login) FROM users WHERE users.login = new_user_login; IF (login_exist = 0) THEN SELECT array_append(emails_array, new_user_email); INSERT INTO users (login,password,emails,first_name,last_name) VALUES (new_user_login,new_user_password,emails_array,new_user_first_name,new_user_last_name) RETURNING id INTO new_user_id; RETURN new_user_id; ELSE RETURN 0; END IF; END $BODY$ LANGUAGE plpgsql VOLATILE COST 100; 

It returns sql-state: 42601 on SELECT INTO. But if only the number is 0. When the login exists, it correctly returns 0; What is the problem? I don’t even know what it is. thanks for reference;

Answer 1

This instruction:

 SELECT array_append(emails_array, new_user_email); 

should fail because array_append returns the changed array and ignoring the selection result is not allowed.

If you want to add to the source array, this should be:

 SELECT array_append(emails_array, new_user_email) INTO emails_array; 

However, this is not even necessary. You can simplify the body of your function:

 BEGIN INSERT INTO users (login,password,emails,first_name,last_name) SELECT new_user_login,new_user_password,array[new_user_email],new_user_first_name,new_user_last_name WHERE NOT EXISTS (select 1 FROM users WHERE users.login = new_user_login) RETURNING id INTO new_user_id; RETURN coalesce(new_user_id,0); END; 

Answer 2

In addition to what @Daniel already answered , do not use legacy ALIAS for naming function parameters. Guidance note here :

It is best to use it only to override given names.

Use the argument names :

 CREATE OR REPLACE FUNCTION register_user(_login text , _password text , _email text , _first_name text , _last_name text) RETURNS bigint AS $func$ DECLARE _id bigint; BEGIN INSERT INTO users (login, password, emails, first_name, last_name) SELECT _login, _password, ARRAY[_email], _first_name, _last_name WHERE NOT EXISTS (SELECT 1 FROM users WHERE login = _login) RETURNING id INTO _id; RETURN COALESCE(_id, 0); END $func$ LANGUAGE plpgsql;