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

6 - Funções no PostgreSQL

O PostgreSQL oferece quatro tipos de funções:

• Funções escritas em SQL
• Funções em linguagens de procedimento (PL/pgSQL, PL/Tcl, PL/php, PL/Java, etc)
• Funções internas (rount(), now(), max(), count(), etc).
• Funções na linguagem C

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
    [ RETURNS rettype ]
  { LANGUAGE langname
    | IMMUTABLE | STABLE | VOLATILE
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...
    [ WITH ( attribute [, ...] ) ]

Para reforçar a segurança é interessante usar o parâmetro SECURITY DEFINER, que especifica que a função será executada com os privilégios do usuário que a criou.

• SECURITY INVOKER indica que a função deve ser executada com os privilégios do usuário que a chamou (padrão).
• SECURITY DEFINER especifica que a função deve ser executada com os privilégios do usuário que a criou.

Uma grande força do PostgreSQL é que ele permite a criação de funções pelo usuário em diversas linguagens: SQL, PlpgSQL, TCL, Perl, Phyton, Ruby.

Para ter exemplos a disposição vamos instalar os do diretório "tutorial" dos fontes do PostgreSQL:

• Acessar /usr/local/src/postgresql-8.1.3/src/tutorial e executar: make install

Feito isso teremos 5 arquivos .sql.

• O syscat.sql traz consultas sobre o catálogo de sistema, o que se chama de metadados (metadata).
• O basic.sql e o advanced.sql são consultas SQL.
• O complex.sql trata da criação de um tipo de dados pelo usuário e seu uso.
• O func.sql traz algumas funções em SQL e outras em C.

6.1 – Funções em SQL

O que outros SGBDs chamam de stored procedures o PostgreSQL chama de funções, que podem ser em diversas linguagens.

CREATE OR REPLACE FUNCTION olamundo() RETURNS int4
AS 'SELECT 1' LANGUAGE 'sql';
SELECT olamundo() ;
CREATE OR REPLACE FUNCTION add_numeros(nr1 int4, nr2 int4) RETURNS int4 
AS 'SELECT $1 + $2' LANGUAGE 'sql';
SELECT add_numeros(300, 700) AS resposta ;

Podemos passar como parâmetro o nome de uma tabela:

CREATE TEMP TABLE empregados (
	nome text,
	salario numeric,
	idade integer,
	baia point
);

INSERT INTO empregados VALUES('João',2200,21,point('(1,1)'));
INSERT INTO empregados VALUES('José',4200,30,point('(2,1)'));

CREATE FUNCTION dobrar_salario(empregados) RETURNS numeric AS $$
	SELECT $1.salario * 2 AS salario;
$$ LANGUAGE SQL;

SELECT nome, dobrar_salario(emp.*) AS sonho
	FROM empregados
	WHERE empregados.baia ~= point '(2,1)';

Algumas vezes é prático gerar o valor do argumento composto em tempo de execução. Isto pode ser feito através da construção ROW.

SELECT nome, dobrar_salario(ROW(nome, salario*1.1, idade, baia)) AS sonho
	FROM empregados;

Função que retorna um tipo composto. Função que retorna uma única linha da tabela empregados:

CREATE FUNCTION novo_empregado() RETURNS empregados AS $$
	SELECT text 'Nenhum' AS nome,
	1000.0 AS salario,
	25 AS idade,
	point '(2,2)' AS baia;
$$ LANGUAGE SQL;

Ou

CREATE OR REPLACE FUNCTION novo_empregado() RETURNS empregados AS $$
	SELECT ROW('Nenhum', 1000.0, 25, '(2,2)')::empregados;
$$ LANGUAGE SQL;

Chamar assim:

SELECT novo_empregado();

ou

SELECT * FROM novo_empregado();

Funções SQL como fontes de tabelas

CREATE TEMP TABLE teste (testeid int, testesubid int, testename text);
INSERT INTO teste VALUES (1, 1, 'João');
INSERT INTO teste VALUES (1, 2, 'José');
INSERT INTO teste VALUES (2, 1, 'Maria');

CREATE FUNCTION getteste(int) RETURNS teste AS $$
   SELECT * FROM teste WHERE testeid = $1;
$$ LANGUAGE SQL;
SELECT *, upper(testename) FROM getteste(1) AS t1;

Tabelas Temporárias - criar tabelas temporárias (TEMP), faz com que o servidor se encarregue de removê-la (o que faz logo que a conexão seja encerrada).

CREATE TEMP TABLE nometabela (campo tipo);

Funções SQL retornando conjunto

CREATE FUNCTION getteste(int) RETURNS SETOF teste AS $$
	SELECT * FROM teste WHERE testeid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getteste(1) AS t1;

Funções SQL polimórficas

As funções SQL podem ser declaradas como recebendo e retornando os tipos polimórficos anyelement e anyarray.

CREATE FUNCTION constroi_matriz(anyelement, anyelement) RETURNS anyarray AS $$
	SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;

SELECT constroi_matriz(1, 2) AS intarray, constroi_matriz('a'::text, 'b') AS textarray;

CREATE FUNCTION eh_maior(anyelement, anyelement) RETURNS boolean AS $$
	SELECT $1 > $2;
$$ LANGUAGE SQL;

SELECT eh_maior(1, 2);

Mais detalhes no capítulo 31 do manual.