Aplicativos em PHP/Administração dos SGBDs/PostgreSQL

Aqui apenas passo o link para o livro

PostgreSQL Prático

• PostgreSQL Prático
  • Aplicativos em PHP/Trabalhando em PHP com/PostgreSQL

Com isso evitando repetições ou a reinvenção da roda.

Tutorial sobre o phpPgAdmin

- Baixar de - http://phppgadmin.sourceforge.net/

- Copiar para o diretório web e descompactar no diretório "phppgadmin"

- Editar o arquivo conf/config.inc.php e alterar para dois servidores (um local e outro remoto) caso assim queira:

        //...
	// Servidor Local
	// Display name for the server on the login screen
	$conf['servers'][0]['desc'] = 'Local';

	// Hostname or IP address for server.  Use '' for UNIX domain socket.
	// use 'localhost' for TCP/IP connection on this computer
	$conf['servers'][0]['host'] = '127.0.0.1';
	$conf['servers'][0]['defaultdb'] = 'nomebancodefault';

        //...
	// Servidor Remoto
	// Example for a second server
	$conf['servers'][1]['desc'] = 'Remoto';
	$conf['servers'][1]['host'] = '10.99.00.11';
	$conf['servers'][1]['port'] = 5432;
	$conf['servers'][1]['defaultdb'] = 'nomebancodefault';

        //...
	// If extra login security is true, then logins via phpPgAdmin with no
	// password or certain usernames (pgsql, postgres, root, administrator)
	// will be denied. Only set this false once you have read the FAQ and
	// understand how to change PostgreSQL's pg_hba.conf to enable
	// passworded local connections.
	$conf['extra_login_security'] = false;

	// Importantes para Importar/Exportar
	$conf['servers'][0]['pg_dump_path'] = '/usr/local/pgsql/bin/pg_dump';
	$conf['servers'][0]['pg_dumpall_path'] = '/usr/local/pgsql/bin/pg_dumpall';

- Pronto, basta abrir http://localhost/phppgadmin

Criando Chave Estrangeira Composta no phpPgAdmin e no PGAdmin

O exemplo é para uma chave com 4 campos, mas o procedimento é válido para qualquer chave composta.

Relacionar composicoes com precos.

Quero que ao digitar um preço em composições o SGBD verifique a integridade do mesmo em precos.

Temos uma tabela cuja chave primária é composta por 4 campos:

tabela, insumo_grupo, insumo, fornecedor e queremos relacionar com outra tabela que também contém os mesmos campos com os mesmos tipos de dados.

Como isso pode ter algum engano, seguem os passos.

Esquema das tabelas:

CREATE TABLE "composicoes" (
    "servico" character varying(10) NOT NULL,
    "insumo_grupo" character varying(6) NOT NULL,
    "insumo" character varying(10) NOT NULL,
    "quantidade" real NOT NULL DEFAULT 0,
    "coeficiente" real NOT NULL DEFAULT 0,
    "data_inclusao" timestamp without time zone,
    "data_alteracao" timestamp without time zone,
    "data_cancelamento" timestamp without time zone,
    "uid_inclusao" character varying(10),
    "uid_alteracao" character varying(10),
    "uid_cancelamento" character varying(10),
    "tabela" smallint NOT NULL DEFAULT 2006,
    "fornecedor" smallint NOT NULL DEFAULT 1,
    CONSTRAINT "composicoes_fk_insumo" FOREIGN KEY (insumo_grupo, insumo) REFERENCES insumos(grupo, insumo),
    CONSTRAINT "composicoes_fk_servicos" FOREIGN KEY (servico) REFERENCES servicos(servico),
    CONSTRAINT "composicoes_pkey" PRIMARY KEY (servico, insumo_grupo, insumo)
) WITH OIDS;

