PostgreSQL sql-state:SELECT INTO上为42601 [英] PostgreSQL sql-state: 42601 on SELECT INTO
问题描述
好吧,我正在尝试创建一个简单的过程,检查是否有这样的登录用户,如果没有,请在用户表中添加新行.但是遇到了意外问题.
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;
在SELECT INTO上返回sql-state:42601.但是,如果仅count为0.当存在登录名时,它正确返回0;否则,返回0. 到底是什么问题?我什至不知道这是什么. 寻求帮助;
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;
推荐答案
此说明:
SELECT array_append(emails_array, new_user_email);
应该失败,因为array_append
返回修改后的数组,并且不允许忽略选择结果.
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;
这篇关于PostgreSQL sql-state:SELECT INTO上为42601的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!