37.8. Curseurs

Plutôt que d'exécuter la totalité d'une requête à la fois, il est possible de créer un curseur qui encapsule la requête, puis en lit le résultat quelques lignes à la fois. Une des raisons pour faire de la sorte est d'éviter les surcharges de mémoire quand le résultat contient un grand nombre de lignes (cependant, les utilisateurs PL/pgSQL n'ont généralement pas besoin de se préoccuper de cela puisque les boucles FOR utilisent automatiquement un curseur en interne pour éviter les problèmes de mémoire). Un usage plus intéressant est de renvoyer une référence à un curseur qu'elle a créé, permettant à l'appelant de lire les lignes. Ceci fournit un moyen efficace de renvoyer de grands ensembles de lignes à partir des fonctions.

37.8.1. Déclaration de variables curseur

Tous les accès aux curseurs dans PL/pgSQL se font par les variables curseur, qui sont toujours du type de données spécial refcursor. Un des moyens de créer une variable curseur est de simplement la déclarer comme une variable de type refcursor. Un autre moyen est d'utiliser la syntaxe de déclaration de curseur qui est en général :

nom CURSOR [ ( arguments ) ] FOR requête ;

(FOR peut être remplacé par IS pour la compatibilité avec Oracle). arguments, si spécifié, est une liste de paires de nom type-de-donnée qui définit les noms devant être remplacés par les valeurs des paramètres dans la requête donnée. La valeur effective à substituer pour ces noms sera spécifiée plus tard lors de l'ouverture du curseur.

Quelques exemples :

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

Ces variables sont toutes trois du type de données refcursor, mais la première peut être utilisées avec n'importe quelle requête, alors que la seconde a une requête complètement spécifiée qui lui est déjà liée, et la dernière est liée à une requête paramétrée. (key sera remplacée par un paramètre de valeur entière lors de l'ouverture du curseur.) La variable curs1 est dite non liée puisqu'elle n'est pas liée a une requête particulière.

37.8.2. Ouverture De Curseurs

Avant qu'un curseur puisse être utilisé pour rapatrier des lignes, il doit être ouvert. (C'est l'action équivalente de la commande SQL DECLARE CURSOR.) PL/pgSQL a trois formes pour l'instruction OPEN, dont deux utilisent des variables curseur non liées et les autres utilisent une variable curseur liée.

37.8.2.1. OPEN FOR SELECT

OPEN curseur-non-lié FOR SELECT ...;

