PostgreSQL Prático/Funções Definidas pelo Usuário e Triggers/SQL
6 - Funções no PostgreSQL
editarO 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.