Seções iMasters
Banco de Dados + PostgreSQL

Utilizando cursores no PostgreSQL

A utilização de cursores em
PostgreSQL com o auxílio da criação de Types pode se tornar uma
ferramenta muito poderosa. Imagine a criação de um relatório
ou de um procedimento
complexo com N tabelas e JOINS. Dentro dessa rotina do cursor,
você consegue escrever toda a regra de negócio e encaminhar para o
programador apenas um comando bem simples para ele executar, como vamos fazer aqui:

select * from imasters.cur_telefones(1) 

A grande
vantagem da utilização do cursor é quando, além da exibição
desses dados, é necessário fazer alguma operação sobre esses registros
como cálculos etc.

Ambiente utilizado:

  • Distribuição: Linux OpenSuse 11.3
  • Banco de dados: PostgreSQL 8.4.5
  • Ferramenta de Modelagem: DeZign For Databases 6.3.0
  • Gerenciador do banco de dados: pgAdmin3

O que é um Cursor?

Um cursor representa uma tabela temporariamente armazenada em memória. O objetivo do nosso artigo é listar todos os telefones de um determinado usuário
utilizando cursor no banco de dados PostgreSQL.

Modelagem do sistema

Criação do Banco de Dados

Utilizando o terminal no usuário postgres, vamos criar o banco de dados iMasters – o schema iMasters e a linguagem plpgsql:

su postgres
psql
CREATE DATABASE imasters;
q
psql -d imasters
CREATE SCHEMA imasters;
CREATE LANGUAGE plpgsql;
q

Screenshot:

Script de criação das tabelas

Os scripts abaixo podem ser executados utilizando o programa pgAdmin3 ou via terminal:


CREATE TABLE imasters.usuario (
usuario_id SERIAL NOT NULL,
nome CHARACTER VARYING(100) NOT NULL,
data_nasc DATE NOT NULL,
CONSTRAINT PK_usuario PRIMARY KEY (usuario_id)
);

COMMENT ON COLUMN imasters.usuario.usuario_id IS 'ID auto incremento';
COMMENT ON COLUMN imasters.usuario.nome IS 'Nome completo';
COMMENT ON COLUMN imasters.usuario.data_nasc IS 'Data de Nascimento';


CREATE TABLE imasters.telefone (
usuario_id INTEGER NOT NULL,
fone_id INTEGER NOT NULL,
ddd CHARACTER VARYING(2) NOT NULL,
numero CHARACTER VARYING(10) NOT NULL,
CONSTRAINT PK_telefone PRIMARY KEY (usuario_id, fone_id)
);

COMMENT ON COLUMN imasters.telefone.usuario_id IS 'ID do Usuario';
COMMENT ON COLUMN imasters.telefone.fone_id IS 'ID do Telefone';
COMMENT ON COLUMN imasters.telefone.ddd IS 'DDD do Telefone';
COMMENT ON COLUMN imasters.telefone.numero IS 'Numero do Telefone';


ALTER TABLE imasters.telefone ADD CONSTRAINT usuario_telefone
FOREIGN KEY (usuario_id) REFERENCES imasters.usuario (usuario_id);

Popular as tabelas

INSERT INTO imasters.usuario(nome, data_nasc)  VALUES('Júlio César Martini', '1980-10-07');
INSERT INTO imasters.usuario(nome,data_nasc) VALUES('Alexandre Hernandes Martini', '1983-07-30');

INSERT INTO imasters.telefone(usuario_id, fone_id, ddd, numero) VALUES(1, 1,'19', '35410000');
INSERT INTO imasters.telefone(usuario_id, fone_id, ddd, numero) VALUES(1, 2,'19', '81940000');
INSERT INTO imasters.telefone(usuario_id, fone_id, ddd, numero) VALUES(2, 1,'16', '37411234');
INSERT INTO imasters.telefone(usuario_id, fone_id, ddd, numero) VALUES(2, 2,'16', '96181256');

Criação do cursor para retornar os telefones do usuário selecionado

CREATE OR REPLACE FUNCTION imasters.cur_telefones(integer)
RETURNS SETOF imasters.telefone AS
$BODY$
DECLARE

--parametros
p_usuario_id ALIAS FOR $1;

--cursor
reg imasters.telefone%ROWTYPE;

BEGIN

--busca os telefones do usuario informado
FOR reg in
SELECT t.usuario_id, t.fone_id, t.ddd, t.numero
FROM imasters.telefone t
WHERE t.usuario_id = p_usuario_id

LOOP

RETURN NEXT reg;

END LOOP;

RETURN;

END;

$BODY$
LANGUAGE plpgsql VOLATILE;

Depurando

Analisando o código acima, temos a linha onde definimos a variável reg como sendo:

imasters.telefone%ROWTYPE 

Isso quer dizer que, quando executarmos a query que irá popular essa variável, ela irá herdar as características de cada campo dessa tabela. Executando o nosso cursor através do pgAdmin, passando o ID de um usuário, temos como resultado:

SELECT * FROM imasters.cur_telefones(1);

Resultado

Se preferir, em vez de exibir o ID do Usuário e do Fone ID, podemos visualizar apenas o DDD e o NÚMERO. Para isso, faremos a seguinte alteração na query:

SELECT ddd, numero 
FROM imasters.cur_telefones(1);

Resultado

