Funções em PostgreSQL

O PostgreSQL permite criar funções (também chamadas de user defined functions), e essas funções permitem misturar programação imperativa com programação declarativa.

As funções no PostgreSQL não necessariamente obedecem ROLLBACKS em transações. Em alguns casos sim, outros não.

O PostgreSQL suporta diferentes linguagens de programação.

As mais famosas e seguras são:

  • PLSQL (Procedural Language SQL)

  • PLPGSQL (Procedural Language PostgreSQL)

  • C (Linguagem C)

  • PLV8 (Pocedural Language V8 Javascript Engine)

O PLPGSQL (Procedural Language PostgreSQL) possui mais funções e é mais legível que o PLSQL (Procedural Language SQL). Ambos são baseados nos tipos e na sintaxe do SQL, o que se torna ruim para quem não é muito acostumado.

Em termos de performance o PGSQL e o PLPGSQL são bem parecidos. O C é o mais rápido de todos. Já o PLV8 é mais lento por ser Javascript, entretanto é o mais legível, moderno e fácil de mexer.

Nesse artigo vou me focar nas funções escritas com PLPGSQL pois ela é a linguagem mais usada para criar funções. Para C ou PLV8 algumas coisas mudam, principalmente os tipos de dados.

Como criar uma função?

De forma genérica, uma função, independente da linguagem usada, terá esse formato:

CREATE FUNCTION nome_da_funcao(-- Tipo dos parâmetros da funçãoparametro_1 TIPO_DE_DADO_DO_PARAMETRO_1,parametro_2 TIPO_DE_DADO_DO_PARAMETRO_2)-- Tipo de retorno da funçãoRETURNS TIPO_DE_RETORNO_DA_FUNCAOLANGUAGE 'linguagem_usada'-- VOLATILIDADE: VOLATILE, STABLE IMMUTABLEAS $code$...código da função...$code$;

Abaixo explicarei melhor sobre os tipos de dados.

Tipos de dados (usados em parâmetros, retorno e variáveis)

Ao criar uma função você precisa definir os tipos das variáveis que você irá usar. Os tipos são usados em três lugares:

  • Parâmetros da função

  • Retorno da função

  • Declaração de variáveis dentro da função (no PL SQL e PL PLGSQL)

Cada uma dessas variáveis precisam ter um tipo. Esses tipos podem ser:

  • Tipos padrão do PostgreSQL (INTEGER, TEXT, NUMERIC, BOOLEAN...)

  • Tipo definido pelo usuário (user defined type)

  • Tipo dinâmico copiado de alguma coluna, tabela ou outra variável

  • Tipo dinâmico ANYELEMENT, que simboliza uma variável de qualquer tipo que não seja um ARRAY ou um SET

  • Tipos com vários elementos ANY, também chamado de SET, contém várias colunas (vários elementos) em uma só variável (ROW ou RECORD)

  • Pseudo tipos

Tipos padrão do PostgreSQL

São os tipos padrão, usados normalmente no PostgreSQL:

INTEGER, NUMERIC, TEXT, VARCHAR, BOOLEAN, TIMESTAMP, TIMESTAMPTZ, etc.

Tipo definido pelo usuário

Aceita um tipo criado pelo usuário (user defined type).

Tipo dinâmico copiado de outro elemento

Para copiar o tipo dinamicamente de alguma outra coluna, tabela ou variável:

variavel%TYPE

Tipos polimórficos (ou pseudo tipos)

É uma categoria de tipos muito interessante, eles pode assumir qualquer tipo existente. É muito útil para definir uma função de operação matemática que funciona com todos os tipos que envolvem números: SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION. Desse modo é possível, com apenas uma função, aceitar todos os tipos de dados numéricos.

Limitações: quando uma função com vários parâmetros ANYELEMENT (por exemplo) for chamada, todos os parâmetros ANYELEMENT precisam receber variáveis do mesmo tipo.

São eles:

  • ANYELEMENT - assume qualquer tipo de dado, mas quando chamada em um parâmetro e um retorno, todos os tipos precisam ser iguais

  • ANYARRAY - assume qualquer tipo de dado em um array, e todos os dados dentro desse array são do mesmo tipo

  • ANYNONARRAY - semelhante ao ANYELEMENT, mas impede que seja um ARRAY

  • ANYENUM - qualquer tipo ENUM

  • ANYRANGE - qualquer tipo RANGE

Um exemplo de função com ANYELEMENT:

CREATE OR REPLACE FUNCTION somar(parametro_um ANYELEMENT, parametro_dois ANYELEMENT)    RETURNS ANYELEMENT    LANGUAGE 'plpgsql'    STABLE    AS $$BEGIN    RETURN parametro_um + parametro_dois;END;$$;

Mais referências em:
https://www.postgresql.org/docs/current/extend-type-system.html

Tipos com vários elementos: ROW e RECORD

O ROW tem uma estrutura pré definida e pode ser convertido para um composite type. As colunas são pré definidas antes mesmo da função ser chamada.

É possível definir os tipos a partir das colunas de uma tabela dessa forma:

tabela%ROWTYPE

