PL/pgSQL por Diversão e Lucro

Roberto Mello3a. Conferência Brasileira de PostgreSQL - PGCon-BR

Campinas - 2009

1

Objetivos

Apresentar as partes principais do PL/pgSQL

Apresentar casos de uso comum do PL/pgSQL

Apresentar alguns casos do que NÃO fazer com PL/pgSQL

Apresentar os diferentes tipos de gatilhos

Como criar gatilhos com PL/pgSQL

Exercícios para prática

Resolver dúvidas (ou pelo menos tentar)2

Como aproveitar melhor

Tenha a documentação em mãos http://www.postgresql.org/docs/8.4/static/

Tenha o PostgreSQL instalado no seu laptop

Se não tiver, pode escrever no seu editor de texto favorito, ou mesmo em papel

Estou usando exemplos simples, vários dos quais seriam melhor feitos direto em SQL, ao invés de PL

3

Modelo de Dados

CREATE TABLE vendedores( vendedor_id SERIAL NOT NULL PRIMARY KEY, nome varchar(256));

INSERT INTO vendedores VALUES (DEFAULT, ‘Fabio’), (DEFAULT, ‘Joao’), (DEFAULT, ‘Leonardo’);

CREATE TABLE vendas( item_id integer, vendedor_id integer REFERENCES vendedores.vendedor_id, quantidade integer, preco numeric);

INSERT INTO vendas VALUES (1, 1, 5, 142), (2, 3, 54, 45), (3, 2, 21, 467), (4, 2, 32, 343), (5, 1, 21, 235);;

4

PL/pgSQL

É uma linguagem de procedimentos (mas não é a única aceita no PostgreSQL)

Adiciona estrutura e controle ao SQL

É parecida com outras linguagens da família C

Roda dentro do servidor, evitando a viagem e conversões entre cliente e servidor

Um dos seus principais usos é em gatilhos

Acesso à todas as funcionalidades do SQL5

CREATE OR REPLACE FUNCTION uma_funcao(preco numeric) RETURNS integer AS $$DECLARE -- declarações de variáveis quantidade integer := 30;BEGIN -- expressões, consultas, cálculos, etc RETURN quantidade * preco;END;$$ LANGUAGE plpgsql;

Estrutura

6

ParâmetrosPodem ser de entrada (IN) ou saída (OUT)

CREATE OR REPLACE FUNCTION calc_item(p_item_id int, OUT quantidade int, OUT total numeric) AS $$BEGIN    SELECT vendas.quantidade, vendas.quantidade * vendas.preco INTO quantidade, total FROM vendas WHERE item_id = p_item_id;END;$$ LANGUAGE plpgsql;

Últimos valores atribuídos às variáveis de saída são os que valemUso de RETURN seria redundante

7

CREATE OR REPLACE FUNCTION uma_funcao() RETURNS integer AS $$<< fora >>DECLARE quantidade integer := 30;BEGIN RAISE NOTICE 'Quantidade aqui %', quantidade; -- 30 quantidade := 50; -- -- Criamos um sub-bloco -- DECLARE quantidade integer := 80; BEGIN RAISE NOTICE 'Quantidade aqui %', quantidade; -- 80 RAISE NOTICE 'Quantidade de fora aqui %', fora.quantidade; -- 50 END;

RAISE NOTICE 'Quantidade aqui %', quantidade; -- 50

RETURN quantidade;END;$$ LANGUAGE plpgsql;

Blocos

8

Sobre transações

Uma função PL/pgSQL e todos os seus blocos roda INTEIRAMENTE dentro de uma transação

BEGIN e END dentro de uma função são para delinear os blocos, e não para controlar transações

Dentro da função não há como alterar a transação da função (e.g. COMMIT/ROLLBACK)

Pode-se criar sub-transações dentro da função usando controle de exceções (a ser visto mais adiante)

9

Operadores

variavel := expressao

expressao será avaliada como SQL e deve retornar um valor escalar

*, +, -, /

Qualquer outro operador permitido pelo mecanismo de SQL

