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

  • 5 minutos

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

  • Funções escritas em SQL.
  • Funções de linguagem processual, escritas em uma linguagem processual suportada, como PL.pgSQL.
  • Funções internas.
  • Funções da linguagem C.

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

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

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

Uma função imutável não pode modificar o banco de dados e retorna os mesmos resultados se passar 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 lida com ela.

Criar uma função

Uma função retorna um único valor e pode ser usada dentro de uma instrução SELECT.

A sintaxe 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 nos procedimentos armazenados, o símbolo $$ é usado para iniciar e terminar a cadeia de caracteres.

As funções usam 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. Os argumentos OUT e INOUT não podem ser usados em conjunto com as notações RETURNS TABLE.
  • argname - nome do argumento.
  • ArgType - O tipo de dados do argumento. Pode ser do tipo base, composto ou domínio, ou fazer referência a um tipo de coluna de tabela. O tipo de coluna é escrito como table_name.column_name%TYPE. Esse tipo de dados pode ajudar a tornar uma função independente das alterações na definição da 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. Os parâmetros de entrada que seguem um parâmetro com um valor padrão também devem ter valores padrão.
  • rettype - O tipo de dados de retorno, que pode ser um tipo base, composto ou domínio, ou fazer referência a um tipo de coluna de tabela. Se a função não retornar um valor, especifique o tipo de retorno como void. Quando há parâmetros OUT ou INOUT, a cláusula RETURNS pode ser omitida. Se presente, ele deve concordar com o tipo de resultado implícito pelos parâmetros de saída: RECORD se houver vários parâmetros de saída, ou o mesmo tipo que o parâmetro de saída único. O modificador SETOF indica que a função retorna um conjunto de itens, em vez de um único item. O tipo de coluna é referenciado escrevendo table_name.
  • column_name - O nome de uma coluna de saída na sintaxe RETURNS TABLE. Este parâmetro declara um parâmetro nomeado OUT, 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 usada para escrever o procedimento. O padrão é sql se sql_body for especificado. Pode ser sql, c, internal ou o nome de uma linguagem processual 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 quaisquer parâmetros relevantes para ela. Por exemplo:

SELECT myfunction(3), CatID, CatName
    FROM myCats

Funções incorporadas

O PostgreSQL inclui muitas funções incorporadas que você pode usar em suas consultas. As teses abrangem fazer comparações, agregar dados, funções matemáticas, etc. Consulte a documentação on-line para obter uma lista completa das funções do PostgreSQL.

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

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

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

  • String (digite texto)
  • FROM start (digite inteiro)
  • Contagem FOR (número inteiro)

Substring retorna parte do texto de entrada, começando no caractere inicial e parando após a contagem de caracteres. Por exemplo:

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

Esta função é a mesma que substr:

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

Nota

Se você está familiarizado com funções, você deve 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.