CREATE [ OR REPLACE ] FUNCTION nome ( [ [ nome_do_argumento ] tipo_do_argumento [, ...] ] ) RETURNS tipo_retornado { LANGUAGE nome_da_linguagem | IMMUTABLE | STABLE | VOLATILE | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | AS 'definição' | AS 'arquivo_objeto', 'símbolo_de_vinculação' } ... [ WITH ( atributo [, ...] ) ]
O comando CREATE FUNCTION cria uma função. O comando CREATE OR REPLACE FUNCTION cria uma função, ou substitui uma função existente.
Se for incluído o nome do esquema a função será criada no esquema especificado, senão será criada no esquema corrente. O nome da nova função não deve corresponder ao nome de uma função existente no mesmo esquema com argumentos dos mesmos tipos. Entretanto, funções com argumentos de tipos diferentes podem ter o mesmo nome, o que é chamado de sobrecarga (overload).
Para atualizar a definição de uma função existente deve ser usado o comando CREATE OR REPLACE FUNCTION. Não é possível mudar o nome ou os tipos dos argumentos da função desta maneira; se for tentado, na verdade será criada uma nova função distinta. O comando CREATE OR REPLACE FUNCTION também não permite mudar o tipo de dado retornado por uma função existente; para fazer isto a função deve ser removida e recriada.
Se a função for removida e recriada, a nova função não será mais a mesma entidade que era antes; será necessário remover as regras, visões, gatilhos, etc. que fazem referência à função antiga. O comando CREATE OR REPLACE FUNCTION é utilizado para mudar a definição de uma função sem invalidar os objetos que fazem referência à função.
O usuário que cria a função se torna o seu dono.
O nome (opcionalmente qualificado pelo esquema) da função a ser criada.
O nome do argumento. Algumas linguagens (atualmente apenas PL/pgSQL) deixam usar o nome do argumento no corpo da função. Para as demais linguagens, o nome do argumento é apenas uma documentação adicional.
O tipo de dado do argumento da função (opcionalmente qualificado pelo esquema), caso exista. O tipo de dado do argumento pode ser um tipo base, composto, ou domínio, ou pode ser feita referência ao tipo de dado de uma coluna da tabela.
Dependendo da linguagem de implementação também pode ser permitido especificar "pseudotipos", como cstring. Os pseudotipos indicam que o tipo de dado real do argumento não está completamente especificado, ou está fora do conjunto comum de tipos de dado do SQL.
O tipo de dado da coluna é referenciado escrevendo nome_da_tabela.nome_da_coluna%TYPE; a utilização desta notação pode, algumas vezes, ajudar a tornar a função independente das mudanças ocorridas na definição da tabela.
O tipo de dado retornado (opcionalmente qualificado pelo esquema), que pode ser um tipo base, tipo composto ou domínio, ou pode ser feita referência ao tipo de dado de uma coluna de tabela. Dependendo da linguagem de implementação também pode ser permitido especificar "pseudotipos", como cstring.
O modificador SETOF indica que a função retorna um conjunto de itens, em vez de um único item.
O tipo de dado da coluna é referenciado escrevendo nome_da_tabela.nome_da_coluna%TYPE.
O nome da linguagem usada para implementar a função. Pode ser SQL, C, internal, ou o nome de uma linguagem procedural definida pelo usuário. Para manter a compatibilidade com as versões anteriores, o nome pode estar entre apóstrofos (').
Estes atributos informam ao sistema se é seguro substituir várias chamadas à função por uma única chamada, para otimização em tempo de execução. Pode ser especificado, no máximo, um destes três atributos. Se nenhum deles for especificado, o padrão é assumir VOLATILE.
O atributo IMMUTABLE indica que a função não pode modificar o banco de dados, e sempre retorna o mesmo resultado quando recebe os mesmos valores para os argumentos, ou seja, não faz consultas a bancos de dados, ou de alguma outra forma utiliza informações que não estão diretamente presentes na sua lista de argumentos. Se esta opção for especificada, qualquer chamada à função com todos os argumentos constantes poderá ser substituída imediatamente pelo valor da função.
O atributo STABLE indica que a função não pode modificar o banco de dados, e dentro de uma única varredura da tabela a função retorna, consistentemente, o mesmo resultado para os mesmos valores dos argumentos, mas que seu resultado pode mudar entre comandos SQL. Esta é a seleção apropriada para as funções cujos resultados dependem de consultas a bancos de dados, parâmetros variáveis (como a zona horária corrente), etc. Deve ser observado, também, que a família de funções current_timestamp se qualifica como estável, uma vez que seus valores não mudam dentro de uma transação.
O atributo VOLATILE indica que o valor da função pode mudar mesmo dentro de uma única varredura da tabela e, portanto, não pode ser feita nenhuma otimização. Poucas funções de banco de dados são voláteis neste sentido; alguns exemplos são random(), currval() e timeofday(). Deve ser observado que toda função que produz efeito colateral deve ser classificada como volátil, mesmo que seu resultado seja totalmente previsível, para evitar que as chamadas sejam otimizadas; um exemplo é setval().
Para obter informações adicionais deve ser consultada a Seção 31.6. [1] [2] [3]
CALLED ON NULL INPUT (o padrão) indica que a função é chamada normalmente quando algum de seus argumentos é nulo. Portanto, é responsabilidade do autor da função verificar a presença de valores nulos se for necessário, e responder de forma apropriada.
RETURNS NULL ON NULL INPUT ou STRICT indicam que a função sempre retorna nulo quando qualquer um de seus argumentos for nulo. Se este parâmetro for especificado, a função não será executada quando houver argumento nulo; em vez disto será assumido um resultado nulo automaticamente.
SECURITY INVOKER indica que a função deve ser executada com os privilégios do usuário a chamou. Este é o padrão. SECURITY DEFINER especifica que a função deve ser executada com os privilégios do usuário que a criou.
A palavra chave EXTERNAL é permitida para manter a conformidade com o padrão SQL. Entretanto é opcional porque, diferentemente do padrão SQL, esta funcionalidade se aplica a todas as funções, e não apenas às funções externas.
A cadeia de caracteres contendo a definição da função; o significado depende da linguagem. Pode ser o nome de uma função interna, o caminho para um arquivo objeto, um comando SQL, ou um texto escrito em uma linguagem procedural.
Esta forma da cláusula AS é utilizada para funções escritas na linguagem C, carregáveis dinamicamente, quando o nome da função no código fonte na linguagem C não é o mesmo da função SQL. A cadeia de caracteres arquivo_objeto é o nome do arquivo contendo o objeto carregável dinamicamente, e símbolo_de_vinculação é o símbolo de vinculação da função, ou seja, o nome da função no código fonte na linguagem C. Se o símbolo de vinculação for omitido, é assumido como sendo o mesmo nome da função SQL sendo definida.
A forma histórica de especificar informações opcionais sobre a função. Podem ser utilizados os seguintes atributos:
Equivalente a STRICT ou RETURNS NULL ON NULL INPUT.
isCachable é um equivalente obsoleto de IMMUTABLE; ainda é aceito por motivo de compatibilidade com versões anteriores.
Não há diferença entre letras minúsculas e maiúsculas nos nomes dos atributos.
Para obter informações adicionais sobre como escrever funções deve ser consultada a Seção 31.3.
É permitida a sintaxe tipo SQL completa para os argumentos de entrada e o valor retornado. Entretanto, alguns detalhes da especificação do tipo (por exemplo, o campo precisão para o tipo numeric) são de responsabilidade da implementação da função subjacente, sendo engolidos em silêncio (ou seja, não são reconhecidos nem exigidos) pelo comando CREATE FUNCTION.
O PostgreSQL permite a sobrecarga de função, ou seja, pode ser utilizado o mesmo nome por várias funções diferentes, desde que possuam argumentos com tipos de dados distintos. Entretanto, na linguagem C os nomes de todas as funções devem ser diferentes e, portanto, as funções na linguagem C sobrecarregadas devem possuir nomes diferentes (por exemplo, utilizando os tipos dos argumentos como parte do nome da função na linguagem C).
Quando chamadas repetidas ao comando CREATE FUNCTION fazem referência ao mesmo arquivo objeto, o arquivo só é carregado uma vez. Para descarregar e recarregar o arquivo (talvez durante o desenvolvimento), deve ser usado o comando LOAD.
Para remover funções definidas pelo usuário deve ser utilizado o comando DROP FUNCTION .
Geralmente é útil utilizar o caractere cifrão ($) (consulte a Seção 4.1.2.2) para envolver a cadeia de caracteres que define a função, em vez de usar a sintaxe normal que envolve a cadeia de caracteres por apóstrofos. Se a definição da função não estiver envolvida pelo caractere cifrão, então todo apóstrofo ou contrabarra presente na definição da função deverá receber um escape duplicando os mesmos.
Para poder criar uma função o usuário deve possuir o privilégio USAGE na linguagem.
Abaixo estão mostrados exemplos simples para ajudar a começar. Para obter informações adicionais e outros exemplos deve ser consultada a Seção 31.3.
Somar dois números inteiros. Esta função recebe como argumentos dois números inteiros, e retorna como resultado a soma dos dois números recebidos.
CREATE FUNCTION somar_inteiros(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
SELECT somar_inteiros(2,3);
somar_inteiros
--------------
5
(1 linha)
Incrementar um inteiro. Incrementar um inteiro, fazendo uso do nome do argumento no PL/pgSQL:
CREATE OR REPLACE FUNCTION incrementar(i integer) RETURNS integer AS $$
BEGIN
RETURN i + 1;
END;
$$ LANGUAGE plpgsql;
Somar dias a uma data. A seguir está mostrada uma função sobrecarregada para somar dias a data. Pode ser utilizada com os tipos de dado date, timestamp e timestamp with time zone. [4]
CREATE OR REPLACE FUNCTION somar_dias(data date, dias integer)
RETURNS date AS $$
BEGIN
RETURN data + CAST(dias || ' DAYS' AS interval);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION somar_dias(data timestamp, dias integer)
RETURNS timestamp AS $$
BEGIN
RETURN data + CAST(dias || ' DAYS' AS interval);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION somar_dias(data timestamp with time zone, dias integer)
RETURNS timestamp with time zone AS $$
BEGIN
RETURN data + CAST(dias || ' DAYS' AS interval);
END;
$$ LANGUAGE plpgsql;
SELECT somar_dias(date '2004-07-16', 30);
somar_dias
------------
2004-08-15
(1 linha)
SELECT somar_dias(timestamp '2004-07-16 15:00:00', 30);
somar_dias
---------------------
2004-08-15 15:00:00
(1 linha)
SELECT current_timestamp, somar_dias(current_timestamp, 30);
timestamptz | somar_dias
----------------------------+----------------------------
2007-01-20 14:42:29.078-03 | 2007-02-19 14:42:29.078-03
(1 linha)
O comando CREATE FUNCTION está definido no SQL:1999 e posterior. A versão do PostgreSQL é semelhante mas não é totalmente compatível. Os atributos não são portáveis, nem as diferentes linguagens disponíveis o são.
| [1] |
Oracle — No comando CREATE FUNCTION deve ser especificado DETERMINISTIC para indicar que a função retorna o mesmo valor do resultado sempre que for chamada com os mesmos valores para seus argumentos. Esta palavra chave deverá ser especificada se houver intenção de chamar a função em uma expressão de um índice baseado em função, ou na consulta de uma visão materializada marcada com REFRESH FAST ou ENABLE QUERY REWRITE. Quando o banco de dados Oracle encontra uma função determinística em um destes contextos, tenta utilizar os resultados calculados anteriormente sempre que possível, em vez de executar novamente a função. Esta cláusula não deve ser especificada para definir uma função que utiliza variáveis de pacotes ou que acessa o banco de dados de uma maneira que pode afetar o resultado retornado pela função. Procedendo desta maneira, o resultado não será capturado se o banco de dados Oracle decidir por não executar novamente a função. Oracle® Database SQL Reference 10g Release 1 (10.1) Part Number B10759-01 — DETERMINISTIC Clause (N. do T.) |
| [2] |
SQL Server — No comando CREATE FUNCTION a propriedade IsDeterministic determina se a função é determinística ou não-determinística. É permitido acesso aos dados locais nas funções determinísticas. Por exemplo, as funções que sempre retornam o mesmo resultado toda vez que são chamadas utilizando um conjunto específico de valores de entrada, e com o mesmo estado do banco de dados, devem ser marcadas como determinísticas. SQL Server 2005 Books Online — CREATE FUNCTION (Transact-SQL) (N. do T.) |
| [3] |
DB2 — No comando CREATE FUNCTION as cláusulas opcionais DETERMINISTIC e NOT DETERMINISTIC especificam se a função sempre retorna os mesmos resultados para os mesmos valores dos argumentos (DETERMINISTIC), ou se a função depende de alguns valores de estado que afetam o os resultados (NOT DETERMINISTIC). Ou seja, a função DETERMINISTIC deve retornar sempre a mesma tabela em chamadas sucessivas com entradas idênticas. As otimizações que tiram vantagem do fato das entradas idênticas sempre produzirem os mesmos resultados podem ser inibidas especificando NOT DETERMINISTIC. DB2 Version 9 for Linux, UNIX, and Windows — CREATE FUNCTION (SQL Scalar, Table, or Row) statement (N. do T.) |
| [4] |
Exemplo escrito pelo tradutor, não fazendo parte do manual original. |
