39.7. Curseurs

OPEN var_curseur_nonlie [ [ NO ] SCROLL ] FOR requete;

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 une variable de curseur non lié (c'est-à-dire comme une simple variable refcursor). La requête doit être un SELECT ou quelque chose d'autre qui renvoie des lignes (comme EXPLAIN). La requête est traitée de la même façon que les autres commandes SQL dans PL/pgSQL : les noms de variables PL/pgSQL sont substitués et le plan de requête est mis en cache pour une possible ré-utilisation. Quand une variable PL/pgSQL est substituée dans une requête de type curseur, la valeur qui est substituée est celle qu'elle avait au moment du OPEN ; les modifications ultérieures n'auront pas affectées le comportement du curseur. Les options SCROLL et NO SCROLL ont la même signification que pour un curseur lié.

Exemple :

OPEN curs1 FOR SELECT * FROM foo WHERE cle = ma_cle;

OPEN var_curseur_nonlie [ [ NO ] SCROLL ] FOR EXECUTE requete
                                     [ USING expression [, ... ] ];

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 une variable de 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 le plan de la requête puisse changer d'une exécution à l'autre (voir la Section 39.10.2, « Mise en cache du plan »), et cela signifie aussi que la substitution de variable n'est pas faite sur la chaîne de commande. Comme avec la commande EXECUTE, les valeurs de paramètre peuvent être insérées dans la commande dynamique avec USING. Les options SCROLL et NO SCROLL ont la même signification que pour un curseur lié.

Exemple :

OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident(tabname) 
                                        || ' WHERE col1 = $1' USING keyvalue;

Dans cet exemple, le nom de la table est insérée dans la requête textuellement et l'utilisation de quote_ident() est recommandé afin de se prémunir contre des injections SQL. La valeur de comparaison pour col1 esr insérée avec la paramètre USING et n'a donc pas besoin d'être protégé.

OPEN var_curseur_lié [ ( [ nom_argument := ] valeur_argument [, ...] ) ];

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. Notez que SCROLL et NO SCROLL ne peuvent pas être indiqués dans OPEN car le comportement du curseur était déjà déterminé.

Les valeurs des arguments peuvent être passées en utilisant soit la notation en position soit la notation nommée. Dans la première, tous les arguments sont indiqués dans l'ordre. Dans la seconde, chaque nom d'argument est indiqué en utilisant := pour la séparer de l'expression de l'argument. De façon similaire à l'appel de fonctions, décrit dans Section 4.3, « Fonctions appelantes », il est aussi autorisé de mixer notation en position et notation nommée.

Voici quelques exemples (ils utilisent les exemples de déclaration de curseur ci-dessus) :

OPEN curs2;
OPEN curs3(42);
OPEN curs3(key := 42);

Comme la substitution de variable est faite sur la requête d'un curseur lié, il existe en fait deux façons de passer les valeurs au curseur : soit avec un argument explicite pour OPEN soit en référençant implicitement une variable PL/pgSQL dans la requête. Néanmoins, seules les variables déclarées avant que le curseur lié ne soit déclaré lui seront substituées. Dans tous les cas, la valeur passée est déterminée au moment de l'exécution de la commande OPEN. Par exemple, une autre façon d'obtenir le même effet que l'exemple curs3 ci-dessus est la suivante :

DECLARE
    key integer;
    curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
BEGIN
    key := 42;
    OPEN curs4;

FETCH [ direction { FROM | IN } ] curseur INTO cible;

FETCH récupère la prochaine ligne à partir d'un curseur et la place dans une cible, qui peut être une variable ligne, une variable record ou une liste de variables simples séparées par des virgules, comme dans un SELECT INTO. S'il n'y a pas de ligne suivante, la cible est mise à NULL. Comme avec SELECT INTO, la variable spéciale FOUND peut être lue pour voir si une ligne a été récupérée.

La clause direction peut être une des variantes suivantes autorisées pour la commande SQL FETCH(7) sauf celles qui peuvent récupérer plus d'une ligne ; nommément, cela peut être NEXT, PRIOR, FIRST, LAST, ABSOLUTE nombre, RELATIVE nombre, FORWARD ou BACKWARD. Omettre direction est identique à spécifier NEXT. Les valeurs direction qui nécessitent d'aller en sens inverse risquent d'échouer sauf si le curseur a été déclaré ou ouvert avec l'option SCROLL.

curseur doit être le nom d'une variable refcursor qui référence un portail de curseur ouvert.

Exemples :

FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;

MOVE [ direction { FROM | IN } ] curseur;

MOVE repositionne un curseur sans récupérer de données. MOVE fonctionne exactement comme la commande FETCH sauf qu'elle ne fait que repositionner le curseur et ne renvoie donc pas les lignes du déplacement. Comme avec SELECT INTO, la variable spéciale FOUND peut être lue pour vérifier s'il y avait bien les lignes correspondant au déplacement.

La clause de direction peut être l'une des variantes autorisées dna sla commande SQL FETCH(7), nommément NEXT, PRIOR, FIRST, LAST, ABSOLUTE nombre, RELATIVE nombre, ALL, FORWARD [ nombre | ALL ], ou BACKWARD [ nombre | ALL ]. Omettre direction est identique à spécifier NEXT. Les valeurs direction qui nécessitent de se déplacer en arrière risquent d'échouer sauf si le curseur a été déclaré ou ouvert avec l'option SCROLL.

Exemples :

MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;

UPDATE table SET ... WHERE CURRENT OF curseur;
DELETE FROM table WHERE CURRENT OF curseur;

Quand un curseur est positionné sur une ligne d'une table, cette ligne peut être mise à jour ou supprimée en utilisant le curseur qui identifie la ligne. Il existe des restrictions sur ce que peut être la requête du curseur (en particulier, pas de regroupement) et il est mieux d'utiliser FOR UPDATE dans le curseur. Pour des informations supplémentaires, voir la page de référence DECLARE(7).

Un exemple :

UPDATE foo SET valdonnee = mavaleur WHERE CURRENT OF curs1;

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 pour libérer la variable curseur pour pouvoir la réouvrir.

Exemple :

CLOSE curs1;

Les fonctions PL/pgSQL peuvent renvoyer des curseurs à l'appelant. Ceci est utile pour renvoyer plusieurs lignes ou colonnes, spécialement avec des ensembles de résultats très grands. 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 autrement connu par l'appelant). L'appelant peut alors récupérer les lignes à partir du curseur. Le curseur peut être fermé par l'appelant ou il sera fermé automatiquement à la fin de la transaction.

Le nom du portail utilisé pour un curseur peut être spécifié par le développeur ou peut ê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 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 portail existant et l'affecte à la variable refcursor.

L'exemple suivant montre une façon de fournir un nom de curseur par 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;

-- Il faut être dans une transaction pour utiliser les curseurs.
BEGIN;
SELECT fonction_reference2();

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

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

L'exemple suivant montre une façon de renvoyer plusieurs curseurs à une seule fonction :

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

-- doit être dans une transaction pour utiliser les curseurs.
BEGIN;

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

FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;