用户定义的类型

您可以定义 用户自定义类型，这些是基于现有 Snowflake 数据类型 的新数据类型。例如，假设要为人员的年龄定义一列，并且希望将值限制为最多包含三位数字，并且小数点后没有数字。您可以定义一个名为 age 的数据类型，对应 NUMBER(3,0)。

用户定义类型是架构级对象，可用于所有可以使用类型的地方，包括列定义、函数和过程定义以及转换表达式。

用户定义的类型可以简化架构维护并提高数据质量。您可以定义一次用户定义类型，然后在多个对象中使用它。

您还可以使用用户定义的类型将相关数据字段分组到单个逻辑列中，而不是为字段使用多个列或表。例如，您可以为地址定义一种数据类型，即 结构化 OBJECT 类型，包含街道地址、城市、省/自治区/直辖市和 ZIP 代码。

用户定义类型所需的权限

要在架构中创建用户定义类型，您必须使用已被授予该架构 CREATE TYPE 权限的角色。

有关更多信息，请参阅 用户定义类型的访问控制要求。

用户定义类型的一般使用说明

  • 要更改用户定义类型的定义，请将其删除并重新创建。

    如果更改用户定义类型的定义：

    • 直接对使用该类型的表列进行操作的 SQL 语句可能会返回错误，包括 SELECT 语句和 DML 语句。但是，不直接对使用该类型的表列进行操作的 SQL 语句会正常运行。例如，如果表中包含名为 typed_column 的用户定义类型列，而 SELECT 语句在其 SELECT 列表中指定了其他列，则 SELECT 语句可以正常运行。要更正此问题，您可以修改 SQL 语句以使用基础 Snowflake 类型。

    • 对使用该类型的函数和存储过程的调用会返回错误。要更正此问题，请删除并重新创建函数和存储过程。

  • ALTER TABLE ...ALTER COLUMN 命令可以将列的数据类型从用户定义类型更改为兼容的 Snowflake 数据类型，或从 Snowflake 数据类型转换为用户定义类型。

  • 当您使用 OBJECT_CONSTRUCT 函数或 OBJECT 常量 构造要插入用户定义类型列的对象时，请将结果转换为该用户定义类型。

    有关示例，请参阅 对表列使用用户定义类型。

  • 当 :doc:`集合运算符</sql-reference/operators-query>`（例如 UNION、INTERSECT、EXCEPT）或 :doc:`条件表达式函数</sql-reference/expressions-conditional>`（例如 CASE、IFF、COALESCE、NVL 等）计算解析为用户定义类型值的表达式时，Snowflake 会使用操作数的基础基类型确定常见类型。默认情况下，结果的数据类型是此基本类型。如果希望结果是用户定义类型的值，请将最终表达式显式转换为用户定义类型。

    在集合运算或条件表达式函数中使用用户定义类型时，适用以下规则：

    • 用户定义的类型与其基类型不同，但在表达式类型解析中，它们会强制转换为基类型以查找通用类型。

    • 如果分支或操作数解析为单个 Snowflake 类型（例如，VARCHAR 或 NUMBER)，即结果类型。

    • 要保留用户定义类型或生成作为用户定义类型值的结果，请使用 CAST(expr AS user-defined type) 或 expr::user-defined type 转换 整个表达式。

    • 不兼容的基类型（例如，VARCHAR 和 NUMBER）遵循正常的 强制转换规则。如果不存在公共基类型，则返回错误。

    有关示例，请参阅 将集合运算符和条件表达式函数与用户定义类型结合使用。

  • 允许在函数 重载 中使用用户定义类型和兼容的 Snowflake 数据类型。也就是说，您可以为函数实参类型指定用户定义的类型，也可以为同名函数的实参类型指定兼容的 Snowflake 数据类型。

  • 如果将用户定义的类型指定为 SQL 用户定义的函数 (UDF)的 RETURN 类型或 Snowflake Scripting 存储过程，返回值必须在 UDF 主体或存储过程中显式转换为用户定义的类型。

  • 当用户定义类型被用作 UDF 或非 SQL（例如 Python 或 Java）语言编写的存储过程的参数或返回值时，用户定义的类型将被视为与其基类型相同。

  • 架构演化 不支持用户定义的类型。

转换用户定义的类型

用户定义的类型支持 数据类型转换，包括显式类型转换和隐式类型转换（强制转换）：

用户定义类型的显式转换

用户定义类型的值可转换为与其基类型相同的数据类型。例如，创建一个名为 age 的用户定义类型，该类型基于 NUMBER 类型：

CREATE TYPE age AS NUMBER(3,0);

如果值可以转换为用户定义类型的基类型，则该值可以转换为用户定义类型。例如，值 10 可以转换为 NUMBER 类型，因此您可以将值转换为 age 类型：

SELECT 10::age;

