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'une fonction a créé, permettant à l'appelant de
lire les lignes. C'est un moyen efficace de renvoyer de grands ensembles de
lignes à partir des fonctions.
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
[ [ NO ] SCROLL ] CURSOR [ (arguments
) ] FORrequête
;
(FOR
peut être remplacé par IS
pour la compatibilité avec
Oracle).
Si SCROLL
est spécifié, le curseur sera capable d'aller
en sens inverse ; si NO SCROLL
est indiqué, les
récupérations en sens inverses seront rejetées ; si rien n'est
indiqué, cela dépend de la requête.
arguments
est une liste de paires de
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 indiquée plus tard lors de
l'ouverture du curseur.
nom
type-de-donnée
Quelques exemples :
DECLARE curs1 refcursor; curs2 CURSOR FOR SELECT * FROM tenk1; curs3 CURSOR (cle integer) FOR SELECT * FROM tenk1 WHERE unique1 = cle;
Ces variables sont toutes trois du type de données refcursor
mais la première peut être utilisée 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
(cle
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 à une requête particulière.
GET STACKED DIAGNOSTICS ... PG_EXCEPTION_CONTEXT
renvoie le même type de pile d'appels, mais en décrivant l'emplacement où
l'erreur a été détectée, plutôt que l'emplacement actuel.
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
dispose de trois formes pour
l'instruction OPEN
, dont deux utilisent des variables curseur non liées
et la dernière une variable curseur liée.
Les variables des curseurs liés peuvent aussi être utilisés sans les ouvrir
explicitement, via l'instruction FOR
décrite dans
Section 42.7.4.
OPEN FOR
requête
OPENvar_curseur_nonlie
[ [ NO ] SCROLL ] FORrequete
;
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 FOR EXECUTE
OPENvar_curseur_nonlie
[ [ NO ] SCROLL ] FOR EXECUTErequete
[ USINGexpression
[, ... ] ];
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 42.10.2),
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
format()
ou USING
. Les options SCROLL
et
NO SCROLL
ont la même signification que pour un
curseur lié.
Exemple :
OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE nom_colonne = $1', ma_table) USING valeur_clef;
Dans cet exemple, le nom de la table est inséré dans la requête
via la fonction format()
. La valeur de la colonne
nom_colonne
utilisée pour la comparaison est
insérée via le paramètre USING
, c'est la raison
pour laquelle elle n'a pas besoin d'être échappée.
OPENvar_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, 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;
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, affecté à 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.
FETCH
FETCH [direction
{ FROM | IN } ]curseur
INTOcible
;
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 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
.
Dans la syntaxe utilisant count
,
la variable count
peut être toute expression
renvoyant un entier (contrairement à la commande FETCH
,
qui n'autorise qu'une constante entière).
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
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.
Exemples :
MOVE curs1; MOVE LAST FROM curs3; MOVE RELATIVE -2 FROM curs4; MOVE FORWARD 2 FROM curs4;
UPDATE/DELETE WHERE CURRENT OF
UPDATEtable
SET ... WHERE CURRENT OFcurseur
; DELETE FROMtable
WHERE CURRENT OFcurseur
;
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.
Un exemple :
UPDATE foo SET valdonnee = mavaleur WHERE CURRENT OF curs1;
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 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
.
Une variable curseur avec limites est initialisée avec la valeur de la chaîne représentant son nom, de façon à ce que le nom du portail soit identique au nom de la variable curseur, sauf si le développeur le surcharge par affectation avant d'ouvrir le curseur. Mais, une variable curseur sans limite aura par défaut la valeur NULL, dont il reçoit 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 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;
C'est une variante de l'instruction FOR
qui permet
l'itération sur les lignes renvoyées par un curseur. La syntaxe est :
[ <<label
>> ] FORvar_record
INvar_curseur_lié
[ ( [nom_argument
:= ]valeur_argument
[, ...] ) ] LOOPinstructions
END LOOP [label
];
La variable curseur doit avoir été liée à une requête lors de sa déclaration
et il ne peut pas être déjà ouvert. L'instruction
FOR
ouvre automatiquement le curseur, et il ferme
le curseur en sortie de la boucle. Une liste des expressions de valeurs
des arguments doit apparaître si et seulement si le curseur a été déclaré
prendre des arguments. Ces valeurs seront substituées dans la requête,
de la même façon que lors d'un OPEN
(voir Section 42.7.2.3).
La variable
variable var_record
est définie automatiquement
avec le type record
et existe seulement dans la boucle (toute
définition existante d'un nom de variable est ignorée dans la boucle).
Chaque ligne renvoyée par le curseur est successivement affectée à la variable
d'enregistrement et le corps de la boucle est exécuté.