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!

De 0 a 10, o quanto você recomendaria este artigo para um amigo?