CREATE TABLE "precos" (
    "tabela" smallint NOT NULL,
    "insumo_grupo" character varying(6) NOT NULL,
    "insumo" character varying(10) NOT NULL,
    "fornecedor" smallint NOT NULL,
    "custo_produtivo" real,
    "custo_improdutivo" real,
    "data_coleta" timestamp without time zone,
    "data_inclusao" timestamp without time zone,
    "data_alteracao" timestamp without time zone,
    "data_cancelamento" timestamp without time zone,
    "uid_inclusao" character varying(10),
    "uid_alteracao" character varying(10),
    "uid_cancelamento" character varying(10),
    CONSTRAINT "precos_fornecedor_fk" FOREIGN KEY (fornecedor) REFERENCES cadastro.fornecedor(codigo_fornecedor) ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT "precos_insumos_fk" FOREIGN KEY (insumo_grupo, insumo) REFERENCES insumos(grupo, insumo),
    CONSTRAINT "precos_pk" PRIMARY KEY (tabela, insumo_grupo, insumo, fornecedor)
) WITH OIDS;

phpPgAdmin

Observe que na tabela de destino (precos), de onde queremos trazer uma informação, a ordem da chave é "tabela, insumo_grupo, insumo e fornecedor". Já em coposicoes, estes campos estão em ordem diferente. Mas no momento de criar a chave estrangeira devemos adicionar na mesma ordem da precos. Veja abaixo.

- Abrir a tabela (composicoes) para a qual queremos inserir uma chave estrangeira no phpPgAdmin

- Clicar na aba Restrição

- Clicar em adicionar chave estrangeira

- Digitar um nome para a chave: composicoes_precos_fk

- Agora vem a dica: selecione nesta ordem à esquerda e clique na seta dupla após selecionar cada um:

 - primeiro tabela

 - depois insumo_grupo

 - depois insumo

 - por fim fornecedor (caso altere esta ordem a chave não será criada)

- Em Tabela alvo selecione preco

- Clique em Adicionar (abaixo)

- Á esquerda selecione os quatro campos de precos (tabela, insumo_grupo, insumo e fornecedor) e clique na seta dupla para a direita.

- Clique em Adicionar abaixo.

PGAdmin

- Abrir a tabela no PgAdmin (duplo clique no nome da tabela à esquerda)

- Clique em Constraints

- Selecione Fireign Key abaixo e clique em Add

- Digite um nome em name "composicoes_precos_fk"

- Em references selecione "precos"

- Clique na aba Columns

- Em Local column selecione 'tabela'

- Em Referecing selecione 'tabela' e clique em Add

- De forma semelhante adicione os 3 pares restantes: insumo_grupo - insumo_grupo, insumo - insumo e fornecedor - fornecedor.

Tive problema ao adicionar insumo, então adicionei fornecedor antes e depois insumo e foi bem.

- Então clique em OK, confira e OK novamente.

PlPgSQL Tutorial

Esta é uma das linguagens de funções armazenadas com que trabalha o PostgreSQL. É a mais popular das linguagens no PostgreSQL.

Delimitadores

O código de uma função plpgsql é especificado em CREATE FUNCTION como uma string literal delimitado por aspas.

1 Apóstrofo (corpo da função)

CREATE FUNCTION olamundo() RETURNS integer AS '

         ....

' LANGUAGE plpgsql;

Inicia após AS e termina antes de LANGUAGE

2 Apóstrofos (mascar string literal no corpo da função)

a_output := Blah;

SELECT * FROM users WHERE f_name=foobar;

4 Apóstrofos (string constante no corpo da função)

a_output := a_output || AND name LIKE 'foobar' AND xyz

Mais detalhes na documentação oficial.

Declaração de Variáveis

user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;

Sintaxe geral da declaração de variáveis:

nome [ CONSTANT ] tipo [ NOT NULL ] [ { DEFAULT | := } expressão ];

Exemplos:

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

Declarado Funções

CREATE FUNCTION func_escopo() RETURNS integer AS $$
DECLARE
quantidade integer := 30;

BEGIN
RAISE NOTICE 'Aqui a quantidade é %', quantidade; -- A quantidade aqui é 30
quantidade := 50;
--
-- Criar um sub-bloco
--
DECLARE
quantidade integer := 80;
BEGIN
RAISE NOTICE 'Aqui a quantidade é %', quantidade; -- A quantidade aqui é 80
END;
RAISE NOTICE 'Aqui a quantidade é %', quantidade; -- A quantidade aqui é 50
RETURN quantidade;
END;
$$ LANGUAGE plpgsql;