O RECORD tem uma estrutura dinâmica, não se sabe o que há dentro dele, então todas suas operações são em tempo de execução. É como se fosse um map/dicionário de chave tipo string e valor tipo qualquer. Se você acessar um campo que não existe dentro de uma variável RECORD uma exceção será lançada.

Parâmetros da função

É muito simples declarar os parâmetros da função, basta usar o nome da variável e seu tipo:

CREATE FUNCTION nome_da_funcao(parametro_um TEXT,parametro_dois INTEGER)...

E se você quiser aceitar um número variável de parâmetros? Isso é possível usando o VARIADIC

CREATE FUNCTION nome_da_funcao(VARIADIC parametros TEXT[])...

Quando você chamar a função, poderá passar um número variável de parâmetros:

SELECT nome_da_funcao('a');SELECT nome_da_funcao('a','b');SELECT nome_da_funcao('a','b','c','d','e');

Exemplo de função VARIADIC com soma:

CREATE OR REPLACE FUNCTION somar(VARIADIC parametro_um ANYELEMENT[])    RETURNS ANYELEMENT    LANGUAGE 'plpgsql'    STABLE    AS $$BEGIN    RETURN parametro_um + parametro_dois;END;$$;

Parâmetros

Exemplo completo de criação de função em PLPGSQL