E se eu quiser acrescentar o nome do usuário nessa query? Vocês me diriam: é só alterar a SQL do cursor e fazer um JOIN com a tabela de usuário e exibir o campo nome, correto? Vamos fazer:

CREATE OR REPLACE FUNCTION imasters.cur_telefones(integer)
RETURNS SETOF imasters.telefone AS
$BODY$
DECLARE

--parametros
p_usuario_id ALIAS FOR $1;

--cursor
reg imasters.telefone%ROWTYPE;

BEGIN

--busca os telefones do usuario informado
FOR reg in
SELECT u.nome, t.usuario_id, t.fone_id, t.ddd, t.numero
FROM imasters.usuario u
INNER JOIN imasters.telefone t ON(u.usuario_id = t.usuario_id)
WHERE t.usuario_id = p_usuario_id

LOOP

RETURN NEXT reg;

END LOOP;

RETURN;

END;

$BODY$
LANGUAGE plpgsql VOLATILE;

Executando a query: SELECT * FROM imasters.cur_telefones(1), teremos como resultado o erro: ERROR:  invalid input syntax for integer: “Júlio César Martini”. Mas por que isso acontece?

Esse erro acontece porque no retorno do cursor setamos RETURNS SETOF imasters.telefone. Ele espera que os campos sigam os datatypes da tabela
imasters.telefone, o que não ocorre, pois o
primeiro campo da tabela é o usuário_id, e estamos trazendo na query
o nome do usuário.

Alterando o cursor para trazer o nome do usuário

Vamos fazer uma alteração no cursor, para que ele traga os seguintes dados: Nome do Usuário, DDD e o Telefone. Mas, antes de fazer essa alteração, vamos falar um pouco sobre a criação de um TYPE em PostgreSQL.

Como você pode observar, não possuímos nenhuma tabela em nosso banco de dados que represente essa sequência: Nome do Usuário, DDD e o Telefone. Para suprir essa necessidade, criaremos um novo tipo de dado, utilizando o CREATE TYPE.

CREATE TYPE imasters.type_cur_telefones AS (
nome character varying(100)
, ddd character varying(2)
, telefone character varying(10)
);

Com o TYPE criado, devemos alterar o cursor para que em vez de ele retornar os campos baseados na tabela imasters.telefone, ele utilize o novo tipo de dado.

DROP FUNCTION IF EXISTS imasters.cur_telefones(integer) CASCADE;


CREATE OR REPLACE FUNCTION imasters.cur_telefones(integer)
RETURNS SETOF imasters.type_cur_telefones AS
$BODY$
DECLARE

--parametros
p_usuario_id ALIAS FOR $1;

--cursor
reg imasters.type_cur_telefones%ROWTYPE;

BEGIN

--busca os telefones do usuario informado
FOR reg in
SELECT u.nome, t.ddd, t.numero
FROM imasters.usuario u
INNER JOIN imasters.telefone t ON(u.usuario_id = t.usuario_id)
WHERE t.usuario_id = p_usuario_id

LOOP

RETURN NEXT reg;

END LOOP;

RETURN;

END;

$BODY$
LANGUAGE plpgsql VOLATILE;

Executando

SELECT * FROM imasters.cur_telefones(1);

Resultado

Conclusão

Vale ressaltar que neste artigo estamos trabalhando com cursores explícitos, que retornam dados baseados em um SELECT, mas podemos trabalhar com cursores implícitos, que apenas executam determinados procedimentos como INSERT, DELETE e UPDATE.

Opinem, deixem suas sugestões, afinal, a coluna é de vocês.

Até a próxima!

Comente também

8 Comentários

Caio Teixeira

Eu teria um efeito parecido utilizando view’s. Qual das duas formas (ponteiros ou views) vc sugere levando em conta a velocidade ?

Júlio César Martini

Olá Caio,
Esse artigo foi apenas um simples exemplo de utilização de cursores utilizando PostgreSQL. Como vc mesmo disse poderíamos ter usado uma View que teria o mesmo efeito. Nesse caso poderíamos modificar e implementar no nosso cursor além de enviar os dados fizesse alguma manipulação em algum dado (update, delete) ou até insert em outras tabelas.

Estefanio Luiz Almeida

Muito bom o artigo, bem didatico.

Rogério dos Santos Ferreira

Colegas,
Estou iniciando no PGS, e peço uma ajuda que talvez seja muito simples.
No MS-SQL, posso ler simultaneamente dois bancos, e até gravar essa leitura em um terceiro banco. Algo assim:
insert into banco2.dono.tabela2
select coluna1, coluna2 from banco1.dono.tabela1

Como fazer isso no postgre ??????

    Samuel Mendes

    Procure informação sobre dblink(). São funções para acesso a banco de dados remotos (na mesma instância ou servidores diferentes)

Luiz Marinho

Bom artgio Júlio.. Então quando podemos ver o cursores implícitos, que apenas executam determinados procedimentos como INSERT, DELETE e UPDATE.

Marcos

Porque o tipo da variável, não poderia ser do tipo RECORD?

Niguno da Silva

olá a todos, gostei bastante do artigo publicado pois foi de grande ajuda pra mim. Gostaria de informar que encontrei o mesmo artigo copiado na íntegra em outro site e achei uma tremenda sacanagem! Segue o link do mesmo para que o autor tome providências: http://www.f2sistemas.com.br/site/utilizando-cursores-no-postgresql.html

Qual a sua opinião?