CREATE FUNCTION - Amazon Redshift
Privilégios obrigatóriosSintaxeParâmetrosObservações de usoExemplos

CREATE FUNCTION

Crie uma nova função definida pelo usuário (UDF) escalar usando uma cláusula SQL SELECT ou um programa Python.

Para ter mais informações e exemplos, consulte Funções definidas pelo usuário (UDFs) no Amazon Redshift.

Privilégios obrigatórios

Você deve ter permissão de uma das seguintes formas para executar CREATE OR REPLACE FUNCTION:

  • Para CREATE FUNCTION:

    • O superusuário pode usar linguagens confiáveis e não confiáveis para criar funções.

    • Usuários com o privilégio de CREATE [ OR REPLACE ] FUNCTION podem criar funções com linguagens confiáveis.

  • Para REPLACE FUNCTION:

    • Superusuário

    • Usuários com o privilégio CREATE [ OR REPLACE ] FUNCTION

    • Proprietário da função

Sintaxe

CREATE [ OR REPLACE ] FUNCTION f_function_name
( { [py_arg_name  py_arg_data_type |
sql_arg_data_type } [ , ... ] ] )
RETURNS data_type
{ VOLATILE | STABLE | IMMUTABLE }
AS $$
  { python_program | SELECT_clause }
$$ LANGUAGE { plpythonu | sql }

Parâmetros

OR REPLACE

Especifica que, se uma função com os mesmos tipos de dados de argumento de nome e entrada, ou assinatura, como esta já existir, a função existente será substituída. Você só pode substituir uma função por uma nova função que defina um conjunto idêntico de tipos de dados. É preciso ser um superusuário para substituir uma função.

Se uma função for definida com o mesmo nome que uma função existente mas com uma assinatura diferente, uma nova função será criada. Em outras palavras, o nome da função fica sobrecarregado. Para obter mais informações, consulte Sobrecarga de nomes de função.

f_nome_função

Nome da função. Se você especificar um nome de esquema (como myschema.myfunction), a função será criada usando o esquema especificado. Caso contrário, a função será criada no esquema atual. Para obter mais informações sobre nomes válidos, consulte Nomes e identificadores.

Recomendamos que você prefixe os nomes de todos os UDF com f_. O Amazon Redshift reserva o prefixo f_ para nomes UDF, portanto, usando o prefixo f_, você garante que seu nome UDF não entrará em conflito com nenhum nome de função SQL integrado do Amazon Redshift existente ou futuro. Para obter mais informações, consulte Evitar conflitos na nomeação da UDF.

Você pode definir mais de uma função com o mesmo nome se os tipos de dados dos argumentos de entrada forem diferentes. Em outras palavras, o nome da função fica sobrecarregado. Para ter mais informações, consulte Sobrecarga de nomes de função.

py_arg_name py_arg_data_type | sql_arg_data_type

Para uma UDF Python, uma lista de nomes de argumento de entrada e tipos de dados. Para uma UDF SQL, uma lista de tipos de dados sem nomes de argumento. Em uma UDF Python, consulte argumentos usando nomes de argumento. Em uma UDF SQL, consulte argumentos usando $1, $2, etc. com base na ordem dos argumentos na lista de argumentos.

Para uma UDF SQL, os tipos de dados de entrada e de retorno podem ser qualquer tipo de dados padrão do Amazon Redshift. Para uma UDF Python, os tipos de dados de entrada e retorno podem ser SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE PRECISION, BOOLEAN, CHAR, VARCHAR, DATE ou TIMESTAMP. Além disso, as funções definidas pelo usuário (UDFs) do Python são compatíveis com um tipo de dados ANYELEMENT. Ele é convertido automaticamente em um tipo de dados padrão com base no tipo de dados do argumento correspondente fornecido no tempo de execução. Se vários argumentos usarem ANYELEMENT, todos encontrarão o mesmo tipo de dados no tempo de execução, com base no primeiro argumento ANYELEMENT da lista. Para ter mais informações, consulte Tipos de dados da UDF Python e Tipos de dados.

Você pode especificar um máximo de 32 argumentos.

RETURNS tipo_dados

Tipo de dados do valor retornado pela função. O tipo de dados RETURNS pode ser qualquer tipo de dados padrão do Amazon Redshift. Além disso, as UDFs Python podem usar um tipo de dados ANYELEMENT, que é convertido automaticamente em um tipo de dados padrão com base no argumento fornecido no tempo de execução. Se você especificar ANYELEMENT para o tipo de dados de retorno, pelo menos um argumento deverá usar ANYELEMENT. O tipo de dados de retorno corresponde ao tipo de dados fornecido pelo argumento ANYELEMENT quando a função for chamada. Para obter mais informações, consulte Tipos de dados da UDF Python.

VOLATILE | STABLE | IMMUTABLE

Informa o otimizador de consulta sobre a volatilidade da função.

Você terá a melhor otimização se identificar sua função com a categoria mais restrita de volatilidade válida para ela. Entretanto, se a categoria for restrita demais, o otimizador poderá ignorar erroneamente algumas chamadas, o que resultará em um conjunto incorreto de resultados. Por ordem de nível de restrição, começando a partir da menos restrita, as categorias são:

  • VOLATILE

  • STABLE

  • IMMUTABLE

VOLATILE

Dados os mesmos argumentos, a função pode retornar resultados diferentes em chamadas sucessivas, mesmo para as linhas em uma única instrução. O otimizador de consulta não pode fazer suposições sobre o comportamento de uma função volátil, portanto, uma consulta que usa uma função volátil deve reavaliar a função para cada linha de entrada.