10

Exercício 1

Crie uma função que aceite 2 números inteiros e retorne a soma dos dois números

11

Executando ConsultasSQL que não retorna registros pode ser executado diretamente.

CREATE FUNCTION remarca( p_item_id int, p_pct int) RETURNS NULL AS $$DECLARE novo_preco numeric DEFAULT 0;BEGIN UPDATE vendas SET preco = preco + 1 WHERE item_id = p_item_id; -- Usando PERFORM ao invés de SELECT descartará todos -- os resultados que sejam retornados PERFORM remarca_todos();

-- Variável especial FOUND IF FOUND THEN raise notice ‘resultados descartados’; END IF;END;$$ LANGUAGE plpgsql; 12

Executando ConsultasResultados de consultas precisam ser postos em variáveis

CREATE FUNCTION remarca( p_item_id int, p_pct int) RETURNS numeric AS $$DECLARE novo_preco numeric DEFAULT 0;BEGIN UPDATE vendas SET preco = preco + (preco * 1/p_pct) WHERE item_id = p_item_id RETURNING preco INTO novo_preco;

RETURN novo_preco;END;$$ LANGUAGE plpgsql;

13

Executando Consultas

Do mesmo jeito para SELECT, INSERT, DELETE

SELECT expressões_select INTO [STRICT] alvo FROM ...;

INSERT ... RETURNING expressões INTO [STRICT] alvo;

UPDATE ... RETURNING expressões INTO [STRICT] target;

DELETE ... RETURNING expressões INTO [STRICT] alvo;

14

Exercício 2

Crie uma função que:

Receba um identificador de um vendedor

Retorne a soma das vendas daquele vendedor

15

Registros

Variáveis do tipo RECORD armazenam registros inteiros

DECLARE uma_venda RECORD;BEGIN SELECT * INTO uma_venda FROM vendas WHERE item_id = p_item_id; RAISE NOTICE ‘Preco: %’,uma_venda.preco;

16

Estruturas de ControleIF var <> 0 THEN faça algoELSIF var >= 2 THEN faça outra coisaELSE trate casos extrasEND IF;

FOR i IN 1..10 LOOP --i irá de 0 a 10 --no laçoEND LOOP;

WHILE preco > 410 LOOP RAISE NOTICE ‘Bom Lucro’;END LOOP;

CASE vendedor WHEN 1, 3 THEN faca algo aquiELSE outros calculosEND CASE;

CASE WHEN vendedor = 1 THEN comissao := 100; WHEN vendedor = 2 THEN comissao := 60; ELSE comissao := 30;END CASE;

17

Passando pelos Resultados de Consultas

DECLARE v_vendedores RECORD;BEGIN FOR v_vendedores IN SELECT * FROM vendedores ORDER BY vendedor_id LOOP RAISE NOTICE ‘%’, v_vendedores.nome; END LOOP;

18

Exercício 3

Crie uma função que receba um vendedor_id e imprima ‘Parabens!’ para cada venda (tabela vendas) que o total (preco * quantidade) da venda tenha passado de 800

19

Retornando Múltiplos Registros

CREATE OR REPLACE FUNCTION calc_item(p_item_id int) RETURNS TABLE (quantidade int, total numeric) AS $$BEGIN    RETURN QUERY SELECT vendas.quantidade, vendas.quantidade * vendas.preco FROM vendas WHERE item_id = p_item_id;END;$$ LANGUAGE plpgsql;

20

RETURNS TABLE é o mesmo que declarar vários parâmetros OUT e especificar RETURNS SETOF umtipo

Retornando Múltiplos RegistrosCREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS

$BODY$DECLARE r foo%rowtype;BEGIN FOR r IN SELECT * FROM foo WHERE fooid > 0 LOOP -- pode-se fazer processamento aqui RETURN NEXT r; -- returna o registro corrente do SELECT END LOOP; RETURN;END$BODY$LANGUAGE 'plpgsql';

21

Retornando Múltiplos Registros

CUIDADO!!

