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

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

• Funções escritas no SQL.
• Funções de linguagem processual, escritas em uma linguagem processual 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 pode não necessariamente retornar o mesmo resultado com os mesmos parâmetros de entrada todas as vezes. Portanto, 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 em que foi chamado.

Uma função imutável não pode modificar o banco de dados e retornará os mesmos resultados se passar pelos mesmos argumentos, independentemente da consulta que o 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 um único valor e pode ser usada em 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 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:

• nome – 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 - nome do argumento.
• argtype – o tipo de dado 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 table_name.column_name%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 devem 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 um 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, ele deverá 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 uma 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 - o idioma usado 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. As teses abrangem fazer comparações, agregar dados, funções matemáticas etc. Consulte 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:

• Cadeia de caracteres (tipo texto)
• FROM start (do tipo integer)
• Contagem FOR (inteiro de tipo)

A subcadeia de caracteres 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

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