STABLE

Dados os mesmos argumentos, a função certamente retorna os mesmos resultados para todas as linhas processadas em uma única instrução. A função pode retornar resultados diferentes quando chamada em instruções diferentes. Esta categoria permite que o otimizador aperfeiçoe várias chamadas da função em uma única instrução para uma única chamada da instrução.

IMMUTABLE

Dados os mesmos argumentos, a função sempre retorna o mesmo resultado. Quando uma consulta chama uma função IMMUTABLE com argumentos constantes, o otimizador faz uma avaliação prévia da função.

AS $$ instrução $$

Uma construção que delimita a instrução a ser executada. As palavras-chave literais AS $$ e $$ são obrigatórias.

O Amazon Redshift exige que você inclua a instrução na sua função usando um formato chamado cotação de dólar. Qualquer item incluído na instrução é aprovado exatamente como está. Não é preciso inserir caracteres de escape antes dos caracteres especiais, pois o conteúdo da string é gravado literalmente.

Com a cotação de dólar, um par de símbolos de cifrão ($$) deve ser usado no início e no final da instrução para que ela seja executada, conforme exibido no exemplo a seguir. 

$$ my statement $$

Como opção, entre cada par de símbolos de cifrão, você pode especificar uma string para ajudar a identificar a instrução. A string que você usa deve ser a mesma no início e no final dos pares de sinais. A sequência diferencia maiúsculas de minúsculas e segue as mesmas restrições de um identificador sem aspas, com a exceção de que não pode conter cifrões. O exemplo a seguir usa a string test. 

$test$ my statement $test$

Para obter mais informações sobre o uso de símbolos de cifrão, consulte “Constantes de string entre símbolos de cifrão” em Estrutura lexical na documentação do PostgreSQL.

programa_python

Programa executável válido em Python que retorna um valor. A instrução que você aprovar com a função deve estar em conformidade com os requisitos de recuo como especificado no Guia de estilo do código Python no site do Python. Para obter mais informações, consulte Suporte da linguagem Python para UDFs.

SQL_clause

Uma cláusula SQL SELECT.

A cláusula SELECT não pode conter estes tipos de cláusulas:

  • FROM

  • INTO

  • WHERE

  • GROUP BY

  • ORDER BY

  • LIMIT

LANGUAGE { plpythonu | sql }

Para Python, especifique plpythonu. Para SQL, especifique sql. Você deve ter permissão de uso na linguagem para SQL ou plpythonu. Para obter mais informações, consulte Segurança e permissões de UDFs.

Observações de uso

Funções aninhadas

Você pode chamar outra função definida pelo usuário (UDF) SQL a partir de uma UDF SQL. A função aninhada deve existir quando você executa o comando CREATE FUNCTION. O Amazon Redshift não rastreia as dependências das UDFs, portanto, se você remover a função aninhada, o Amazon Redshift não retornará um erro. Contudo, a UDF falhará se a função aninhada não existir. Por exemplo, a função a seguir chama a função f_sql_greater na cláusula SELECT.

create function f_sql_commission (float, float )
  returns float
stable
as $$
  select f_sql_greater ($1, $2)
$$ language sql;

Segurança e privilégios de UDF

Para criar uma UDF, você deve ter permissão de uso na linguagem para SQL ou plpythonu (Python). Por padrão, USAGE ON LANGUAGE SQL é concedido para PUBLIC. No entanto, é necessário conceder explicitamente USAGE ON LANGUAGE PLPYTHONU a usuários ou grupos específicos.

Para revogar o uso na SQL, revogue primeiro o uso em PUBLIC. Depois, conceda o uso na SQL somente a usuários ou grupos específicos que tenham permissão para criar UDFs SQL. O exemplo a seguir revoga o uso na SQL em PUBLIC e concede o uso ao grupo de usuários udf_devs.

revoke usage on language sql from PUBLIC;
grant usage on language sql to group udf_devs;

Para executar uma UDF, é necessário ter permissão de execução em cada função. Por padrão, a permissão de execução de novas UDFs é concedida a PUBLIC. Para restringir o uso, revogue a execução da permissão de PUBLIC para a função. Depois, conceda o privilégio a pessoas ou grupos específicos.

Este exemplo revoga a permissão de execução da função f_py_greater em PUBLIC e concede o uso ao grupo de usuários udf_devs.

revoke execute on function f_py_greater(a float, b float) from PUBLIC;
grant execute on function f_py_greater(a float, b float) to group udf_devs;

Os superusuários têm todos os privilégios por padrão.

Para ter mais informações, consulte GRANT e REVOKE.

Exemplos

Exemplo de UDF Python escalar

O exemplo a seguir cria uma UDF Python que compara dois inteiros e retorna o valor maior.

create function f_py_greater (a float, b float)
  returns float
stable
as $$
  if a > b:
    return a
  return b
$$ language plpythonu;

O exemplo a seguir consulta a tabela SALES e chama a nova função f_py_greater para retornar COMMISSION ou 20% de PRICEPAID, o que for maior.

select f_py_greater (commission, pricepaid*0.20) from sales;

Exemplo de UDF SQL escalar

O seguinte exemplo cria uma função que compara dois números e retorna o maior valor. 

create function f_sql_greater (float, float)
  returns float
stable
as $$
  select case when $1 > $2 then $1
    else $2
  end
$$ language sql;

A consulta a seguir chama a nova função f_sql_greater para consultar a tabela SALES e retorna COMMISSION ou 20% de PRICEPAID, o que for maior.

select f_sql_greater (commission, pricepaid*0.20) from sales;