Tipos definidos pelo usuário

Você pode definir tipos definidos pelo usuário, que são novos tipos de dados baseados em :doc:` tipos de dados do Snowflake </sql-reference-data-types>` existentes. Por exemplo, suponha que você queira definir uma coluna para a idade de uma pessoa e queira restringir os valores para incluir números com no máximo três dígitos e nenhum dígito após a vírgula decimal. Você pode definir um tipo de dados chamado age que corresponde a NUMBER(3,0).

Um tipo definido pelo usuário é um objeto de nível de esquema que pode ser utilizado em todos os locais em que é possível utilizar tipos, incluindo definições de coluna, definições de função e procedimento e expressões de conversão.

Os tipos definidos pelo usuário podem simplificar a manutenção do esquema e melhorar a qualidade dos dados. Você pode definir um tipo definido pelo usuário uma vez e usá-lo em vários objetos.

Você também pode usar tipos definidos pelo usuário para agrupar campos de dados relacionados em uma única coluna lógica, em vez de usar várias colunas ou tabelas para os campos. Por exemplo, você pode definir um tipo de dados para endereços que seja um tipo estruturado OBJECT com campos para o endereço, cidade, estado e CEP.

Privilégios necessários para tipos definidos pelo usuário

Para criar um tipo definido pelo usuário em um esquema, você deve usar uma função que tenha recebido o privilégio CREATE TYPE nesse esquema.

Para obter mais informações, consulte os requisitos de controle de acesso para tipos definidos pelo usuário.

Notas de uso geral para tipos definidos pelo usuário

  • Para alterar a definição de um tipo definido pelo usuário, descarte-o e recrie-o.

    Se você alterar a definição de um tipo definido pelo usuário:

    • As instruções SQL que operam diretamente em colunas de tabela que usam o tipo poderão retornar erros, incluindo instruções SELECT e DML. No entanto, as instruções SQL que não operam diretamente em colunas de tabela que usam o tipo serão executadas normalmente. Por exemplo, se uma tabela contiver uma coluna de tipo definido pelo usuário chamada typed_column e uma instrução SELECT especificar outras colunas na lista SELECT, a instrução SELECT será executada normalmente. Para corrigir o problema, você pode revisar as instruções SQL para usar os tipos Snowflake subjacentes.

    • Chamadas para funções e procedimentos armazenados que usam o tipo retornarão erros. Para corrigir o problema, descarte e recrie as funções e os procedimentos armazenados.

  • O comando ALTER TABLE … ALTER COLUMN pode alterar o tipo de dados de uma coluna de um tipo definido pelo usuário para um :doc:` tipo de dados Snowflake </sql-reference-data-types>` compatível ou de um tipo de dados Snowflake para um tipo definido pelo usuário.

  • Quando você está construindo um objeto para inserir em uma coluna de um tipo definido pelo usuário usando a função OBJECT_CONSTRUCT ou uma constante OBJECT, converta o resultado para o tipo definido pelo usuário.

    Para exemplos, consulte Usando um tipo definido pelo usuário para uma coluna de tabela.

  • Quando os :doc:` operadores de conjunto </sql-reference/operators-query>` (por exemplo, UNION, INTERSECT, EXCEPT) ou as :doc:` funções de expressão condicional </sql-reference/expressions-conditional>` (por exemplo, CASE, IFF, COALESCE, NVL e assim por diante) avaliam uma expressão que resulta em um valor de um tipo definido pelo usuário, o Snowflake determina um tipo comum usando os tipos base subjacentes dos operandos. Por padrão, o tipo de dados do resultado é esse tipo base. Se você quiser que o resultado seja um valor de um tipo definido pelo usuário, converta explicitamente a expressão final para o tipo definido pelo usuário.

    As regras a seguir se aplicam quando tipos definidos pelo usuário são utilizados ​​em operações de conjunto ou funções de expressão condicional:

    • Os tipos definidos pelo usuário são distintos dos tipos base, mas, na resolução do tipo de expressão, eles sofrem coerção para os tipos base a fim de encontrar um tipo comum.

    • Se as ramificações ou operandos resultarem em um único tipo do Snowflake (por exemplo, VARCHAR ou NUMBER), esse será o tipo do resultado.

    • Para preservar um tipo definido pelo usuário ou produzir um resultado que seja um valor de um tipo definido pelo usuário, converta a expressão geral usando CAST(expr AS user-defined type) ou expr::user-defined type.

    • Tipos base incompatíveis (por exemplo, VARCHAR e NUMBER) seguem as regras de coerção normais. Se não existir um tipo base comum, um erro será retornado.

    Para exemplos, consulte Usando operadores de conjunto e funções de expressão condicional com tipos definidos pelo usuário.

  • É permitido usar tipos definidos pelo usuário e tipos de dados Snowflake compatíveis para :ref:` sobrecarga <label-procedure_function_name_overloading>` de função. Ou seja, você pode especificar um tipo definido pelo usuário para um tipo de argumento de função e pode especificar um tipo de dados Snowflake compatível para um tipo de argumento de uma função com o mesmo nome.

  • Se um tipo definido pelo usuário for especificado como o tipo RETURN de uma função definida pelo usuário (user-defined function, UDF) SQL ou procedimento armazenado do Snowflake Scripting, o valor de retorno deverá ser convertido explicitamente para o tipo definido pelo usuário no corpo da UDF ou do procedimento armazenado.

  • Quando um tipo definido pelo usuário é utilizado como argumento ou valor de retorno para uma UDF ou um procedimento escrito em uma linguagem diferente de SQL (como Python ou Java), o tipo definido pelo usuário é tratado da mesma forma que o tipo base.

  • A evolução de esquema não é compatível com tipos definidos pelo usuário.

