postgresql 用于验证电子邮件地址的 Postgres 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4908211/
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 Function to Validate Email Address
提问by Nick Binnet
A check constraint which would call a function to validate email addresses is not working fine for me.
调用函数来验证电子邮件地址的检查约束对我来说不起作用。
CREATE OR REPLACE FUNCTION f_IsValidEmail(text) returns BOOLEAN AS
'select ~ ''^[^@\s]+@[^@\s]+(\.[^@\s]+)+$'' as result
' LANGUAGE sql;
SELECT f_IsValidEmail('[email protected]');
The function is returning false, which should be true. I have tried a couple of other regexs but in vain. Can anyone point out what's wrong with this function?
该函数返回 false，这应该是 true。我尝试了其他几个正则表达式，但徒劳无功。谁能指出这个功能有什么问题？
回答by Evan Carroll
A bunch of these answers are closeto the right way. These are the points for my submission.
一堆这些答案接近正确的方式。这些是我提交的要点。
- You want to use a domain -- NOT the rule system.
- You do NOT want to validate these email addresses with a regex.(Update Mar 2017: not really true anymore)
- 您想使用域——而不是规则系统。
- 您不想使用正则表达式验证这些电子邮件地址。（2017 年 3 月更新：不再是真的）
I show two methods of how to do this the right on DBA.StackExchange.com. Both to check for the MX-record, and also using the HTML5 spec. Here is the short and sweet.
我在 DBA.StackExchange.com 上展示了如何正确执行此操作的两种方法。既要检查 MX 记录，又要使用 HTML5 规范。这是简短而甜蜜的。
CREATE EXTENSION citext;
CREATE DOMAIN email AS citext
CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$' );
SELECT '[email protected]'::email;
SELECT CAST('[email protected]' AS email);
For more information I highlysuggest you read the answer in full. In the answer, I also show how you create a DOMAINover Email::Valid, and explain why I no longer use that method.
有关更多信息，我强烈建议您完整阅读答案。在答案中，我还展示了您如何创建DOMAINoverEmail::Valid，并解释为什么我不再使用该方法。
回答by Scott Marlowe
Before you go putting a lot of effort into this, the thing you want to do is make sure you're not kicking out valid email addresses. There's all kinds of insane rules for can or can't be in an email address, and if you get it wrong, in the wrong direction, a user with a perfectly valid email address might get rejected by your system.
在您为此付出很多努力之前，您要做的事情是确保您没有踢出有效的电子邮件地址。对于可以或不能在电子邮件地址中存在各种疯狂的规则，如果您弄错了，在错误的方向上，具有完全有效电子邮件地址的用户可能会被您的系统拒绝。
The best way to determine if an email address is valid is to use it as part of a registration process where email is REQUIRED. Anything else is a lot of work for little gain.
确定电子邮件地址是否有效的最佳方法是将其用作需要电子邮件的注册过程的一部分。其他任何事情都是徒劳无功。
回答by Peter Eisentraut
I recommend a solution using PL/Perl and the Email::Address module. Something like the following:
我推荐使用 PL/Perl 和 Email::Address 模块的解决方案。类似于以下内容：
CREATE OR REPLACE FUNCTION email_valid(email text) RETURNS bool
LANGUAGE plperlu
AS $$
use Email::Address;
my @addresses = Email::Address->parse($_[0]);
return scalar(@addresses) > 0 ? 1 : 0;
$$;
See also http://wiki.postgresql.org/wiki/Email_address_parsing.
回答by Mike Sherrill 'Cat Recall'
If you can figure out a regular expression that matches email addresses to your satisfaction, consider whether it might not be more useful as a domain rather than a check constraint.
如果您能找出一个符合您满意的电子邮件地址的正则表达式，请考虑它作为域而不是检查约束是否更有用。
For some of the pitfalls see regular-expressions.info.
对于一些陷阱，请参阅正则表达式.info。
回答by earksiinni
Don't attempt to create a regex to validate e-mails!
不要尝试创建正则表达式来验证电子邮件！
It is notoriously difficult to accomplish. Here's a better solution:
这是出了名的难以完成。这是一个更好的解决方案：
Assuming that you have Perl installed on your database host, install the Email::Valid module on the same host using CPAN:
假设您在数据库主机上安装了 Perl，使用 CPAN 在同一台主机上安装 Email::Valid 模块：
you@localhost$ cpan Email::Valid
Then make sure that you have PL/Perl installed. Connect to your database in psql and add plperlu as a language:
然后确保您安装了 PL/Perl。在 psql 中连接到您的数据库并添加 plperlu 作为语言：
CREATE EXTENSION plperlu;
(Keep in mind that this is an untrusted language, so you'll be giving your db direct file access, which could pose a security risk if someone were to insert malicious code into your Perl modules or into the db functions. However, you need to do it for the next step.)
（请记住，这是一种不受信任的语言，因此您将授予 db 直接文件访问权限，如果有人将恶意代码插入您的 Perl 模块或 db 函数中，这可能会带来安全风险。但是，您需要为下一步做。）
Add the following function to your database:
将以下函数添加到您的数据库中：
CREATE FUNCTION validate_email() RETURNS trigger AS $$
use Email::Valid;
return if Email::Valid->address($_TD->{new}{email});
elog(ERROR, "invalid email address $_TD->{new}{email} inserted into $_TD->{table_name}(email)");
return "SKIP";
$$ LANGUAGE plperlu;
Add a trigger constraint to your table on your column (assuming that your table is named "users" and your column is named "email"):
在您的列上为您的表添加触发器约束（假设您的表名为“users”并且您的列名为“email”）：
CREATE TRIGGER users_valid_email_trig
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE PROCEDURE validate_email();
And you're done!
大功告成！
This solution uses the Email::Valid Perl module to handle validation, which in turn relies on a regex to ensure RFC 822 compliance. However, it is a monster of a regex, so don't try to come up with your own.
此解决方案使用 Email::Valid Perl 模块来处理验证，而后者又依赖于正则表达式来确保符合 RFC 822。然而，它是一个正则表达式的怪物，所以不要试图想出你自己的。
If you feel uncomfortable with enabling plperlu instead of plain plperl, you could probably port the relevant functions into your database.
如果您对启用 plperlu 而不是普通的 plperl 感到不舒服，您可以将相关函数移植到您的数据库中。
回答by a_horse_with_no_name
Works for me:
对我有用：
psql (9.0.2)
Type "help" for help.
postgres=> CREATE OR REPLACE FUNCTION "f_IsValidEmail"(text) returns BOOLEAN AS
postgres-> 'select ~ ''^[^@\s]+@[^@\s]+(\.[^@\s]+)+$'' as result
postgres'> ' LANGUAGE sql;
CREATE FUNCTION
postgres=> commit;
COMMIT
postgres=> SELECT "f_IsValidEmail"('[email protected]');
f_IsValidEmail
----------------
t
(1 row)
postgres=>