RETURN NEXT e RETURN QUERY armazenam TODO o resultado na memória antes de retornar, o que causa uso exagerado de memória e disco se o resultado for de muitos registros

22

Cursores

Cursores são resultados de consultas que se pode ir e voltar na lista dos resultados, ao invés de só adiante

São eficientes no que diz respeito ao uso de memória

Só podem ser usados dentro de uma transação

Operações:

FETCH [ direction { FROM | IN } ] cursor INTO target;MOVE [ direction { FROM | IN } ] cursor;UPDATE table SET ... WHERE CURRENT OF cursor;DELETE FROM table WHERE CURRENT OF cursor;

23

CursoresCREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$BEGIN OPEN $1 FOR SELECT * FROM table_1; RETURN NEXT $1; OPEN $2 FOR SELECT * FROM table_2; RETURN NEXT $2;END;$$ LANGUAGE plpgsql;

-- precisa estar numa transação para usar cursores.BEGIN;

SELECT * FROM myfunc('a', 'b');

FETCH ALL FROM a;FETCH ALL FROM b;COMMIT;

24

Gatilhos

Funções que são chamadas automaticamente

antes (before) ou depois (after) de Insert, Update, Delete, Truncate

Função tem que ser declarada específicamente como de gatilho, e um CREATE TRIGGER tem que ser rodado por tabela sobre a qual o gatilho incidirá

25

Gatilhos - variáveis

NEW - armazena o novo registro em insert/updateOLD - armazena o registro velho em UPDATE/DELETETG_NAME - nome do gatilho disparadoTG_WHEN - string contendo “BEFORE” ou “AFTER”TG_LEVEL - string com ROW ou STATEMENT dependendo do gatilhoTG_OP - string com INSERT, UPDATE, DELETE, or TRUNCATE indicando a operaçãoTG_RELID - oid da tabela que chamou o gatilhoTG_TABLE_NAME - nome da tabela que chaou o gatilhoTG_TABLE_SCHEMA - nome do esquema da tabela que chamou o gatilhoTG_NARGS - número de argumentos dados à função do gatilho no CREATE TRIGGERTG_ARGV[] - matriz com os argumentos dados no CREATE TRIGGER

26

GatilhosCREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$ BEGIN IF NEW.empname IS NULL THEN RAISE EXCEPTION 'empname não pode ser nulo'; END IF; IF NEW.salary IS NULL THEN RAISE EXCEPTION '% não pode ter salário nulo', NEW.empname; END IF;

IF NEW.salary < 0 THEN RAISE EXCEPTION '% não pode ter salário negativo', NEW.empname; END IF;

-- Lembre quem mudou a folha, e quando NEW.last_date := current_timestamp; NEW.last_user := current_user; RETURN NEW; END;$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

27

Exercício 4

Crie uma função de gatilho que insira um novo registro na tabela comissoes cada vez que um vendedor fizer uma nova venda (tabela vendas) cujo preço seja maior que 400

A comissão deverá ser de 15% do valor da venda

Tabela comissoes tem colunas: comissao_id, vendedor_id, item_id, comissao

28

Armadilhas a serem evitadas

Programadores inexperientes em SQL tentam fazer tudo em PL/pgSQL, ao invés de procurar a maneira idiomática (e geralmente muito mais rápida) de se fazer em SQL

Consultas dentro de funções também precisam ser testadas e otimizadas, senão viram buraco negro

Mudanças de dados em gatilhos não devem ser silenciosas: erros tem que ser notificados à aplicação para que ela seja corrigida

29

Ex: Gatilho errôneo

BEGIN

/* Don’t allow date_created or created_by fields to be updated */

IF (’UPDATE’ = TG_OP) THEN bool_has_column := func_table_has_column(TG_RELNAME::varchar, ‘created_by’);

IF bool_has_column THEN NEW.created_by = OLD.created_by; END IF; NEW.date_created = OLD.date_created;END IF;

RETURN NEW;END;

30

Dados são mudados slienciosamente, sem log

Perguntas?

robertomello@2reng.com.br

31