如果用户定义类型的基类型可以转换为其他数据类型，则可以将用户定义类型值转换为该数据类型。例如，NUMBER 值可以转换为 VARCHAR 类型，因此用户定义类型 age 的值 10 可以转换为 VARCHAR 类型：

SELECT 10::age::VARCHAR;

用户定义类型的强制转换

用户定义的类型值强制转换为其基类型。因此，在所有操作中，其行为与其基类型相同。例如，创建一个名为 age 的用户定义类型，该类型基于 NUMBER 类型，以及包含两列类型为 age 的表：

CREATE TYPE age AS NUMBER(3,0);

CREATE TABLE test_age_udf(a age, b age);

在表中插入值：

INSERT INTO test_age_udf VALUES (10, 20);

以下示例对表值执行加法运算，Snowflake 将 age 值强制转换为类型 NUMBER 的值以完成操作。该示例使用 SYSTEM$TYPEOF 函数以显示结果的数据类型：

SELECT a + b AS result,
       SYSTEM$TYPEOF(a + b) AS type
  FROM test_age_udf;

+--------+------------------+
| RESULT | TYPE             |
|--------+------------------|
|     30 | NUMBER(4,0)[SB1] |
+--------+------------------+

用户定义的数据类型的示例

以下示例展示了如何使用用户定义的类型：

对表列使用用户定义类型

在以下示例中，您将创建一个名为 address 的用户定义类型，然后在表中使用该类型：

  1. 创建基于 结构化 OBJECT 类型 的用户定义类型存储地址信息：

    CREATE TYPE address AS OBJECT(
  street VARCHAR(100),
  city VARCHAR(50),
  state_abbr CHAR(2),
  zip_code CHAR(10)
);

  2. 创建一个存储客户信息（包括地址）的表：

    CREATE TABLE customers_udt_test (
  cust_id VARCHAR NOT NULL,
  cust_name VARCHAR(100),
  cust_address address
);

  3. 在表中插入一行，并通过将 OBJECT 常量 类型转换为 address 类型来指定 cust_address 列的值：

    INSERT INTO customers_udt_test (cust_id, cust_name, cust_address)
  SELECT
    '1000',
    'Example1 Inc',
    {
      'street': '101 Snow Street',
      'city': 'San Francisco',
      'state_abbr': 'CA',
      'zip_code': '94102'
    }::address;

  4. 在表中插入一行，并通过调用 OBJECT_CONSTRUCT 函数并将返回值转换为 address 类型来指定 cust_address 列的值：

    INSERT INTO customers_udt_test (cust_id, cust_name, cust_address)
  SELECT
    '1001',
    'Example2 Inc',
    OBJECT_CONSTRUCT(
      'street', '555 Polar Bear Street',
      'city', 'New York',
      'state_abbr', 'NY',
      'zip_code', '10001'
    )::address;

  5. 在表中插入一行，通过将 OBJECT 常量转换为 OBJECT 类型（该类型是 address 类型的基类型）来指定 cust_address 列的值：通常更容易将 OBJECT 常量转换为用户定义的类型，但此示例表明 OBJECT 常量强制转换为用户定义的类型：

    INSERT INTO customers_udt_test (cust_id, cust_name, cust_address)
  SELECT
    '1002',
    'Example3 Inc',
    {
      'street': '909 Flake Street',
      'city': 'Seattle',
      'state_abbr': 'WA',
      'zip_code': '98109'
    }::OBJECT(
         street VARCHAR(100),
         city VARCHAR(50),
         state_abbr CHAR(2),
         zip_code CHAR(10));

  6. 要显示插入的行，请查询表：

    SELECT * FROM customers_udt_test;

    +---------+--------------+--------------------------------------+
| CUST_ID | CUST_NAME    | CUST_ADDRESS                         |
|---------+--------------+--------------------------------------|
| 1000    | Example1 Inc | {                                    |
|         |              |   "street": "101 Snow Street",       |
|         |              |   "city": "San Francisco",           |
|         |              |   "state_abbr": "CA",                |
|         |              |   "zip_code": "94102"                |
|         |              | }                                    |
| 1001    | Example2 Inc | {                                    |
|         |              |   "street": "555 Polar Bear Street", |
|         |              |   "city": "New York",                |
|         |              |   "state_abbr": "NY",                |
|         |              |   "zip_code": "10001"                |
|         |              | }                                    |
| 1002    | Example3 Inc | {                                    |
|         |              |   "street": "909 Flake Street",      |
|         |              |   "city": "Seattle",                 |
|         |              |   "state_abbr": "WA",                |
|         |              |   "zip_code": "98109"                |
|         |              | }                                    |
+---------+--------------+--------------------------------------+

  7. 查询表，并使用冒号运算符仅显示 address 数据中的 zip_code 值：

    SELECT cust_id,
       cust_name,
       cust_address:zip_code
  FROM customers_udt_test;

    +---------+--------------+-----------------------+