Execute
SELECT func_escopo() as escopo;

Obs.: O psql exibe as mensagens disparadas pelo RAISE.

Obs2.: BEGIN e END na plpgsql não agrupa transações, apenas grupos de comandos.

Alias para Parâmetros de Funções

Sintaxe:

nome ALIAS FOR $n;

Exemplos:

CREATE FUNCTION vendas_taxa(real) RETURNS real AS $$
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

$1 é um alias para o parâmetro da função que é do tipo real.

CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
DECLARE
    v_string ALIAS FOR $1;
    index ALIAS FOR $2;
BEGIN
    -- Aqui fazemos alguns cálculos usando v_string e index
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION concat_campos(tab tbl_clientes) RETURNS text AS $$
BEGIN
    RETURN tab.nome || '' '' || tab.email || '' '' || tab.cidade || '' '' || tab.estado;
END;
$$ LANGUAGE plpgsql;

Também podemos (mais claro) usar os nomes dos parâmetros explicitamente:

CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
    tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

O parâmetro OUT (output, saída) é mais útil quando retorna múltiplos parâmetros:

CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
    sum := x + y;
    prod := x * y;
END;
$$ LANGUAGE plpgsql

Executando Funções

Usa-se o select para executar uma função, como se fosse uma view ou consulta comum.

select sum_n_product(5, 6);

Tipos Polimórficos

Quanto tipos polimórficos (anyelement e anyarray) são usados para declarar de funções, um parâmetro especial ($0) é criado. Este tipo de dados é o atual retorno da função.

É inicializado como NULL e pode ser modificado pela função.

Exemplo

Função que trabalha com qualquer tipo de dados e que suporta o operador +:

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
    result ALIAS FOR $0;
BEGIN
    result := v1 + v2 + v3;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

Ou:

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
                                 OUT sum anyelement)
AS $$
BEGIN
    sum := v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql;

Veja, que ao usar OUT não há necessidade de retorno (já está implícito).

Copiando Tipos

variavel%TYPE

%TYPE fornece o tipo de dados de uma variável ou de um campo de tabela.

Para declarar uma variável com o mesmo tipo de dado de usuarios.id_usuario deve ser escrito:

id_usuario usuarios.id_usuario%TYPE;

Tipos row

Pode armazenar um registro resultante de um SELECT ou de um FOR.

nome nome_da_tabela%ROWTYPE;

nome nome_do_tipo_composto;

Os campos podem ser acessados com nomevariavel.nomecampo;

Exemplo de uso:

CREATE FUNCTION mesclar_campos(t_linha nome_da_tabela) RETURNS text AS $$
DECLARE
t2_linha nome_tabela2%ROWTYPE;
BEGIN
SELECT * INTO t2_linha FROM nome_tabela2 WHERE ... ;
RETURN t_linha.f1 || t2_linha.f3 || t_linha.f5 || t2_linha.f7;
END;
$$ LANGUAGE plpgsql;
SELECT mesclar_campos(t.*) FROM nome_da_tabela t WHERE ... ;

Tipo registro (record)

nome record;

Renomeando Variáveis

RENAME nome_antigo TO novo_nome;

CREATE FUNCTION logfunc1(logtxt text) RETURNS timestamp AS $$
BEGIN
INSERT INTO logtable VALUES (logtxt, 'now');
RETURN 'now';
END;
$$ LANGUAGE plpgsql;
e
CREATE FUNCTION logfunc2(logtxt text) RETURNS timestamp AS $$
DECLARE
curtime timestamp;
BEGIN
curtime := 'now';
INSERT INTO logtable VALUES (logtxt, curtime);
RETURN curtime;
END;
$$ LANGUAGE plpgsql;

Atribuições

identificador := expressão;

SELECT INTO meu_registro * FROM emp WHERE nome_emp = meu_nome;
IF NOT FOUND THEN
RAISE EXCEPTION ''não foi encontrado o empregado %!'', meu_nome;
END IF;

