35.8. Cursores

PostgreSQL 14.5: Cursores

Em vez de executar toda a consulta de uma vez, é possível definir um cursor encapsulando a consulta e, depois, ler umas poucas linhas do resultado da consulta de cada vez. Um dos motivos de se fazer desta maneira, é para evitar o uso excessivo de memória quando o resultado contém muitas linhas (Entretanto, normalmente não há necessidade dos usuários da linguagem PL/pgSQL se preocuparem com isto, uma vez que os laços FOR utilizam internamente um cursor para evitar problemas de memória, automaticamente). Uma utilização mais interessante é retornar a referência a um cursor criado pela função, permitindo a quem chamou ler as linhas. Esta forma proporciona uma maneira eficiente para a função retornar conjuntos grandes de linhas.

35.8.1. Declaração de variável cursor

Todos os acessos aos cursores na linguagem PL/pgSQL são feitos através de variáveis cursor, que sempre são do tipo de dado especial refcursor. Uma forma de criar uma variável cursor é simplesmente declará-la como sendo do tipo refcursor. Outra forma é utilizar a sintaxe de declaração de cursor, cuja forma geral é:

nome CURSOR [ ( argumentos ) ] FOR comando ;

(O FOR pode ser substituído por IS para ficar compatível com o Oracle). Os argumentos, quando especificados, são uma lista separada por vírgulas de pares nome tipo_de_dado. Esta lista define nomes a serem substituídos por valores de parâmetros na consulta. Os valores verdadeiros que substituirão estes nomes são especificados posteriormente, quando o cursor for aberto.

Alguns exemplos:

DECLARE
    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (chave integer) IS SELECT * FROM tenk1 WHERE unico1 = chave;

Todas estas três variáveis possuem o tipo de dado refcursor, mas a primeira pode ser utilizada em qualquer consulta, enquanto a segunda possui uma consulta totalmente especificada ligada à mesma, e a terceira possui uma consulta parametrizada ligada à mesma (O parâmetro chave será substituído por um valor inteiro quando o cursor for aberto). A variável curs1 é dita como desligada (unbound), uma vez quer não está ligada a uma determinada consulta.

35.8.2. Abertura de cursor

Antes do cursor poder ser utilizado para trazer linhas, este deve ser aberto (É a ação equivalente ao comando SQL DECLARE CURSOR). A linguagem PL/pgSQL possui três formas para a instrução OPEN, duas das quais utilizam variáveis cursor desligadas, enquanto a terceira utiliza uma variável cursor ligada.

35.8.2.1. OPEN FOR SELECT

OPEN cursor_desligado FOR SELECT ...;

A variável cursor é aberta e recebe a consulta especificada para executar. O cursor não pode estar aberto, e deve ter sido declarado como um cursor desligado, ou seja, simplesmente como uma variável do tipo refcursor. O comando SELECT é tratado da mesma maneira que nas outras instruções SELECT da linguagem PL/pgSQL: Os nomes das variáveis da linguagem PL/pgSQL são substituídos, e o plano de execução é colocado no cache para uma possível reutilização.

Exemplo:

OPEN curs1 FOR SELECT * FROM foo WHERE chave = minha_chave;

35.8.2.2. OPEN FOR EXECUTE

OPEN cursor_desligado FOR EXECUTE cadeia_de_caracteres_da_consulta;

A variável cursor é aberta e recebe a consulta especificada para executar. O cursor não pode estar aberto, e deve ter sido declarado como um cursor desligado, ou seja, simplesmente como uma variável do tipo refcursor. A consulta é especificada como uma expressão cadeia de caracteres da mesma maneira que no comando EXECUTE. Como habitual, esta forma provê flexibilidade e, portanto, a consulta pode variar entre execuções.

Exemplo:

OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);

35.8.2.3. Abertura de cursor ligado

OPEN cursor_ligado [ ( valores_dos_argumentos ) ];

Esta forma do OPEN é utilizada para abrir uma variável cursor cuja consulta foi ligada à mesma ao ser declarada. O cursor não pode estar aberto. Deve estar presente uma lista de expressões com os valores reais dos argumentos se, e somente se, o cursor for declarado como recebendo argumentos. Estes valores são substituídos na consulta. O plano de comando do cursor ligado é sempre considerado como passível de ser colocado no cache; neste caso não há forma EXECUTE equivalente.

Exemplos:

