Criar e usar uma função no Banco de Dados do Azure para PostgreSQL

  • 5 minutos

Já aprendemos que o PostgreSQL dá suporte a linguagens diferentes. As funções podem ser categorizadas em quatro tipos diferentes:

  • Funções escritas em SQL.
  • Funções de linguagem de procedimento, escritas em uma linguagem de procedimento com suporte, como PL.pgSQL.
  • Funções internas.
  • Funções de linguagem C.

Além disso, a finalidade da função também pode ser categorizada como volátil, imutável ou estável.

Uma função volátil (o padrão) pode modificar o banco de dados e não necessariamente retornará o mesmo resultado com os mesmos parâmetros de entrada a cada vez. Portanto, toda vez que essa função for chamada, ela deverá ser reavaliada.

Uma função estável não pode modificar o banco de dados e retornará o mesmo resultado se receber os mesmos argumentos e for executada na mesma instrução. Se essa função for chamada várias vezes, o otimizador de consulta poderá usar os resultados da última vez em que ela foi chamada.

Uma função imutável não pode modificar o banco de dados e retornará os mesmos resultados se receber os mesmos argumentos, independentemente da consulta que a chama.

A volatilidade de uma função faz uma grande diferença para a eficiência com que o otimizador de consulta a manipula.

Criar uma função

Uma função retorna apenas um valor e pode ser usada em uma instrução SELECT.

Esta é a sintaxe usada para criar uma função é:

CREATE [OR REPLACE] FUNCTION
myfunction ([inputparam] type {default})
RETURNS returntype AS
$$
SQL body
$$
LANGUAGE 'language_name';
CREATE FUNCTION

Assim como acontece com os procedimentos armazenados, o símbolo $$ é usado para iniciar e encerrar a cadeia de caracteres.

As funções assumem os seguintes parâmetros:

  • name – opcionalmente, inclua o nome do esquema.
  • argmode – o modo do argumento. Pode ser IN, OUT, INOUT ou VARIADIC. O padrão é IN. VARDIAC é um número indefinido de argumentos de entrada do mesmo tipo e é seguido por argumentos OUT. Argumentos OUT e INOUT não podem ser usados junto com notações RETURNS TABLE.
  • argname – o nome do argumento.
  • argtype – o tipo de dados do argumento. Pode ser de um dos tipos base, composto ou de domínio, ou fazer referência a um tipo de coluna de tabela. O tipo de coluna é escrito como nome_da_tabela.nome_da_coluna%TYPE. Esse tipo de dados pode ajudar a tornar uma função independente das alterações de definição de tabela.
  • t_expr – um valor padrão (do mesmo tipo) se o parâmetro não for especificado. Somente os parâmetros IN e INOUT têm um valor padrão. Parâmetros de entrada seguindo um parâmetro com um valor padrão também precisam ter valores padrão.
  • rettype – o tipo de dados de retorno, que pode ser um tipo base, composto ou de domínio, ou referenciar um tipo de coluna de tabela. Se a função não retornar nenhum valor, especifique o tipo de retorno como nulo. Quando há parâmetros OUT ou INOUT, a cláusula RETURNS pode ser omitida. Se estiver presente, ela precisará concordar com o tipo de resultado implícito pelos parâmetros de saída. A cláusula será igual ao parâmetro de saída se houver apenas um, ou RECORD se houver vários. O modificador SETOF indica que a função retornará um conjunto de itens, em vez de apenas um item. O tipo de coluna é referenciado escrevendo table_name.
  • column_name – o nome de uma coluna de saída na sintaxe RETURNS TABLE. Esse parâmetro declara um parâmetro OUT nomeado, exceto que RETURNS TABLE também implica RETURNS SETOF.
  • column_type – o tipo de dados de uma coluna de saída na sintaxe RETURNS TABLE.
  • lang_name – a linguagem de programação usada para escrever o procedimento. O padrão será sql se sql_body for especificado. Pode ser SQL, C, interno ou o nome de uma linguagem de procedimento definida pelo usuário, por exemplo, PLPGSQL.

Use as palavras-chave IMMUTABLE, STABLE ou VOLATILE como uma dica para o otimizador de consulta sobre a função. VOLATILE é o padrão.

Chamar uma função

Uma função pode ser usada em uma consulta passando parâmetros relevantes para ela. Por exemplo:

SELECT myfunction(3), CatID, CatName
    FROM myCats

Funções internas

O PostgreSQL inclui muitas funções internas que você pode usar em suas consultas. Elas incluem comparações, agregações de dados, funções matemáticas etc. Confira a documentação online para obter uma lista completa de funções PostgreSQL.

Um exemplo de uma função de cadeia de caracteres interna é substring.

substring (*string* text [ FROM *start* integer ] [ FOR *count* integer ] ) → text

Essa função usa três parâmetros de entrada:

  • String (do tipo text)
  • FROM start (do tipo integer)
  • Contagem FOR (inteiro de tipo)

A subcadeia de caracteres retorna parte do texto de entrada, começando no caractere definido em start e parando após o número de caracteres definido em count. Por exemplo:

substring('Thomas' from 2 for 3) → hom
substring('Thomas' from 3) → omas
substring('Thomas' for 2) → Th

Essa função é a mesma que a substr:

substr ( *string* text, *start* integer [, *count* integer ] ) → text
substr('alphabet', 3) → phabet
substr('alphabet', 3, 2) → ph

Observação

Se você estiver familiarizado com funções, terá notado que a primeira versão usa palavras-chave em vez de vírgulas para separar argumentos. O PostgreSQL fornece ambas as versões dessas funções.