Execução de Expressão ou Consulta sem Resultado

PERFORM create_mv('cs_session_page_requests_mv', my_query);

Não Fazer Nada

NULL;

Por exemplo, os dois fragmentos de código a seguir são equivalentes:

BEGIN
	y := x / 0;
EXCEPTION
	WHEN division_by_zero THEN
	NULL; -- ignorar o erro
END;
BEGIN
	y := x / 0;
EXCEPTION
	WHEN division_by_zero THEN -- ignorar o erro
END;

Execução de Comandos Dinâmicos

EXECUTE cadeia_de_caracteres_do_comando;

EXECUTE 'UPDATE tbl SET '
|| quote_ident(nome_da_coluna)
|| ' = '
|| quote_literal(novo_valor)
|| ' WHERE key = '
|| quote_literal(valor_chave);

EXECUTE 'UPDATE tbl SET '
|| quote_ident(nome_da_coluna)
|| ' = $$'
|| novo_valor
|| '$$ WHERE key = '
|| quote_literal(valor_chave);

Estruturas de Controle

Return next

RETURN NEXT expressão;

Quando uma função PL/pgSQL é declarada como retornando SETOF algum_tipo, o procedimento a ser seguido é um pouco diferente. Neste caso, os itens individuais a serem retornados são especificados em comandos RETURN NEXT, e um comando RETURN final, sem nenhum argumento, é utilizado para indicar que a função chegou ao fim de sua execução. O comando RETURN NEXT pode ser utilizado tanto com tipos de dado escalares quanto compostos; no último caso toda uma “tabela” de resultados é retornada.

As funções que utilizam RETURN NEXT devem ser chamadas da seguinte maneira:

SELECT * FROM alguma_função();

Condicionais

• IF ... THEN
• IF ... THEN ... ELSE
• IF ... THEN ... ELSE IF
• IF ... THEN ... ELSIF ... THEN ... ELSE
• IF ... THEN ... ELSEIF ... THEN ... ELSE

IF linha_demo.sexo = 'm' THEN
sexo_extenso := 'masculino';
ELSE
IF linha_demo.sexo = 'f' THEN
sexo_extenso := 'feminino';
END IF;
END IF;

IF expressão_booleana THEN
instruções
[ ELSIF expressão_booleana THEN
instruções
[ ELSIF expressão_booleana THEN
instruções
...]]
[ ELSE
instruções ]
END IF;

IF numero = 0 THEN
resultado := 'zero';
ELSIF numero > 0 THEN
resultado := 'positivo';
ELSIF numero < 0 THEN
resultado := 'negativo';
ELSE
-- hmm, a única outra possibilidade é que o número seja nulo
resultado := 'NULL';
END IF;

Laços

[<<rótulo>>]
LOOP
instruções
END LOOP;

Exit

EXIT [ rótulo ] [ WHEN expressão ];

LOOP
-- algum processamento
IF contador > 0 THEN
EXIT; -- sair do laço
END IF;
END LOOP;
LOOP
-- algum processamento
EXIT WHEN contador > 0; -- mesmo resultado do exemplo acima
END LOOP;
BEGIN
-- algum processamento
IF estoque > 100000 THEN
EXIT; -- causa a saída do bloco BEGIN
END IF;
END;

While

[<<rótulo>>]
WHILE expressão LOOP
instruções
END LOOP;

WHILE quantia_devida > 0 AND saldo_do_certificado_de_bonus > 0 LOOP
-- algum processamento
END LOOP;
WHILE NOT expressão_booleana LOOP
-- algum processamento
END LOOP;

For (laços internos)

[<<rótulo>>]
FOR nome IN [ REVERSE ] expressão .. expressão LOOP
instruções
END LOOP;

FOR i IN 1..10 LOOP
-- algum processamento
RAISE NOTICE 'i é %', i;
END LOOP;
FOR i IN REVERSE 10..1 LOOP
-- algum processamento
END LOOP;

Laços através do resultado de consultas

[<<rótulo>>]
FOR registro_ou_linha IN comando LOOP
instruções
END LOOP;

CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
	mviews RECORD;
BEGIN
	PERFORM cs_log('Atualização das visões materializadas...');
		FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
		-- Agora "mviews" possui um registro de cs_materialized_views
		PERFORM cs_log('Atualizando a visão materializada ' || 							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('Fim da atualização das visões materializadas.');
	RETURN 1;
END;
$$ LANGUAGE plpgsql;

[<<rótulo>>]
FOR registro_ou_linha IN EXECUTE texto_da_expressão LOOP
instruções
END LOOP;

Capturar Erros

[ <<rótulo>> ]
[ DECLARE
declarações ]
BEGIN
instruções
EXCEPTION
WHEN condição [ OR condição ... ] THEN
instruções_do_tratador
[ WHEN condição [ OR condição ... ] THEN
instruções_do_tratador
... ]
END;

INSERT INTO minha_tabela(nome, sobrenome) VALUES('Tom', 'Jones');
BEGIN
	UPDATE minha_tabela SET nome = 'Joe' WHERE sobrenome = 'Jones';
	x := x + 1;
	y := x / 0;
EXCEPTION
	WHEN division_by_zero THEN
		RAISE NOTICE 'capturado division_by_zero';
		RETURN x;
END;

Declaração de Variáveis do tipo Cursor

Todos os acessos aos cursores na linguagem PL/pgSQL são feitos através de variáveis cursor, que sempre são do tipo de dado especial refcursor. Uma forma de criar uma variável cursor é simplesmente declará-la como sendo do tipo refcursor. Outra forma é utilizar a sintaxe de declaração de cursor, cuja forma geral é:

nome CURSOR [ ( argumentos ) ] FOR comando ;

Exemplos:

DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (chave integer) IS SELECT * FROM tenk1 WHERE unico1 = chave;

CREATE TABLE teste (col text);
INSERT INTO teste VALUES ('123');
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT col FROM teste;
RETURN $1;
END;
' LANGUAGE plpgsql;
BEGIN;
SELECT reffunc('funccursor');
reffunc
------------
funccursor
(1 linha)
FETCH ALL IN funccursor;

COMMIT;

Erros e Mensagens

RAISE nível 'formato' [, variável [, ...]];

Os níveis possíveis são DEBUG, LOG, INFO, NOTICE, WARNING, e EXCEPTION. O nível EXCEPTION causa um erro (que normalmente interrompe a transação corrente); os outros níveis apenas geram mensagens com diferentes níveis de prioridade. Se as mensagens de uma determinada prioridade são informadas ao cliente, escritas no log do servidor, ou as duas coisas, é controlado pelas variáveis de configuração log_min_messages e client_min_messages.

Procedimentos de Gatilho (Trigger)

É criado pelo comando CREATE FUNCTION, declarando o procedimento como uma função sem argumentos e que retorna o tipo trigger. Deve ser observado que a função deve ser declarada sem argumentos, mesmo que espere receber os argumentos especificados no comando CREATE TRIGGER — os argumentos do gatilho são passados através de TG_ARGV, conforme descrito abaixo.

Quando uma função escrita em PL/pgSQL é chamada como um gatilho, diversas variáveis especiais são criadas automaticamente no bloco de nível mais alto. São estas:

NEW

Tipo de dado RECORD; variável contendo a nova linha do banco de dados, para as operações de INSERT/UPDATE nos gatilhos no nível de linha. O valor desta variável é NULL nos gatilhos no nível de instrução.

OLD

Tipo de dado RECORD; variável contendo a antiga linha do banco de dados, para as operações de UPDATE/DELETE nos gatilhos no nível de linha. O valor desta variável é NULL nos gatilhos no nível de instrução.

TG_NAME

Tipo de dado name; variável contendo o nome do gatilho disparado.

TG_WHEN

Tipo de dado text; uma cadeia de caracteres contendo BEFORE ou AFTER, dependendo da definição do gatilho.

TG_LEVEL

Tipo de dado text; uma cadeia de caracteres contendo ROW ou STATEMENT, dependendo da definição do gatilho.

TG_OP

Tipo de dado text; uma cadeia de caracteres contendo INSERT, UPDATE, ou DELETE, informando para qual operação o gatilho foi disparado.

TG_RELID

Tipo de dado oid; o ID de objeto da tabela que causou o disparo do gatilho.

TG_RELNAME

Tipo de dado name; o nome da tabela que causou o disparo do gatilho.

TG_NARGS

Tipo de dado integer; o número de argumentos fornecidos ao procedimento de gatilho na instrução CREATE

TG_ARGV[]

Tipo de dado matriz de text; os argumentos da instrução CREATE TRIGGER. O contador do índice começa por 0.

Índices inválidos (menor que 0 ou maior ou igual a tg_nargs) resultam em um valor nulo.

Exemplo:

O gatilho deste exemplo garante que:

- quando é inserida ou atualizada uma linha na tabela, fica sempre registrado nesta linha o usuário que efetuou a inserção ou a atualização

- quando isto ocorreu.

- além disso, o gatilho verifica se é fornecido o nome do empregado

- e se o valor do salário é um número positivo.

CREATE TABLE emp (
	nome_emp text,
	salario integer,
	ultima_data timestamp,
	ultimo_usuario text
);

CREATE FUNCTION emp_gatilho() RETURNS trigger AS $emp_gatilho$
	BEGIN
		-- Verificar se foi fornecido o nome e o salário do empregado
		IF NEW.nome_emp IS NULL THEN
			RAISE EXCEPTION 'O nome do empregado não pode ser nulo';
		END IF;
		IF NEW.salario IS NULL THEN
			RAISE EXCEPTION '% não pode ter um salário nulo', NEW.nome_emp;
		END IF;
		-- Quem paga para trabalhar?
		IF NEW.salario < 0 THEN
			RAISE EXCEPTION '% não pode ter um salário negativo', NEW.nome_emp;
		END IF;
		-- Registrar quem alterou a folha de pagamento e quando
		NEW.ultima_data := 'now';
		NEW.ultimo_usuario := current_user;
		RETURN NEW;
	END;

$emp_gatilho$ LANGUAGE plpgsql;

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

INSERT INTO emp (nome_emp, salario) VALUES ('João',1000);
INSERT INTO emp (nome_emp, salario) VALUES ('José',1500);
INSERT INTO emp (nome_emp, salario) VALUES ('Maria',2500);

SELECT * FROM emp;

Gatilho para registrar inserções e atualizações

CREATE TABLE emp (
	nome_emp text,
	salario integer,
	usu_cria text, -- Usuário que criou a linha
	data_cria timestamp, -- Data da criação da linha
	usu_atu text, -- Usuário que fez a atualização
	data_atu timestamp -- Data da atualização
);

CREATE FUNCTION emp_gatilho() RETURNS trigger AS $emp_gatilho$
	BEGIN
		-- Verificar se foi fornecido o nome do empregado
		IF NEW.nome_emp IS NULL THEN
			RAISE EXCEPTION 'O nome do empregado não pode ser nulo';
		END IF;
		IF NEW.salario IS NULL THEN
			RAISE EXCEPTION '% não pode ter um salário nulo', NEW.nome_emp;
		END IF;
		-- Quem paga para trabalhar?
		IF NEW.salario < 0 THEN
			RAISE EXCEPTION '% não pode ter um salário negativo', NEW.nome_emp;
		END IF;
		-- Registrar quem criou a linha e quando
		IF (TG_OP = 'INSERT') THEN
			NEW.data_cria := current_timestamp;
			NEW.usu_cria := current_user;
		-- Registrar quem alterou a linha e quando
		ELSIF (TG_OP = 'UPDATE') THEN
			NEW.data_atu := current_timestamp;
			NEW.usu_atu := current_user;
		END IF;
		RETURN NEW;
END;
$emp_gatilho$ LANGUAGE plpgsql;

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

INSERT INTO emp (nome_emp, salario) VALUES ('João',1000);
INSERT INTO emp (nome_emp, salario) VALUES ('José',1500);
INSERT INTO emp (nome_emp, salario) VALUES ('Maria',250);
UPDATE emp SET salario = 2500 WHERE nome_emp = 'Maria';

SELECT * FROM emp;

Gatilho para auditoria

Todas as operações na tabela emp serão registradas na tabela emp_audit

CREATE TABLE emp (
	nome_emp text NOT NULL,
	salario integer
);

CREATE TABLE emp_audit(
	operacao char(1) NOT NULL,
	usuario text NOT NULL,
	data timestamp NOT NULL,
	nome_emp text NOT NULL,
	salario integer
);

CREATE OR REPLACE FUNCTION processa_emp_audit() RETURNS TRIGGER AS $emp_audit$
	BEGIN
		--
		-- Cria uma linha na tabela emp_audit para refletir a operação
		-- realizada na tabela emp. Utiliza a variável especial TG_OP
		-- para descobrir a operação sendo realizada.
		--
		IF (TG_OP = 'DELETE') THEN
			INSERT INTO emp_audit SELECT 'E', user, now(), OLD.*;
			RETURN OLD;
		ELSIF (TG_OP = 'UPDATE') THEN
			INSERT INTO emp_audit SELECT 'A', user, now(), NEW.*;
			RETURN NEW;
		ELSIF (TG_OP = 'INSERT') THEN
			INSERT INTO emp_audit SELECT 'I', user, now(), NEW.*;
			RETURN NEW;
		END IF;
		RETURN NULL; -- o resultado é ignorado uma vez que este é um gatilho AFTER
	END;
$emp_audit$ language plpgsql;

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
	FOR EACH ROW EXECUTE PROCEDURE processa_emp_audit();

INSERT INTO emp (nome_emp, salario) VALUES ('João',1000);
INSERT INTO emp (nome_emp, salario) VALUES ('José',1500);
INSERT INTO emp (nome_emp, salario) VALUES ('Maria',250);
UPDATE emp SET salario = 2500 WHERE nome_emp = 'Maria';
DELETE FROM emp WHERE nome_emp = 'João';

SELECT * FROM emp;

SELECT * FROM emp_audit;

Autoditoria ao nível de campos

CREATE TABLE emp (
	id serial PRIMARY KEY,
	nome_emp text NOT NULL,
	salario integer
);

CREATE TABLE emp_audit(
	usuario text NOT NULL,
	data timestamp NOT NULL,
	id integer NOT NULL,
	coluna text NOT NULL,
	valor_antigo text NOT NULL,
	valor_novo text NOT NULL
);

CREATE OR REPLACE FUNCTION processa_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
		--
		-- Não permitir atualizar a chave primária
		--
		IF (NEW.id <> OLD.id) THEN
			RAISE EXCEPTION 'Não é permitido atualizar o campo ID';
		END IF;
		--
		-- Inserir linhas na tabela emp_audit para refletir as alterações
		-- realizada na tabela emp.
		--
		IF (NEW.nome_emp <> OLD.nome_emp) THEN
			INSERT INTO emp_audit SELECT current_user, current_timestamp,
			NEW.id, 'nome_emp', OLD.nome_emp, NEW.nome_emp;
		END IF;
		IF (NEW.salario <> OLD.salario) THEN
			INSERT INTO emp_audit SELECT current_user, current_timestamp,
			NEW.id, 'salario', OLD.salario, NEW.salario;
		END IF;
		RETURN NULL; -- o resultado é ignorado uma vez que este é um gatilho AFTER
	END;
$emp_audit$ language plpgsql;

CREATE TRIGGER emp_audit
AFTER UPDATE ON emp
	FOR EACH ROW EXECUTE PROCEDURE processa_emp_audit();

INSERT INTO emp (nome_emp, salario) VALUES ('João',1000);
INSERT INTO emp (nome_emp, salario) VALUES ('José',1500);
INSERT INTO emp (nome_emp, salario) VALUES ('Maria',2500);
UPDATE emp SET salario = 2500 WHERE id = 2;
UPDATE emp SET nome_emp = 'Maria Cecília' WHERE id = 3;
UPDATE emp SET id=100 WHERE id=1;
ERRO: Não é permitido atualizar o campo ID

SELECT * FROM emp;

SELECT * FROM emp_audit;

Gatilho para manter uma tabela sumário

O esquema que está detalhado a seguir é parcialmente baseado no exemplo Grocery Store do livro The Data Warehouse Toolkit de Ralph Kimball.

--
-- Main tables - time dimension and sales fact.
--

CREATE TABLE time_dimension (
	time_key integer NOT NULL,
	day_of_week integer NOT NULL,
	day_of_month integer NOT NULL,
	month integer NOT NULL,
	quarter integer NOT NULL,
	year integer NOT NULL
);

CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);