| CUST_ID | CUST_NAME    | CUST_ADDRESS:ZIP_CODE |
|---------+--------------+-----------------------|
| 1000    | Example1 Inc | 94102                 |
| 1001    | Example2 Inc | 10001                 |
| 1002    | Example3 Inc | 98109                 |
+---------+--------------+-----------------------+

将集合运算符和条件表达式函数与用户定义类型结合使用

当 集合运算符 或 条件表达式函数 计算 Snowflake 类型和用户定义类型的值时，这些类型必须兼容且可以强制转换为单一类型。生成的输出属于 Snowflake 基类型，除非显式转换为用户定义的类型。有关更多信息，请参阅 用户定义类型的一般使用说明。

本节中的示例将集合运算符和条件表达式与用户定义的类型结合使用。首先，创建多个具有各种基类型的用户定义类型：

CREATE TYPE us_zipcode AS VARCHAR;
CREATE TYPE uk_postcode AS VARCHAR;
CREATE TYPE positive_integer AS INTEGER;
CREATE TYPE positive_number AS NUMBER;

以下查询调用 IFF 函数。该调用计算 us_zipcode 用户定义的类型的值，以及兼容的 Snowflake 类型的值。查询使用 SYSTEM$TYPEOF 函数，以显示结果是 Snowflake 基类型 VARCHAR：

SELECT IFF(TRUE, '90210'::us_zipcode, '10006') AS result,
       SYSTEM$TYPEOF(IFF(TRUE, '90210'::us_zipcode, '10006')) AS type;

+--------+-------------------------+
| RESULT | TYPE                    |
|--------+-------------------------|
| 90210  | VARCHAR(134217728)[LOB] |
+--------+-------------------------+

以下查询与上一个查询相同，但它将结果转换为 us_zipcode 用户定义的类型：

SELECT IFF(TRUE, '90210'::us_zipcode, '10006')::us_zipcode AS result,
       SYSTEM$TYPEOF(IFF(TRUE, '90210'::us_zipcode, '10006')::us_zipcode) AS type;

+--------+-------------------------------+
| RESULT | TYPE                          |
|--------+-------------------------------|
| 90210  | MYDB.MYSCHEMA.US_ZIPCODE[LOB] |
+--------+-------------------------------+

以下查询包含 CASE 表达式，用于计算不同但兼容的用户定义类型，并返回 Snowflake 基类型的值：

SELECT CASE
         WHEN TRUE THEN 'SW1A 0AA'::uk_postcode
           ELSE '90210'::us_zipcode
         END AS result,
       SYSTEM$TYPEOF(CASE
         WHEN TRUE THEN 'SW1A 0AA'::uk_postcode
           ELSE '90210'::us_zipcode
         END) AS type;

+----------+-------------------------+
| RESULT   | TYPE                    |
|----------+-------------------------|
| SW1A 0AA | VARCHAR(134217728)[LOB] |
+----------+-------------------------+

以下查询与上一个查询相同，但它将结果转换为 uk_postcode 用户定义的类型：

SELECT CAST(CASE
         WHEN TRUE THEN 'SW1A 0AA'::uk_postcode
           ELSE '90210'::us_zipcode
         END AS uk_postcode) AS result,
       SYSTEM$TYPEOF(CAST(CASE
         WHEN TRUE THEN 'SW1A 0AA'::uk_postcode
           ELSE '90210'::us_zipcode
         END AS uk_postcode)) AS type;

+----------+--------------------------------------------+
| RESULT   | TYPE                                       |
|----------+--------------------------------------------|
| SW1A 0AA | MYDB.MYSCHEMA.UK_POSTCODE[LOB]             |
+----------+--------------------------------------------+

以下查询包含 COALESCE 表达式，用于计算不同但兼容的用户定义类型，并返回 Snowflake 基类型的值：

SELECT COALESCE(
         5::positive_integer,
         10::positive_number) AS result,
       SYSTEM$TYPEOF(COALESCE(
         5::positive_integer,
         10::positive_number)) AS type;

+--------+-------------------+
| RESULT | TYPE              |
|--------+-------------------|
|      5 | NUMBER(38,0)[SB1] |
+--------+-------------------+

以下查询与上一个查询相同，但它将结果转换为 positive_number 用户定义的类型：

SELECT CAST(COALESCE(
         5::positive_integer,
         10::positive_number
       ) AS positive_number) AS result,
       SYSTEM$TYPEOF(CAST(COALESCE(
         5::positive_integer,
         10::positive_number
       ) AS positive_number)) AS type;

+--------+------------------------------------+
| RESULT | TYPE                               |
|--------+------------------------------------|
|      5 | MYDB.MYSCHEMA.POSITIVE_NUMBER[SB1] |
+--------+------------------------------------+