PostgreSQL Prático/Funções Definidas pelo Usuário e Triggers/PlpgSQL

Adicionar hiperligações
Origem: Wikilivros, livros abertos por um mundo aberto.

6.2 - Funções em Pl/pgSQL[editar | editar código-fonte]

As funções em linguagens procedurais no PostgreSQL, como a Pl/pgSQL são correspondentes ao que se chama comumente de Stored Procedures. Por default o PostgreSQL só traz suporte às funções na linguagem SQL. Para dar suporte à funções em outras linguagens temos que efetuar procedimentos como a seguir.

Para que o banco postgres tenha suporte à linguagem de procedimento Pl/PgSQL executamos na linha de comando como super usuário do PostgreSQL:

createlang plpgsql –U nomeuser nomebanco no prompt ou create language plpgsql por SQL.

PGSQL 8.4

createlang -U nomeuser -W -e plpgsql nomebanco

A Pl/pgSQL é a linguagem de procedimentos armazenados mais utilizada no PostgreSQL, devido ser a mais madura e com mais recursos. 

//Código-fonte
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;

=> SELECT func_escopo();



CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
DECLARE
   v_string ALIAS FOR $1;
   index    ALIAS FOR $2;
BEGIN
   -- algum processamento neste ponto
END;

$$ LANGUAGE plpgsql;

Exemplo concatenar campos. 

CREATE FUNCTION concatenar_campos_selecionados(in_t nome_da_tabela) RETURNS text AS $$
BEGIN
   RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;

$$ LANGUAGE plpgsql;

Exemplo para somar 3 Valores 

CREATE FUNCTION somar_tres_valores(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
   resultado ALIAS FOR $0;
BEGIN
   resultado := v1 + v2 + v3;
   RETURN resultado;
END;

$$ LANGUAGE plpgsql;

SELECT somar_tres_valores(10,20,30);

Utilização de tipo composto: 

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 ... ;

Temos uma tabela (datas) com dois campos (data e hora) e queremos usar uma função para manipular os dados desta tabela: 

CREATE or REPLACE FUNCTION data_ctl(opcao char, fdata date, fhora time) RETURNS char(10) AS $$
DECLARE
    opcao ALIAS FOR $1;
    vdata ALIAS FOR $2;
    vhora ALIAS FOR $3;
    retorno char(10);
BEGIN
    IF opcao = 'I' THEN 
	insert into datas (data, hora) values (vdata, vhora);
        retorno := 'INSERT';
    END IF;
    IF opcao = 'U' THEN 
	update datas set data = vdata, hora = vhora where data='1995-11-01';
        retorno := 'UPDATE';
    END IF;
    IF opcao = 'D' THEN 
	delete from datas where data = vdata;
        retorno := 'DELETE';
    ELSE
        retorno := 'NENHUMA';
    END IF;	
    RETURN retorno;
END;
$$
LANGUAGE plpgsql;

--select data_ctl('I','1996-11-01', '08:15');
select data_ctl('U','1997-11-01','06:36');
select data_ctl('U','1997-11-01','06:36');

Mais Detalhes no capítulo 35 do manual oficial.

Funções que Retornam Conjuntos de Registros (SETS) 

CREATE OR REPLACE FUNCTION codigo_empregado (codigo INTEGER)
   RETURNS SETOF INTEGER AS $$
   DECLARE
	registro RECORD;
	retval INTEGER;
   BEGIN
	FOR registro IN SELECT * FROM empregados WHERE salario >= $1 LOOP
		RETURN NEXT registro.departamento_cod;
	END LOOP;
	RETURN;
   END;
$$ language 'plpgsql';

select * from codigo_empregado (0);
select count (*), g from codigo_empregado (5000) g group by g;


Funções que retornam Registro Para criar funções em plpgsql que retornem um registro, antes precisamos criar uma variável composta do tipo ROWTYPE, descrevendo o registro (tupla) de saída da função. 

CREATE TABLE empregados(
  nome_emp text,
  salario int4,
  codigo int4 NOT NULL,
  departamento_cod int4,
  CONSTRAINT empregados_pkey PRIMARY KEY (codigo),
  CONSTRAINT empregados_departamento_cod_fkey FOREIGN KEY (departamento_cod)
      REFERENCES departamentos (codigo) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
) 

CREATE TABLE departamentos (codigo INT primary key,  nome varchar);
CREATE TYPE dept_media AS (minsal INT, maxsal INT, medsal INT);

create or replace function media_dept() returns dept_media as
$$
declare
	r dept_media%rowtype;
	dept record;
	bucket int8;
	counter int;
   begin
	bucket := 0;
	counter := 0;
	r.maxsal :=0;
	r.minsal :=0;
	for dept in select sum(salario) as salario, d.codigo as departamento
		from empregados e, departamentos d where e.departamento_cod = d.codigo
		group by departamento loop
	   counter := counter + 1;
	   bucket := bucket + dept.salario;

	   if r.maxsal <= dept.salario or r.maxsal = 0 then
		r.maxsal := dept.salario;
	   end if;

	   if r.minsal <= dept.salario or r.minsal = 0 then
		r.minsal := dept.salario;
	   end if;

	end loop;
	r.medsal := bucket/counter;
	return r;
   end
   $$ language 'plpgsql';


Funções que Retornam Conjunto de Registros (SETOF, Result Set) Também requerem a criação de uma variável (tipo definidopelo user) 

CREATE TYPE media_sal AS
	(deptcod int, minsal int, maxsal int, medsal int);
CREATE OR REPLACE FUNCTION medsal() RETURNS SETOF media_sal AS
$$
DECLARE
	s media_sal%ROWTYPE;
	salrec RECORD;
	bucket int;
	counter int;
BEGIN
	bucket :=0;
	counter :=0;
	s.maxsal :=0;
	s.minsal :=0;
	s.deptcod :=0;
	FOR salrec IN SELECT salario AS salario, d.codigo AS departamento
		FROM empregados e, departamentos d WHERE e.departamento_cod = d.codigo ORDER BY d.codigo LOOP
		IF s.deptcod = 0 THEN
			s.deptcod := salrec.departamento;
			s.minsal := salrec.salario;
			s.maxsal := salrec.salario;
			counter := counter + 1;
			bucket := bucket + salrec.salario;
		ELSE
			IF s.deptcod = salrec.departamento THEN
				IF s.maxsal <= salrec.salario THEN
					s.maxsal := salrec.salario;
				END IF;
				IF s.minsal >= salrec.salario THEN
					s.minsal := salrec.salario;
				END IF;
                                bucket := bucket + salrec.salario;
				counter := counter +1;
			ELSE
				s.medsal := bucket/counter;
				RETURN NEXT s;
				s.deptcod := salrec.departamento;
				s.minsal := salrec.salario;
				s.maxsal := salrec.salario;
				counter := 1;
				bucket := salrec.salario;
			END IF;
		END IF;
	END LOOP;
   	s.medsal := bucket/counter;
	RETURN NEXT s;
	RETURN;
END 
$$
LANGUAGE 'plpgsql';

select * from medsal()

Relacionando: 

select d.nome, a.minsal, a.maxsal, a.medsal 
from medsal() a, departamentos d 
where d.codigo = a.deptcod
Obtido em "https://pt.wikibooks.org/w/index.php?title=PostgreSQL_Prático/Funções_Definidas_pelo_Usuário_e_Triggers/PlpgSQL&oldid=234408"