CREATE TABLE sales_fact (
	time_key integer NOT NULL,
	product_key integer NOT NULL,
	store_key integer NOT NULL,
	amount_sold numeric(12,2) NOT NULL,
	units_sold integer NOT NULL,
	amount_cost numeric(12,2) NOT NULL
);

CREATE INDEX sales_fact_time ON sales_fact(time_key);

--
-- Summary table - sales by time.
--

CREATE TABLE sales_summary_bytime (
	time_key integer NOT NULL,
	amount_sold numeric(15,2) NOT NULL,
	units_sold numeric(12) NOT NULL,
	amount_cost numeric(15,2) NOT NULL
);

CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);

--
-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
--

CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS
$maint_sales_summary_bytime$
	DECLARE
		delta_time_key integer;
		delta_amount_sold numeric(15,2);
		delta_units_sold numeric(12);
		delta_amount_cost numeric(15,2);
	BEGIN
		-- Work out the increment/decrement amount(s).
		IF (TG_OP = 'DELETE') THEN
			delta_time_key = OLD.time_key;
			delta_amount_sold = -1 * OLD.amount_sold;
			delta_units_sold = -1 * OLD.units_sold;
			delta_amount_cost = -1 * OLD.amount_cost;
		ELSIF (TG_OP = 'UPDATE') THEN
			-- forbid updates that change the time_key -
			-- (probably not too onerous, as DELETE + INSERT is how most
			-- changes will be made).
			IF ( OLD.time_key != NEW.time_key) THEN
				RAISE EXCEPTION 'Update of time_key : % -> % not allowed', OLD.time_key,
				NEW.time_key;
			END IF;
			delta_time_key = OLD.time_key;
			delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
			delta_units_sold = NEW.units_sold - OLD.units_sold;
			delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
		ELSIF (TG_OP = 'INSERT') THEN
			delta_time_key = NEW.time_key;
			delta_amount_sold = NEW.amount_sold;
			delta_units_sold = NEW.units_sold;
			delta_amount_cost = NEW.amount_cost;
		END IF;
		-- Update the summary row with the new values.
		UPDATE sales_summary_bytime
			SET amount_sold = amount_sold + delta_amount_sold,
				units_sold = units_sold + delta_units_sold,
				amount_cost = amount_cost + delta_amount_cost
		WHERE time_key = delta_time_key;

		-- There might have been no row with this time_key (e.g new data!).
		IF (NOT FOUND) THEN
			BEGIN
				INSERT INTO sales_summary_bytime (
					time_key,
					amount_sold,
					units_sold,
					amount_cost)
			VALUES (
				delta_time_key,
				delta_amount_sold,
				delta_units_sold,
				delta_amount_cost
				);
			EXCEPTION
				--
				-- Catch race condition when two transactions are adding data
				-- for a new time_key.
				--
				WHEN UNIQUE_VIOLATION THEN
					UPDATE sales_summary_bytime
						SET amount_sold = amount_sold + delta_amount_sold,
							units_sold = units_sold + delta_units_sold,
							amount_cost = amount_cost + delta_amount_cost
						WHERE time_key = delta_time_key;
			END;
		END IF;
		RETURN NULL;
	END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;

CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
	FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();

Este é um resumo da documentação oficial em inglês e em português do Brasil, que devem ser consultados para informações mais detalhadas em:

- http://www.postgresql.org/docs/8.2/interactive/index.html

- http://pgdocptbr.sourceforge.net/pg80/index.html