sql - 转换到自定义域 PostgreSQL

Question

我正在尝试创建自定义域类型以强制对多个表进行约束。
约束工作正常，但我还想添加将在实际转换和检查之前应用小写的自定义转换。

CREATE DOMAIN email AS varchar(80)
  CHECK (value ~ ('^[a-z0-9!#$%&''*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*@' ||
                  '(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?$'));

CREATE OR REPLACE FUNCTION text_as_email(text)
  RETURNS email
LANGUAGE SQL AS
$$ SELECT lower($1) :: email $$;

CREATE CAST ( text AS email )
WITH FUNCTION text_as_email(text) AS IMPLICIT;

领域和功能都按预期工作

SELECT 'abc@abc.com' :: email; -- no errors
SELECT 'abc@@abc.com' :: email; -- expected error
SELECT text_as_email('ABC@abc.com'); -- no errors, returns 'abc@abc.com'

但是转换大写失败

SELECT 'ABC@abc.com' :: email; -- gives an error

如果有任何想法，我将不胜感激。

Answer 1

来自 the documentation:

A cast to or from a domain type currently has no effect. Casting to or from a domain uses the casts associated with its underlying type.

创建类型转换时会警告您:

CREATE CAST ( text AS email )
WITH FUNCTION text_as_email(text) AS IMPLICIT;

WARNING:  cast will be ignored because the target data type is a domain