Convertendo tipos definidos pelo usuário

Os tipos definidos pelo usuário são compatíveis com conversão de tipo de dados, incluindo conversão explícita e implícita (coerção):

Conversão explícita de e para tipos definidos pelo usuário

Um valor de tipo definido pelo usuário pode ser convertido nos mesmos tipos de dados que os valores do tipo base. Por exemplo, crie um tipo definido pelo usuário chamado age que seja baseado no tipo NUMBER:

CREATE TYPE age AS NUMBER(3,0);

Será possível converter um valor em um tipo definido pelo usuário se o valor puder ser convertido para o tipo base do tipo definido pelo usuário. Por exemplo, é possível converter o valor 10 no tipo NUMBER, então você pode converter o valor para o tipo age:

SELECT 10::age;

Um valor de tipo definido pelo usuário pode ser convertido em um tipo de dados diferente se o tipo base do tipo definido pelo usuário puder ser convertido nesse tipo de dados. Por exemplo, um valor NUMBER pode ser convertido no tipo VARCHAR, então o valor 10 do tipo definido pelo usuário age pode ser convertido no tipo VARCHAR:

SELECT 10::age::VARCHAR;

Coerção de tipos definidos pelo usuário

Um valor de tipo definido pelo usuário aplica coerção para o tipo base dele. Portanto, em todas as operações, ele se comporta da mesma forma que o tipo base. Por exemplo, crie um tipo definido pelo usuário chamado age que seja baseado no tipo NUMBER e uma tabela com duas colunas do tipo age:

CREATE TYPE age AS NUMBER(3,0);

CREATE TABLE test_age_udf(a age, b age);

Insira valores na tabela:

INSERT INTO test_age_udf VALUES (10, 20);

O exemplo a seguir realiza uma operação de adição nos valores da tabela, e o Snowflake aplica coerção aos valores age para valores do tipo NUMBER a fim de concluir a operação. O exemplo usa a função SYSTEM$TYPEOF para mostrar o tipo de dados do resultado:

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

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

Exemplos de tipos de dados definidos pelo usuário

Os exemplos a seguir mostram como usar tipos definidos pelo usuário:

Usando um tipo definido pelo usuário para uma coluna de tabela

No exemplo a seguir, você cria um tipo definido pelo usuário chamado address e, em seguida, usa o tipo em uma tabela:

  1. Crie um tipo definido pelo usuário baseado em um tipo OBJECT estruturado para armazenar informações de endereço:

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

  2. Crie uma tabela que armazena informações do cliente, incluindo o endereço:

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

  3. Insira uma linha na tabela e especifique o valor para a coluna cust_address convertendo uma constante OBJECT para o tipo 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. Insira uma linha na tabela e especifique o valor para a coluna cust_address chamando a função OBJECT_CONSTRUCT e convertendo o valor de retorno para o tipo 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. Insira uma linha na tabela e especifique o valor para a coluna cust_address convertendo uma constante OBJECT para o tipo OBJECT, que é o tipo base do tipo address. Geralmente é mais fácil converter uma constante OBJECT no tipo definido pelo usuário, mas este exemplo mostra que a constante OBJECT tem coerção aplicada para o tipo definido pelo usuário:

    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. Para exibir as linhas inseridas, consulte a tabela:

    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. Consulte a tabela e use o operador de dois pontos para exibir apenas os valores zip_code nos dados address:

    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                 |
+---------+--------------+-----------------------+

Usando operadores de conjunto e funções de expressão condicional com tipos definidos pelo usuário

Quando os operadores de conjunto ou as funções de expressão condicional avaliam valores de tipos Snowflake e tipos definidos pelo usuário, os tipos devem ser compatíveis e coercíveis em um único tipo. A saída resultante é do tipo base Snowflake, a menos que seja explicitamente convertida para um tipo definido pelo usuário. Para obter mais informações, consulte Notas de uso geral para tipos definidos pelo usuário.

Os exemplos nesta seção usam operadores de conjunto e expressões condicionais com tipos definidos pelo usuário. Primeiro, crie vários tipos definidos pelo usuário com vários tipos base:

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

A consulta a seguir chama a função IFF. A chamada avalia um valor do tipo definido pelo usuário us_zipcode e um valor de um tipo Snowflake compatível. A consulta usa a função SYSTEM$TYPEOF para mostrar que o resultado é do tipo base 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] |
+--------+-------------------------+

A consulta a seguir é igual à anterior, mas converte o resultado no tipo definido pelo usuário 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] |
+--------+-------------------------------+

A consulta a seguir contém uma expressão CASE que avalia tipos definidos pelo usuário diferentes, mas compatíveis, e retorna um valor de um tipo base 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] |
+----------+-------------------------+

A consulta a seguir é igual à anterior, mas converte o resultado no tipo definido pelo usuário 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]             |
+----------+--------------------------------------------+

A consulta a seguir contém uma expressão COALESCE que avalia tipos definidos pelo usuário diferentes, mas compatíveis, e retorna um valor de um tipo base 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] |
+--------+-------------------+

A consulta a seguir é igual à anterior, mas converte o resultado no tipo definido pelo usuário 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] |
+--------+------------------------------------+