PostgreSQL sql-state: 42601 on SELECT INTO
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20282639/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL sql-state: 42601 on SELECT INTO
提问by GeraldIstar
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 ;
new_user_password ALIAS FOR ;
new_user_email ALIAS FOR ;
new_user_first_name ALIAS FOR ;
new_user_last_name ALIAS FOR ;
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;
它在 SELECT INTO 上返回 sql-state: 42601。但如果只有计数为 0。当登录存在时，它正确返回 0；问题是什么？我什至不知道这是什么。谢谢你的帮助；
回答by Daniel Vérité
This instruction:
本指令：
SELECT array_append(emails_array, new_user_email);
should fail because array_appendreturns the modified array and ignoring the result of the select is not allowed.
应该失败，因为array_append返回修改后的数组并忽略选择的结果是不允许的。
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;
回答by Erwin Brandstetter
In addition to what @Daniel already answered, don't use the outdated ALIASfor naming function parameters. Quoting the manual here:
除了@Daniel 已经回答的内容之外，不要使用过时的ALIAS来命名函数参数。在这里引用手册：
It's best to use it only for the purpose of overriding predetermined names.
最好仅将其用于覆盖预先确定的名称。
Use argument namesinstead:
改用参数名称：
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;
