Banco de Dados II

Linguagem PL/pgSQL

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:

CREATE OR REPLACE FUNCTION testfunc(INTEGER) RETURNS INTEGER AS '
	....
END;
' LANGUAGE 'plpgsql';

Enquanto roda o psql, você pode carregar ou recarregar uma função de um arquivo da seguinte forma:

\i arquivo.sql

e imediatamente você está apto a utilizar e testar esta função.

1. Insalando o PL/pgSQL

O seguinte comando informa ao sevidor da base de dados onde encontrar o objeto compartilhado para a linguamge PL/pgSQL.

CREATE FUNCTION plpgsql_call_handler () RETURNS LANGUAGE_HANDLER AS
	'$libdir/plpgsql' LANGUAGE C;

O proximo comando define que o call handler previamente declarado será invocada para funções e procedimentos de gatilhos onde o atributo será plpgsql.

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
	HANDLER plpgsql_call_handler;

2. 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:

[ <> ]
	[ DECLARE declarations ]
BEGIN
	statements
END; /* Comentários*/

Qualquer seção de statement em um bloco pode ter um sub-bloco com suas declarações. Por exemplo:

CREATE FUNCTION somefunc() RETURNS INTEGER AS '
DECLARE
	quantity INTEGER := 30;
BEGIN
	RAISE NOTICE ''Quantity here is %'',quantity; -- Quantity here is 30
	quantity := 50;
	--
	-- Create a sub-block
	--
	DECLARE
		quantity INTEGER := 80;
	BEGIN
		RAISE NOTICE ''Quantity here is %'',quantity;  -- Quantity here is 80
	END;
	RAISE NOTICE ''Quantity here is %'',quantity;  -- Quantity here is 50
	RETURN quantity;
END;
' LANGUAGE 'plpgsql';

2.1.Declarações

A sintaxe geral para a declaração de uma variável é:

