postgresql 创建函数时在“int”处或附近的 postgres 错误语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7674031/
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
postgres error syntax error at or near "int" when creating a function
提问by Shiver
I am very new to postgres. I got this error when try to run the following script:
我对 postgres 很陌生。尝试运行以下脚本时出现此错误：
CREATE OR REPLACE FUNCTION xyz(text) RETURNS INTEGER AS
'DECLARE result int;
BEGIN
SELECT count(*) into result from tbldealercommissions
WHERE
txtdealercode = ;
if result < 1 then returns 1;
else returns 2 ;
end if;
END;
'
LANGUAGE sql VOLATILE;
The error is
错误是
ERROR: syntax error at or near "int"
LINE 3: 'DECLARE result int;
not sure what cause this error. Any help is appreciated.
不知道是什么导致了这个错误。任何帮助表示赞赏。
回答by A.H.
This is unsuitable:
这是不合适的：
LANGUAGE sql
LANGUAGE sql
use this instead:
改用这个：
LANGUAGE plpgsql
LANGUAGE plpgsql
The syntax you are trying to use is not pure SQL language but the procedural PL/pgSQL language. In PostgreSQL you can install different languages and PL/pgSQL is only primus inter pares in that regard. This also means that you might get the error message, that this language is not installed. In that case use
您尝试使用的语法不是纯 SQL 语言，而是过程 PL/pgSQL 语言。在 PostgreSQL 中，您可以安装不同的语言，而 PL/pgSQL 只是这方面的主要部分。这也意味着您可能会收到错误消息，即未安装此语言。在这种情况下使用
CREATE LANGUAGE plpgsql;
which actives it. Depending on the version of PostgreSQL you might need superuser rights to do this step.
激活它。根据 PostgreSQL 的版本，您可能需要超级用户权限才能执行此步骤。
Have fun.
玩得开心。
回答by mu is too short
Not only are you using the wrong language (as noted by A.H.) but there is returnskeyword, you want return. You might want to use a different delimiter to avoid running into problems with string literals in your functions, $$is pretty common. I think your function should look more like this:
您不仅使用了错误的语言（如 AH 所指出的），而且还有returns关键字，您想要return. 您可能希望使用不同的分隔符来避免在函数中遇到字符串文字问题，$$这很常见。我认为你的函数应该更像这样：
CREATE OR REPLACE FUNCTION xyz(text) RETURNS INTEGER AS $$
DECLARE result int;
BEGIN
select count(*) into result
from tbldealercommissions
where txtdealercode = ;
if result < 1 then return 1;
else return 2;
end if;
END;
$$ LANGUAGE plpgsql VOLATILE;