OPEN curs2;
OPEN curs3(42);

35.8.3. Utilização de cursores

Uma vez que o cursor tenha sido aberto, este pode ser manipulado pelas instruções descritas a seguir.

Para começar, não há necessidade destas manipulações estarem na mesma função que abriu o cursor. Pode ser retornado pela função um valor refcursor, e deixar por conta de quem chamou operar o cursor (Internamente, o valor de refcursor é simplesmente uma cadeia de caracteres com o nome do tão falado portal que contém a consulta ativa para o cursor. Este nome pode ser passado, atribuído a outras variáveis refcursor, e por aí em diante, sem perturbar o portal).

Todos os portais são fechados implicitamente ao término da transação. Portanto, o valor de refcursor pode ser utilizado para fazer referência a um cursor aberto até o fim da transação.

35.8.3.1. FETCH

FETCH cursor INTO destino;

A instrução FETCH coloca a próxima linha do cursor no destino, que pode ser uma variável linha, uma variável registro, ou uma lista separada por vírgulas de variáveis simples, da mesma maneira que no SELECT INTO. Como no SELECT INTO, pode ser verificada a variável especial FOUND para ver se foi obtida uma linha, ou não.

Exemplos:

FETCH curs1 INTO variável_linha;
FETCH curs2 INTO foo, bar, baz;

35.8.3.2. CLOSE

CLOSE cursor;

A instrução CLOSE fecha o portal subjacente ao cursor aberto. Pode ser utilizada para liberar recursos antes do fim da transação, ou para liberar a variável cursor para que esta possa ser aberta novamente.

Exemplo:

CLOSE curs1;

35.8.3.3. Retornar cursor

As funções PL/pgSQL podem retornar cursores para quem fez a chamada. É útil para retornar várias linhas ou colunas, especialmente em conjuntos de resultados muito grandes. Para ser feito, a função abre o cursor e retorna o nome do cursor para quem chamou (ou simplesmente abre o cursor utilizando o nome do portal especificado por, ou de outra forma conhecido por, quem chamou). Quem chamou poderá então ler as linhas usando o cursor. O cursor pode ser fechado por quem chamou, ou será fechado automaticamente ao término da transação.

O nome do portal utilizado para o cursor pode ser especificado pelo programador ou gerado automaticamente. Para especificar o nome do portal deve-se, simplesmente, atribuir uma cadeia de caracteres à variável refcursor antes de abri-la. O valor cadeia de caracteres da variável refcursor será utilizado pelo OPEN como o nome do portal subjacente. Entretanto, quando a variável refcursor é nula, o OPEN gera automaticamente um nome que não conflita com nenhum portal existente, e atribui este nome à variável refcursor.

Nota: Uma variável cursor ligada é inicializada com o valor cadeia de caracteres que representa o seu nome e, portanto, o nome do portal é o mesmo da variável cursor, a menos que o programador mude este nome fazendo uma atribuição antes de abrir o cursor. Porém, uma variável cursor desligada tem inicialmente o valor nulo por padrão e, portanto, recebe um nome único gerado automaticamente, a menos que este seja mudado.

O exemplo a seguir mostra uma maneira de fornecer o nome do cursor por quem chama:

CREATE TABLE teste (col text);
INSERT INTO teste VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM teste;
    RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');

  reffunc
------------
 funccursor
(1 linha)

FETCH ALL IN funccursor;

 col
-----
 123
(1 linha)

COMMIT;

O exemplo a seguir usa a geração automática de nome de cursor:

CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
    ref refcursor;
BEGIN
    OPEN ref FOR SELECT col FROM teste;
    RETURN ref;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc2();

      reffunc2
--------------------
 <unnamed portal 1>
(1 linha)

FETCH ALL IN "<unnamed cursor 1>";

 col
-----
 123
(1 linha)

COMMIT;

Os exemplos a seguir mostram uma maneira de retornar vários cursores de uma única função:

CREATE FUNCTION minha_funcao(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT * FROM tabela_1;
    RETURN NEXT $1;
    OPEN $2 FOR SELECT * FROM tabela_2;
    RETURN NEXT $2;
    RETURN;
END;
$$ LANGUAGE plpgsql;

-- é necessário estar em uma transação para poder usar cursor
BEGIN;

SELECT * FROM minha_funcao('a', 'b');

FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;
SourceForge.net Logo CSS válido!