name [ 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 variavel 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:

quantity INTEGER DEFAULT 32;
url varchar := ''http://mysite.com'';
user_id CONSTANT INTEGER := 10;

2.1.1. Aliases para parâmetros de funções

name 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:

CREATE FUNCTION sales_tax(REAL) RETURNS REAL AS '
DECLARE
	subtotal ALIAS FOR $1;
BEGIN
	return subtotal * 0.06;
END;
' LANGUAGE 'plpgsql';

2.1.2. Row Types

name tablename%ROWTYPE;

Uma variável de tipo composto é chamado uma variavel row (row-type). Esta variavel pode conter uma coluna inteira 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 indentificador correspondente $n será a coluna e um campo pode ser selecionado dela, por exemplo $1.user_id.

Por exemplo:

CREATE FUNCTION use_two_tables(tablename) RETURNS TEXT AS '
DECLARE
	in_t ALIAS FOR $1;
	use_t table2name%ROWTYPE;
BEGIN
	SELECT * INTO use_t FROM table2name WHERE ... ;
	RETURN in_t.f1 || use_t.f3 || in_t.f5 || use_t.f7;
END;
' LANGUAGE 'plpgsql';

2.1.3. Records

name RECORD;

Variáveis Record são similares aos row-type, mas eles não tem uma estrutura pré-definidas. A subestrutura de um record pode ser mudado durante a atribuição. A consequência disso é que enquanto nada for atribuído a variável, ela não pode ser usada pois retornará um erro de tempo de execução. Isso pelo fato de ela não ter uma estrutura definda ainda.

2.1.4. Atributos

Usando os atributos %TYPE e %ROWTYPE pode se declarar variáveis com um mesmo tipo de dado ou estrutura.

2.1.5. variable%TYPE

Para declarar uma variável com o mesmo tipo de dado de users.user_id, por exemplo, declara-se da seguinte forma:

user_id users.user_id%TYPE;

2.1.6. table%ROWTYPE

%ROWTYPE provê um tipo de dado composto correspondente a uma coluna de uma tabela específica.

DECLARE
	users_rec users%ROWTYPE;
	user_id users.user_id%TYPE;
BEGIN
	user_id := users_rec.user_id;
	...

CREATE FUNCTION does_view_exist(INTEGER) RETURNS bool AS '
DECLARE
	key ALIAS FOR $1;
	table_data cs_materialized_views%ROWTYPE;
BEGIN
	SELECT INTO table_data * FROM cs_materialized_views WHERE sort_key=key;
	IF NOT FOUND THEN
		RETURN false;
	END IF;
	RETURN true;
END;
' LANGUAGE 'plpgsql';

3. Estruturas de controle

3.1. Condições

3.1.1. IF-THEN:

IF boolean-expression THEN
	statements
END IF;

IF v_user_id <> 0 THEN
	UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

3.1.2.IF-THEN-ELSE:

IF boolean-expression THEN
	statements
ELSE
	statements
END IF;

IF v_count > 0 THEN 
	INSERT INTO users_count(count) VALUES(v_count);
	return ''t'';
ELSE 
	return ''f'';
END IF;

3.1.3. IF-THEN-ELSE IF:

IF demo_row.sex = ''m'' THEN
	pretty_sex := ''man'';
ELSE
	IF demo_row.sex = ''f'' THEN
		pretty_sex := ''woman'';
	END IF;
END IF;

3.1.4.IF-THEN-ELSEIF-ELSE:

IF number = 0 THEN
	result := ''zero'';
ELSIF number > 0 THEN 
	result := ''positive'';
ELSIF number < 0 THEN
	result := ''negative'';
ELSE
	-- apenas se number for nulo
	result := ''NULL'';
END IF;

4.2. Laços

4.2.1. LOOP - EXIT

LOOP
	statements
END LOOP;

LOOP
	-- alguns calculos.
	count := count+1;
	IF count > 10 THEN
		EXIT; 
		-- sai do loop
	END IF;
END LOOP;

4.2.3.WHILE

WHILE expression LOOP
	statements
END LOOP;

WHILE salario < 1000 AND desconto < 100 LOOP
	-- algum calculo aqui
END LOOP;

4.2.4. FOR

FOR name IN [ REVERSE ] expression .. expression LOOP
	statements
END LOOP;

FOR i IN 1..10 LOOP
	-- codigo
	RAISE NOTICE ''i is %'',i;
END LOOP;

FOR i IN REVERSE 10..1 LOOP
	-- codigo
END LOOP;

4.2.5. Laço sobre resultado de uma Query

FOR record | row IN select_query LOOP
	statements
END LOOP;

CREATE FUNCTION cs_refresh_mviews () RETURNS INTEGER AS '
DECLARE
	mviews RECORD;
BEGIN
	PERFORM cs_log(''Refreshing materialized views...'');
	FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
		-- Now "mviews" has one record from cs_materialized_views
		PERFORM cs_log(''Refreshing materialized view '' || quote_ident(mviews.mv_name) || ''...'');
		EXECUTE ''TRUNCATE TABLE  '' || quote_ident(mviews.mv_name);
		EXECUTE ''INSERT INTO '' || quote_ident(mviews.mv_name) || '' '' || mviews.mv_query;
	END LOOP;
	PERFORM cs_log(''Done refreshing materialized views.'');
	RETURN 1;
END;
' LANGUAGE 'plpgsql';

4.3. Exemplos:

CREATE OR REPLACE FUNCTION taxas(REAL) RETURNS REAL AS '
DECLARE
	subtotal ALIAS FOR $1;
BEGIN
	IF subtotal>100 THEN
		subtotal := subtotal * 0.06;
	ELSE
		subtotal := subtotal * 0.08;
	END IF;
	RETURN subtotal;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION atualisa_taxas (REAL) RETURNS INTEGER AS '
DECLARE
	taxa ALIAS FOR $1;
	mviews RECORD;
BEGIN
	FOR mviews IN SELECT * FROM funcionario ORDER BY nome LOOP
		UPDATE funcionario SET salario=salario*taxa;
	END LOOP;
	RETURN 1;
END;
' LANGUAGE 'plpgsql';

5. 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; variavel que contém a nova coluna da base de dados de uma operação de INSERT/UPDATE.

OLD

Tipo de dado RECORD; variavel 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 fáz a gerência do esgoque.

CREATE TABLE estoque (
	codigo serial,
	produto varchar(30),
	quantidade integer default '0',
	limite integer default '0',
	primary key(codigo)
);

CREATE TABLE mensagem (
	msgid serial,
	msg varchar(50),
	primary key(msgid)
);

CREATE OR REPLACE FUNCTION verifica_estoque () RETURNS TRIGGER AS '
BEGIN
	-- verifica se o estoque está baixo
	IF NEW.quantidade < NEW.limite THEN
		INSERT INTO mensagem (msg) VALUES(''Estoque de '' || NEW.produto || '' baixo.'');
	END IF;
	RETURN NEW;
END;
' LANGUAGE 'plpgsql';

-- CREATE TRIGGER verifica_estoque BEFORE INSERT OR UPDATE ON estoque
--     FOR EACH ROW EXECUTE PROCEDURE verifica_estoque();

CREATE TRIGGER verifica_estoque BEFORE UPDATE ON estoque
    FOR EACH ROW EXECUTE PROCEDURE verifica_estoque();

[voltar]