Carregando...
Banco de DadosPostgreSQL

Funções em PostgreSQL

O PostgreSQL permite criar funções (também chamadas de stored procedures) no paradigma de programação imperativo (além do paradigma declarativo do SQL).

O PostgreSQL suporta diferentes linguagens de programação, mas por padrão temos a linguagem PL SQL (Procedural Language SQL) e o PL PGSQL

Exemplo de função

CREATE OR REPLACE FUNCTION nome_da_funcao 
( 
parametro_um TEXT, 
parametro_dois INTEGER, 
parametro_tres BOOLEAN
) 
/* 
** Para retornar várias colunas: 
RETURNS TABLE (algo_id INTEGER, valor NUMERIC)
** Para retornar o retorno de um SQL, mas o formato da tabela
RETURNS RECORD 
** Para retornar vazio
RETURNS VOID
*/
RETURNS INTEGER 
/*
Aqui podemos usar diferentes linguagens para escrever a função
Cada linguagem tem suas características.
As principais são plpgsql e sql
Vale a pena conferir também o plv8, que é nada mais que o bom javascript!
*/
LANGUAGE 'plpgsql'
/*
Aqui definimos a volatilidade da função
VOLATILE, STABLE, IMMUTABLE 
*/
STABLE 
AS $$ 
/*
Aqui podemos declarar variáveis.
Um exemplo:
DECLARE
variavel_um TEXT;
variavel_dois INTEGER;
*/
BEGIN 
/*
Aqui finalmente podemos escrever a função.
*/
RETURN... 
/*
Para retornar uma query:
RETURN QUERY SELECT ...;
*/
END; 
$$;

Volatilidade (VOLATILITY)

A volatilidade diz ao o PostgreSQL se ele poderá fazer um cachê para agilizar as chamadas das funções.

Volatile

Não é feito nenhum tipo de cachê. A função é executada e re-executada toda vez que for chamada.

Stable

É feito um cachê a nível de statement, ou seja, se a função for chamada mais de uma vez no mesmo statement, ela é executada apenas uma vez.

Deve ser usado em uma função que executa um SELECT que sempre retornará o mesmo valor dentro do mesmo statement.

Immutable

Faz o cachê a nivel global, é mais otimizado possível.

Deve ser usado nos casos em que dado os mesmos parâmetros, a função retornará sempre o mesmo valor, em qualquer momento.

Strict

Uma função STRICT retornará NULL se algum dos parâmetros for NULL.

Paralelismo (PARALLEL)

Esse parâmetro define se o PostgreSQL pode executar a função em vários núcleos do processador (multi-core) , deixando o processamento muito mais rápido.

Unsafe

Força a execução em apenas um core.

Restricted

Safe

Custo estimado de execução (Estimated COST)

Define um peso estimado para a função ser executada

Leak proof

Yes
No

Referências

Documentação do Carto
https://carto.com/help/working-with-data/sql-stored-procedures/

Documentação oficial sobre Parallel Safety
https://www.postgresql.org/docs/current/parallel-safety.html

Funções simples e interessantes para criar

Transformar INTERVAL em segundos

CREATE OR REPLACE FUNCTION interval_to_seconds 
(
_interval INTERVAL
)
RETURNS DOUBLE PRECISION
LANGUAGE 'plpgsql'
IMMUTABLE
AS $BODY$ 
BEGIN 
RETURN EXTRACT(EPOCH FROM _interval);
END; 
$BODY$;

Operações matemáticas básicas

Somatório e Subtração

CREATE OR REPLACE FUNCTION public."+"(
	parametro_um numeric,
	parametro_dois numeric)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    IMMUTABLE 
AS $BODY$ 

BEGIN 
RETURN parametro_um + parametro_dois;
END; 
$BODY$;
CREATE OR REPLACE FUNCTION public."-"(
	parametro_um numeric,
	parametro_dois numeric)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    IMMUTABLE 
AS $BODY$ 

BEGIN 
RETURN parametro_um - parametro_dois;
END; 
$BODY$;

Multiplicação e Divisão

CREATE OR REPLACE FUNCTION public."*"(
	parametro_um numeric,
	parametro_dois numeric)
    RETURNS numeric
    LANGUAGE 'plpgsql'

    COST 100
    IMMUTABLE 
AS $BODY$ 

BEGIN 
RETURN parametro_um * parametro_dois;
END; 
$BODY$;
CREATE OR REPLACE FUNCTION public."/"(
	parametro_um numeric,
	parametro_dois numeric)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    IMMUTABLE 
AS $BODY$ 

BEGIN 
RETURN parametro_um / parametro_dois;
END; 
$BODY$;
Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *