Linguagem PL/pgSQL
Links Relacionados: Banco de Dados
Base de Dados
Para alguns exemplos vistos nessa aula, utilizaremos a seguinte base de dados:
1-- Excluindo as tabelas, caso ela já exista
2
3drop table dependente;
4
5drop table funcionario;
6
7-- Criando as tabelas
8
9create table funcionario (
10 idfunc serial primary key,
11 nome varchar(30),
12 salario real);
13
14create table dependente (
15 idfunc int references funcionario,
16 iddep serial,
17 nome varchar(30),
18 idade int,
19 primary key (idfunc, iddep));
20
21-- Alimentando as tabelas com informações
22
23insert into funcionario (nome, salario) values
24 ('Americo', 3000.50),
25 ('Beatriz', 5650.00),
26 ('Claudio', 1500.34),
27 ('Diana', 800.00),
28 ('Eduarda', 13300.50),
29 ('Fabricio', 2230.10),
30 ('Gabriel', 4100.00),
31 ('Helio', 2200.00),
32 ('Igor', 6600.00);
33
34insert into dependente (idfunc, nome, idade) values
35 (1, 'Joana', 12),
36 (1, 'Kelly', 2),
37 (2, 'Lara', 6),
38 (5, 'Mauro', 3),
39 (7, 'Nadia', 17),
40 (7, 'Olavo', 4),
41 (8, 'Patricia', 6),
42 (9, 'Quentin', 9),
43 (9, 'Renata', 10),
44 (9, 'Sandro', 3);
Introdução
O PL/pgSQL é uma linguagem procedural para o banco de dados PostgreSQL que pode ser usado para criar funções e triggers (procedimentos de disparo), adicionar estruturas de controle para a linguagem SQL, fazer cálculos complexos, herdar tipos de dados definidos pelos usuários, funções e operadores.
Uma boa maneira de desenvolver em PL/pgSQL é usar um editor simples de sua escolha e em outra janela usar o psql para carregar as funções que são criadas. Desta forma, é interessatne usar CREATE OR REPLACE FUNCTION para criar as funções, pois se determinada função já foi carregada, ela é alterada pela nova. Por exemplo:
1CREATE OR REPLACE FUNCTION testfunc(INTEGER) RETURNS INTEGER AS $$
2 ....
3END;
4$$ LANGUAGE plpgsql;
Enquanto roda o psql, você pode carregar ou recarregar uma função de um arquivo da seguinte forma:
\i arquivo.sqle imediatamente você está apto a utilizar e testar esta função.
Instalando o PL/pgSQL
O seguinte comando informa ao servidor da base de dados onde encontrar o objeto compartilhado para a linguagem PL/pgSQL.
1CREATE FUNCTION plpgsql_call_handler () RETURNS LANGUAGE_HANDLER AS
2 '$libdir/plpgsql' LANGUAGE C;
O próximo comando define que o call handler previamente declarado será invocada para funções e procedimentos de gatilhos onde o atributo será plpgsql.
1CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
2 HANDLER plpgsql_call_handler;
Estrutura do PL/pgSQL
O PL/pgSQL é uma linguagem estruturada na forma de um bloco, onde o conteúdo da função precisa estar dentro deste bloco. Um bloco é definido como:
1[ <> ]
2 [ DECLARE declarations ]
3BEGIN
4 statements
5END; /* Comentários*/
Qualquer seção de statement em um bloco pode ter um sub-bloco com suas declarações. Por exemplo:
1create or replace function somefunc() returns integer as $$
2declare
3 quantity integer := 30;
4begin
5 raise notice 'Quantity here is %',quantity; -- Quantity here is 30
6 quantity := 50;
7 /*
8 Create a sub-block
9 */
10 declare
11 quantity integer := 80;
12 begin
13 raise notice 'Quantity here is %', quantity; -- Quantity here is 80
14 end;
15 raise notice 'Quantity here is %', quantity; -- Quantity here is 50
16 return quantity;
17end;
18$$ language plpgsql;
Para visualizar a função criada dentro do shell do Postgresql, basta digitar:
1\sf somefunction
Declarações
A sintaxe geral para a declaração de uma variável é:
1name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ];
A cláusula DEFAULT especifica o valor atribuído inicialmente para a variável quando o bloco é iniciado. Se não existir a cláusula DEFAULT, então a variável é inicializada com o valor SQL NULL.
A opção CONSTANT previne que o valor da variável atribuído no início se manterá constante durante o bloco.
Se for especificado o NOT NULL, uma atribuição de valor nulo para a variável resultará um erro de tempo de execução. Desta forma, as variáveis declaradas como NOT NULL precisam ter um valor não nulo especificado.
Exemplos
1quantity INTEGER DEFAULT 32;
2url varchar := 'http://mysite.com';
3user_id CONSTANT INTEGER := 10;
Aliases para parâmetros de funções
1name ALIAS FOR $n;
Parâmetros passados para funções são reconhecidas dentro do bloco com identificadores como $1, $2, etc. Opcionalmente pode se criar aliases para estes parâmetros. Por exemplo:
1create or replace function sales_tax(real) returns real as $$
2declare
3 subtotal alias for $1;
4begin
5 return subtotal * 0.06;
6end;
7$$ language plpgsql;
ROWTYPE e TYPE
1name tablename%ROWTYPE
Uma variável de tipo composto é chamado ROWTYPE. Esta variável pode conter um conjunto de informações de um resultado de um SELECT. Um campo individual desta row é acessado com um '.' ponto como rowvar.field.
Os parâmetros para uma função pode ser do tipo composto. Neste caso, um identificador correspondente $n é a coluna e um campo pode ser selecionado dela, por exemplo $1.user_id.
Por exemplo:
1create or replace function testrowtype() returns text as $$
2declare
3 myrow funcionario%rowtype;
4begin
5 select * into myrow from funcionario where upper(nome) like 'A%';
6 return myrow.nome || ' recebe ' || myrow.salario || ' reais.';
7end;
8$$ language plpgsql;
Para declarar uma variável com o mesmo tipo de dado de users.user_id, por exemplo, declara-se da seguinte forma:
1user_id users.user_id%TYPE;
Já o %ROWTYPE provê um tipo de dado composto correspondente a uma coluna de uma tabela específica.
1DECLARE
2 users_rec users%ROWTYPE;
3 user_id users.user_id%TYPE;
4BEGIN
5 user_id := users_rec.user_id;
6 ...
Estruturas de controle
Condições
IF-THEN
1IF boolean-expression THEN
2 statements
3END IF;
Por exemplo:
1IF taxa > 0 THEN
2 UPDATE salario SET salario = salario * taxa;
3END IF;
IF-THEN-ELSE
1IF boolean-expression THEN
2 statements
3ELSE
4 statements
5END IF;
Exemplo:
1IF v_count > 0 THEN
2 INSERT INTO users_count(count) VALUES(v_count);
3 return 't';
4ELSE
5 return 'f';
6END IF;
1create or replace function taxas(real) returns real as $$
2declare
3 valor alias for $1;
4begin
5 if valor > 100 then
6 valor := valor * 0.06;
7 else
8 valor := valor * 0.08;
9 end if;
10 return valor;
11end;
12$$ language plpgsql;
IF-THEN-ELSE IF
1IF demo_row.sex = 'm' THEN
2 pretty_sex := 'man';
3ELSE
4 IF demo_row.sex = 'f' THEN
5 pretty_sex := 'woman';
6 END IF;
7END IF;
IF-THEN-ELSEIF-ELSE
1IF number = 0 THEN
2 result := 'zero';
3ELSIF number > 0 THEN
4 result := 'positivo';
5ELSIF number < 0 THEN
6 result := 'negativo';
7ELSE
8 -- apenas se number for nulo
9 result := 'nulo';
10END IF;
Laços
LOOP - EXIT
1loop
2 -- statements
3end loop;
1loop
2 -- alguns calculos.
3 count := count + 1;
4 if count > 10 then
5 exit; -- sai do loop
6 end if;
7end loop;
WHILE
1WHILE expression LOOP
2 statements
3END LOOP;
1WHILE salario < 1000 AND desconto < 100 LOOP
2 -- algum calculo aqui
3END LOOP;
FOR
1FOR name IN [ REVERSE ] expression .. expression LOOP
2 statements
3END LOOP;
1FOR i IN 1..10 LOOP
2 -- codigo
3 RAISE NOTICE 'i is %',i;
4END LOOP;
1FOR i IN REVERSE 10..1 LOOP
2 -- codigo
3END LOOP;
Laço sobre resultado de uma Query
1FOR record | row IN select_query LOOP
2 -- statements
3END LOOP;
1create or replace function mostra_previa (real) returns integer as $$
2declare
3 taxa alias for $1;
4 myview record;
5 salario real;
6begin
7 for myview in select * from funcionario order by salario loop
8 salario = myview.salario * taxa;
9 raise notice '% - R$ % ', myview.nome, salario;
10 end loop;
11 return 1;
12end;
13$$ language plpgsql;
Gatilhos (Triggers)
O PL/pgSQL permite também a criação de procedimentos de gatilhos (triggers). Estes procedimentos são criados com o comando CREATE FUNCTION com retorno de um tipo TRIGGER.
Quando uma função trigger do PL/pgSQL é chamada, as seguintes variáveis especiais são criadas automaticamente:
NEW
Tipo de dado RECORD; variável que contém a nova coluna da base de dados de uma operação de INSERT/UPDATE.
OLD
Tipo de dado RECORD; variável que contém a velha coluna da base de dados de uma operação de INSERT/UPDATE.
TG_NAME
Tipo de dado name; variável que contém o nome da trigger em execução.
TG_WHEN
Tipo de dado text; uma string de BEFORE ou AFTER dependendo da definição da trigger.
TG_LEVEL
Tipo de dado text; uma string de ROW ou STATEMENT dependendo da definição da trigger.
TG_OP
Tipo de dado text; uma string de INSERT, UPDATE ou DELETE de cada operação da trigger.
TG_RELID
Tipo de dado oid; o ID do objeto da tabela que causou a chamada da trigger.
TG_RELNAME
Tipo de dado name; o nome da tabela que causou a chamada da trigger.
TG_NARGS
Tipo de dado integer; o numero de argumentos passados para a trigger.
TG_ARGV[]
Tipo de dado array of text; os argumentos passados para a trigger, sendo que vai de 0 até tg_nargs-1.
Uma função trigger precisa retornar um valor nulo ou um valor record/row, tendo exatamente a estrutura da tabela que chamou a trigger.
Exemplo de uma trigger no PL/pgSQL
Este exemplo verifica se o estoque de um certo produto está mais baixo do que um certo limite e envia uma mensagem ao funcionário que faz a gerência do estoque.
1drop table estoque;
2drop table mensagem;
3
4create table estoque (
5 codigo serial,
6 produto varchar(30),
7 quantidade integer default '0',
8 limite integer default '0',
9 primary key(codigo)
10);
11
12create table mensagem (
13 msgid serial,
14 msg varchar(255),
15 primary key(msgid)
16);
17
18insert into estoque (produto, quantidade, limite) values
19 ('Mouse', 50, 10),
20 ('Teclado', 30, 5),
21 ('Monitor', 10, 8);
1create or replace function verifica_estoque () returns trigger as $$
2begin
3 -- verifica se o estoque esta baixo
4 if new.quantidade < new.limite then
5 insert into mensagem (msg) values (
6 'O produto ' || new.produto || ' esta com quantidade baixa ' ||
7 'no estoque. Ainda ha ' || new.quantidade || ' pecas.');
8 end if;
9 return new;
10end;
11$$ language plpgsql;
12
13create trigger verifica_estoque before insert or update on estoque
14 for each row execute procedure verifica_estoque();
Query para teste:
1update estoque set quantidade=quantidade-1 where codigo=3; select * from estoque; select * from mensagem;