CREATE OR REPLACE FUNCTION nome_da_funcao (/* Aqui são definidos os parâmetros da função. Se a função não receber parâmetros, deixe tudo vazio. */parametro_um text, parametro_dois integer, parametro_tres boolean)/* Aqui definimos o tipo de retorno da função.Para retornar várias colunas: RETURNS TABLE (algo_id INTEGER, valor NUMERIC)Para retornar o retorno de um SQL: RETURNS RECORD ou RETURNS ROWPara retornar vazio: RETURNS VOID */    RETURNS integer    /* Aqui definimos a linguagem em que a função é escritaAs principais são plpgsql, sql, c e plv8     */    LANGUAGE 'plpgsql'    /* Aqui definimos a volatilidade da funçãoPode ser: VOLATILE, STABLE, IMMUTABLE */    STABLE    AS $$    /* Aqui declaramos variáveis (opcional). */DECLARE    variavel_um text;    variavel_dois integer;BEGIN    /* Aqui podemos começar a escrever a função. */    RETURN...    /* Para retornar uma query (apenas no PLPGSQL)     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

Sem 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 e o cachê é usado nas chamadas seguintes.

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.

Retornando nulo se algum parâmetro for nulo - 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 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.

COST 100

Leak proof

Uma função LEAKPROOF não revela informação dos seus argumentos a não ser pelo seu retorno.  Uma função que jogue um erro para certos valores mas não jogue para outros não pode ser considerada LEAKPROOF. Isso infuencia diretamente nas funcionalidades do ROW LEVEL SECURITY.

Apensar SUPERUSERS podem criar funções LEAKPROOF então funções são NOT LEAKPROOF por padrão.

A sintaxe do LEAKPROOF é:

  • LEAKPROOF

  • NOT LEAKPROOF

Testando funções facilmente com o DO

Uma forma muito fácil de testar funções e ir testando e debugando elas é usando o DO.

O DO cria e executa uma função anônima sem parâmetros e sem retorno (ou retornando void, para os acostumados com Java).

Aqui vai um exemplo:

DO LANGUAGE 'plpgsql'$$DECLARE	texto_teste TEXT;	double_teste DOUBLE PRECISION;	integer_teste INTEGER;	record_teste RECORD;BEGIN	-- Putting values in the variables	SELECT 'hello world' INTO texto_teste;	double_teste := 123.321;	integer_teste := 1 + 2;	record_teste := (123,'aaaa',233.2323);	-- Sending messages to the console	RAISE INFO '%', texto_teste;	RAISE NOTICE '%', double_teste;	RAISE WARNING '%', integer_teste;	RAISE INFO '%', row_to_json(record_teste);	RAISE NOTICE '%, %, %', record_teste.f1::TEXT, record_teste.f2::TEXT, record_teste.f3::TEXT;END;$$

https://www.postgresql.org/docs/current/sql-do.html

Testando funções que retornam uma consulta

DO LANGUAGE 'plpgsql'$$DECLARE	texto_teste TEXT;	double_teste DOUBLE PRECISION;	integer_teste INTEGER;	record_teste RECORD;begin	-- Putting values in the variables	DROP TABLE IF EXISTS test_table;	CREATE TEMPORARY TABLE test_table AS SELECT 'hello temporary' AS hello;END;$$;SELECT *FROM test_table;

Testando retorno de queries dinâmicas em uma consulta

Esse exemplo é bem importante e ajuda muito ao fazer análise de dados e analytics usando o PostgreSQL.

Em minha experiência, ajuda muito a fazer análise de várias tabelas (iguais, partições) em uma só consulta.

DO LANGUAGE 'plpgsql'$$DECLARE	tabela_temporaria TEXT;    tabela_real TEXT;begin    tabela_temporaria := 'tabela_desejada';    tabela_real := 'tabela_real';	EXECUTE format('DROP TABLE IF EXISTS %I;', tabela_temporaria);    EXECUTE format('CREATE TEMP TABLE %I AS SELECT * FROM %I;', tabela_temporaria, tabela_real);END;$$;SELECT *FROM tabela_desejada;

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

Declarações em funções PL PGSQL
https://www.postgresql.org/docs/current/plpgsql-declarations.html

Lançando erros e excessões (exceptions) em funções no PostgreSQL
https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html

Declarando uma função com qualquer tipo
https://stackoverflow.com/questions/36221623/how-to-specify-a-generic-type-for-a-function-parameter

Lista de pseudo tipos no PostgreSQL
https://www.postgresql.org/docs/current/datatype-pseudo.html

Bom tutorial sobre funções no PostgreSQL
https://www.postgresqltutorial.com/plpgsql-function-parameters/

Funções simples e interessantes para criar

Transformar INTERVAL em segundos

CREATE OR REPLACE FUNCTION interval_to_seconds(_interval INTERVAL)RETURNS DOUBLE PRECISIONLANGUAGE 'plpgsql'IMMUTABLEAS $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$;

Somando 2 valores ou mais (usando loop FOR/FOREACH):

CREATE FUNCTION public."++"(VARIADIC INTEGER[]) RETURNS INTEGER AS $$DECLARE  s INTEGER := 0;  x INTEGER;BEGIN  FOREACH x IN ARRAY $1  LOOP    s := s + x;  END LOOP;  RETURN s;END;$$ LANGUAGE plpgsql;

Somando 2 valores ou mais (usando UNNEST e AGGREGATION FUNCTION SUM):

CREATE OR REPLACE FUNCTION public."++"(VARIADIC INTEGER[])RETURNS INTEGERLANGUAGE 'plpgsql'IMMUTABLE AS $BODY$DECLAREx INTEGER;BEGIN	SELECT sum(x_unnested) 	INTO x	FROM (SELECT UNNEST($1) x_unnested) AS _;	RETURN x;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$;

Acessando array em JSONB

-- Acessando array em JSONBCREATE OR REPLACE FUNCTION "->"(	json_object JSONB,	array_index INT)RETURNS JSONBSTABLELANGUAGE 'plpgsql'AS $$BEGINRETURN json_object->array_index;END;$$;-- TestandoSELECT "->"('{"batata": "aaaa"}'::JSONB, 'batata');-- retorna 'aaaa'::JSONB

Acessando objeto (property) em JSONB

-- Acessando objeto (propriedade) em JSONBCREATE OR REPLACE FUNCTION "->"(	json_object JSONB,	object_name TEXT)RETURNS JSONBSTABLELANGUAGE 'plpgsql'AS $$BEGINRETURN json_object->object_name;END;$$;-- TestandoSELECT "->"('[1,2,3]'::JSONB, 0);-- retorna 1::JSONB

Acessando quaisquer elementos em um JSONB (#>)

CREATE OR REPLACE FUNCTION "#>"(	json_object JSONB,	path_search TEXT[])RETURNS JSONBSTABLELANGUAGE 'plpgsql'AS $$BEGINRETURN json_object #> path_search;END;$$;-- TestandoSELECT "#>"('{"batata":[1,2,3]}'::JSONB, '{"batata",-1}'::TEXT[]);-- Retorna'3'::JSONB

Retornando o menor valor de uma lista de timestamps tz

CREATE OR REPLACE FUNCTION public.min(	VARIADIC timestamps TIMESTAMPTZ[])    RETURNS TIMESTAMPTZ    LANGUAGE 'plpgsql'    IMMUTABLE AS $BODY$ DECLARE 	minimum TIMESTAMPTZ;	counter INT;BEGIN 	minimum := timestamps[1];	FOR counter IN 2 .. array_upper(timestamps, 1) LOOP		IF timestamps[counter] < minimum THEN		minimum := timestamps[counter];		END IF;	END LOOP;RETURN minimum;END; $BODY$;-- Exemplo de teste:SELECT min('2020-01-01 00:01','2020-01-01 00:02', '2020-01-01 00:03');-- Retorna 2020-01-01 00:01

Retornando o menor valor de uma lista de intervals

CREATE OR REPLACE FUNCTION public.min(	VARIADIC intervals INTERVAL[])    RETURNS INTERVAL    LANGUAGE 'plpgsql'    IMMUTABLE AS $BODY$ DECLARE 	minimum INTERVAL;	counter INT;BEGIN 	minimum := intervals[1];	FOR counter IN 2 .. array_upper(intervals, 1) LOOP		IF intervals[counter] < minimum THEN		minimum := intervals[counter];		END IF;	END LOOP;RETURN minimum;END; $BODY$;

Atribuindo resultado de um INSERT RETURNING em uma variável

-- Inserindo resultado de INSERT em uma variavelDECLARE  batata INTEGER;BEGIN  INSERT INTO tabela_a (a, b) VALUES (123, 345) RETURNING a INTO batata;...

Functions vs Stored Procedures, qual a diferença?

A diferença é bem simples. Stored procedures podem executar transações e User defined Functions não.

Referências:
https://kb.objectrocket.com/postgresql/function-vs-stored-procedure-602

You should also read: