plpgsql - PostgreSQL sql-state: 42601 on SELECT INTO

Question

Well, i'm trying to create a simple procedure, that check if user with such login, and if no - adding new row in users table. But stuck with 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's 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 count is 0. When login is exist it correctly return 0; What the problem is? I'm even have no idea what is this. thx for help;

Answer 1

This instruction:

SELECT array_append(emails_array, new_user_email);

should fail because array_append returns the modified array and ignoring the result of the select is not allowed.

If you wanted to append into the source array, this should be:

SELECT array_append(emails_array, new_user_email) INTO emails_array;

However this is not even necessary. You may simplify your function body into:

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;