La variable curseur est ouverte et reçoit la requête spécifiée à exécuter. Le curseur ne peut pas être déjà ouvert, et il doit avoir été déclaré comme curseur non lié. (c'est à dire comme une simple variable refcursor). La requête SELECT est traitée de la même façon que les autres instructionsSELECT dans PL/pgSQL : les noms de variables PL/pgSQL sont remplacés, et le plan de requête est mis en cache pour une possible réutilisation.

Exemple :

OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

37.8.2.2. OPEN FOR EXECUTE

OPEN curseur-non-lié FOR EXECUTE chaîne-requête;

La variable curseur est ouverte et reçoit la requête spécifiée à exécuter. Le curseur ne peut pas être déjà ouvert et il doit avoir été déclaré comme curseur non-lié (c'est-à-dire comme une simple variable refcursor. La requête est spécifiée comme une expression chaîne de la même façon que dans une commande EXECUTE. Comme d'habitude, ceci donne assez de flexibilité pour que la requête puisse changer d'une exécution à l'autre.

Exemple :

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

37.8.2.3. Ouverture d'un curseur lié

OPEN curseur-lié [ ( arguments ) ];

Cette forme d'OPEN est utilisée pour ouvrir une variable curseur à laquelle la requête est liée au moment de la déclaration. Le curseur ne peut pas être déjà ouvert. Une liste des expressions arguments doit apparaître si et seulement si le curseur a été déclaré comme acceptant des arguments. Ces valeurs seront remplacées dans la requête. Le plan de requête pour un curseur lié est toujours considéré comme pouvant être mis en cache ; il n'y a pas d'équivalent de la commande EXECUTE dans ce cas.

Exemples :

OPEN curs2;
OPEN curs3(42);

37.8.3. Utilisation des Curseurs

Une fois qu'un curseur a été ouvert, il peut être manipulé grâce aux instructions décrites ci-dessous.

Ces manipulations n'ont pas besoin de se dérouler dans la même fonction que celle qui a ouvert le curseur. Vous pouvez renvoyer une valeur refcursor à partir d'une fonction et laisser l'appelant opérer sur le curseur (d'un point de vue interne, une valeur refcursor est simplement la chaîne de caractères du nom d'un portail contenant la requête active pour le curseur. Ce nom peut être passé à d'autres, assigné à d'autres variables refcursor et ainsi de suite, sans déranger le portail).

Tous les portails sont implicitement fermés à la fin de la transaction. C'est pourquoi une valeur refcursor est utilisable pour référencer un curseur ouvert seulement jusqu'à la fin de la transaction.

37.8.3.1. FETCH

FETCH curseur INTO cible;

FETCH rapatrie le rang suivant depuis le curseur dans une cible, qui peut être une variable ligne, une variable record ou une liste de simples variables séparées d'une virgule, exactement comme SELECT INTO. Comme pour SELECT INTO, la variable spéciale FOUND peut être vérifiée pour voir si une ligne a été obtenue ou pas.

Exemple :

FETCH curs1 INTO var_ligne;
FETCH curs2 INTO foo, bar, baz;

37.8.3.2. CLOSE

CLOSE curseur;

CLOSE ferme le portail sous-tendant un curseur ouvert. Ceci peut être utilisé pour libérer des ressources avant la fin de la transaction ou de libérer la variable curseur pour pouvoir la réouvrir.

Exemple :

CLOSE curs1;

37.8.3.3. Le Renvoi de Curseurs

PL/pgSQL functions peut renvoyer des curseurs à l'appelant Ceci est utile pour renvoyer plusieurs lignes ou colonnes, spécifiquement avec des ensembles de résultats très importants. Pour cela, la fonction ouvre le curseur et renvoie le nom du curseur à l'appelant (ou simplement ouvre le curseur en utilisant un nom de portail spécifié par ou, sinon, connu, par l'appelant). L'appelant peut ensuite récupérer les lignes à partir du curseur. Le curseur est fermé par l'appelant. Sinon, il sera automatiquement fermé à la fin de la transaction.

Le nom du portail utilisé pour un curseur peut être spécifié par le développeur ou être généré automatiquement. Pour spécifier un nom de portail, affectez simplement une chaîne à la variable refcursor avant de l'ouvrir. La valeur de type chaîne de la variable refcursor sera utilisée par OPEN comme nom du portail sous-jacent. Néanmoins, si la variable refcursor est NULL, OPEN génère automatiquement un nom qui n'entre pas en conflit avec tout autre portail existant et l'affecte à la variable refcursor.

Note : Une variable curseur limitée est initialisée avec la chaîne représentant son nom, de façon à ce que le nom du portail soit le même que le nom de la variable du curseur sauf si le développeur le surcharge par affectation avant d'ouvrir le curseur. Mais une variable curseur non limitée aura initialement par défaut la valeur NULL, donc il recevra un nom unique généré automatiquement, sauf s'il est surchargé.

L'exemple suivant montre une façon de fournir un nom de curseur à l'appelant :

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

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

BEGIN;
SELECT fonction_reference('curseur_fonction');
FETCH ALL IN curseur_fonction;
COMMIT;

L'exemple suivant utilise la génération automatique du nom du curseur :

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

BEGIN;
SELECT fonction_reference2();

   fonction_reference2
--------------------------
 <unnamed cursor 1>
(1 row)

FETCH ALL IN "<unnamed cursor 1>";
COMMIT;