DECLARE — Définir un curseur
DECLAREnom[ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FORrequête
DECLARE permet à un utilisateur de créer des curseurs.
Ils peuvent être utilisés pour récupérer un petit nombre de lignes à la
fois à partir d'une requête plus importante.
Après la création du curseur, les lignes sont récupérées en utilisant
FETCH.
Cette page décrit l'utilisation des curseurs au niveau de la commande SQL. Si vous voulez utiliser des curseurs dans une fonction PL/pgSQL, les règles sont différentes -- voir Section 41.7.
nomLe nom du curseur à créer. Il doit être différent de tout autre nom de curseur actif dans la session.
BINARYLe curseur retourne les données au format binaire.
ASENSITIVEINSENSITIVE
La sensibilité du curseur détermine si les changements de données sous
le curseur, réalisés pendant la même transaction, une fois que le
curseur a été déclaré, sont visibles dans le curseur.
INSENSITIVE signifie qu'elles ne sont pas visibles,
ASENSITIVE signifie que le comportement est
dépendant de l'implémentation. Un troisième comportement,
SENSITIVE, signifiant que les changements sont
visibles dans le curseur, n'est pas disponible avec
PostgreSQL. Dans
PostgreSQL, tous les curseurs sont
insensibles ; donc ces mots clés n'ont pas d'effet et sont
seulement acceptés pour la compatibilité avec le standard SQL.
Indiquer INSENSITIVE avec FOR
UPDATE ou FOR SHARE renvoie une erreur.
SCROLLNO SCROLL
SCROLL indique une utilisation possible du curseur
pour récupérer des lignes de façon non séquentielle (c'est-à-dire en remontant
la liste). En fonction de la complexité du plan d'exécution de la requête,
SCROLL peut induire des pertes de
performance sur le temps d'exécution de la requête.
NO SCROLL indique que le curseur ne peut pas être utilisé pour
récupérer des lignes de façon non séquentielle. La valeur par
défaut autorise la non-séquentialité du curseur dans certains cas ; ce n'est pas la
même chose que de spécifier SCROLL.
Voir Notes pour les
détails.
WITH HOLDWITHOUT HOLD
WITH HOLD (NDT : persistant) indique une utilisation possible
du curseur après la validation de la transaction qui l'a créé.
WITHOUT HOLD (NDT : volatil) interdit l'utilisation du curseur
en dehors de la transaction qui l'a créé.
WITHOUT HOLD est la valeur par défaut.
requête
Une commande SELECT
ou VALUES qui
fournira les lignes à renvoyer par le curseur.
Les mots clés ASENSITIVE, BINARY,
INSENSITIVE et SCROLL peuvent
apparaître dans n'importe quel ordre.
Les curseurs normaux renvoient les données au format texte, le même que
produirait un SELECT. L'option BINARY
spécifie que le curseur doit renvoyer les données au format binaire. Ceci
réduit les efforts de conversion pour le serveur et le client, au coût d'un
effort particulier de développement pour la gestion des formats de données
binaires dépendants des plateformes. Comme exemple, si une requête
renvoie une valeur de un dans une colonne de type integer, vous obtiendrez
une chaîne 1 avec un curseur par défaut. Avec un curseur
binaire, vous obtiendrez un champ sur quatre octet contenant la
représentation interne de la valeur (dans l'ordre big-endian).
Les curseurs binaires doivent être utilisés en faisant très attention. Beaucoup d'applications, incluant psql, ne sont pas préparées à gérer des curseurs binaires et s'attendent à ce que les données reviennent dans le format texte.
Quand l'application cliente utilise le protocole des « requêtes
étendues » pour exécuter la commande FETCH, le
message Bind du protocole spécifie si les données sont à récupérer au
format texte ou binaire. Ce choix surcharge la façon dont le curseur est
défini. Le concept de curseur binaire est donc obsolète lors de l'utilisation
du protocole des requêtes étendues -- tout curseur peut être traité soit
en texte soit en binaire.
Si la clause WITH HOLD n'est pas précisée, le curseur créé par
cette commande ne peut être utilisé qu'à l'intérieur d'une transaction.
Ainsi, DECLARE sans WITH HOLD est inutile à
l'extérieur d'un bloc de transaction : le curseur survivrait seulement
jusqu'à la fin de l'instruction. PostgreSQL
rapporte donc une erreur si cette commande
est utilisée en dehors d'un bloc de transactions.
On utilise BEGIN
et COMMIT
(ou ROLLBACK) pour
définir un bloc de transaction.
Si la clause WITH HOLD est précisée, et que la
transaction qui a créé le curseur est validée, ce dernier reste
accessible par les transactions ultérieures de la session.
Au contraire, si la transaction initiale est annulée, le curseur est supprimé.
Un curseur créé avec la clause WITH HOLD est fermé
soit par un appel explicite à la commande CLOSE, soit
par la fin de la session.
Dans l'implantation actuelle, les lignes représentées par un
curseur persistant (WITH HOLD) sont copiées dans un fichier
temporaire ou en mémoire afin de garantir leur disponibilité pour les transactions
suivantes.
WITH HOLD n'est pas utilisable quand la requête contient
déjà FOR UPDATE ou FOR SHARE.
L'option SCROLL est nécessaire à la définition de
curseurs utilisés en récupération remontante (retour dans la
liste des résultats, backward fetch), comme précisé par le
standard SQL. Néanmoins, pour des raisons de compatibilité
avec les versions antérieures, PostgreSQL
autorise les récupérations remontantes sans que l'option
SCROLL ne soit précisé, sous réserve que le plan
d'exécution du curseur soit suffisamment simple pour être géré
sans surcharge. Toutefois, il est fortement conseillé aux développeurs
d'application ne pas utiliser les récupérations remontantes avec
des curseurs qui n'ont pas été créés avec l'option SCROLL.
Si NO SCROLL est spécifié, les récupérations remontantes
sont toujours dévalidées.
Les parcours inverses sont aussi interdits lorsque la requête inclut les
clauses FOR UPDATE et FOR SHARE ;
donc SCROLL peut ne pas être indiqué dans ce cas.
Les curseurs scrollables
pourraient donner des résultats inattendues s'ils font appel à des
fonctions volatiles (voir Section 36.7). Quand
une ligne précédemment récupérée est de nouveau récupérée, la fonction
pourrait être ré-exécutée, amenant peut-être des résultats différentes
de la première exécution. Il est préférable d'indiquer
NO SCROLL pour une requête impliquant des fonctions
volatiles. Si ce n'est pas pratique, un contournement est de déclarer le curseur
SCROLL WITH HOLD et de valider la transaction avant de lire
toute ligne de ce curseur. Cela forcera la sortie entière du cuseur à
être matérialisée dans un stockage temporaire, pour que les fonctions
volatiles soient exécutées exactement une fois pour chaque ligne.
Si la requête du curseur inclut les clauses FOR UPDATE
ou FOR SHARE, alors les lignes renvoyées sont verrouillées
au moment où elles sont récupérées, de la même façon qu'une commande
SELECT standard avec ces
options. De plus, les lignes renvoyées seront les versions les plus à jour.
Il est généralement recommandé d'utiliser FOR UPDATE si
le curseur doit être utilisé avec UPDATE ... WHERE CURRENT
OF ou DELETE ... WHERE CURRENT OF. Utiliser
FOR UPDATE empêche les autres sessions de modifier les
lignes entre le moment où elles sont récupérées et celui où elles sont
modifiées. Sans FOR UPDATE, une commande WHERE
CURRENT OF suivante n'aura pas d'effet si la ligne a été modifiée
depuis la création du curseur.
Une autre raison d'utiliser FOR UPDATE est que, sans ce
dernier, un appel suivant à WHERE CURRENT OF pourrait
échouer si la requête curseur ne répond pas aux règles du standard SQL
d'être « mise à jour simplement » (en particulier, le curseur
doit référencer une seule table et ne pas utiliser de regroupement ou
de tri comme ORDER BY). Les curseurs qui ne peuvent pas
être mis à jour pourraient fonctionner, ou pas, suivant les détails du plan
choisi ; dans le pire des cas, une application pourrait fonctionner
lors des tests puis échouer en production. Si FOR
UPDATE est indiqué, le curseur est garanti être modifiable.
La principale raison de ne pas utiliser FOR UPDATE avec
WHERE CURRENT OF est si vous avez besoin que le curseur
soit déplaçable ou qu'il soit isolé des mises à jour suivantes
(c'est-à-dire qu'il continue à afficher les anciennes données). Si c'est
un prérequis, faites très attention aux problèmes expliqués ci-dessus.
Le standard SQL ne mentionne les curseurs que pour le
SQL embarqué. PostgreSQL
n'implante pas l'instruction
OPEN pour les curseurs ; un curseur est considéré
ouvert à sa déclaration. Néanmoins, ECPG, le
préprocesseur de SQL embarqué pour PostgreSQL,
supporte les conventions du standard SQL relatives aux curseurs, dont celles
utilisant les instructions DECLARE et
OPEN.
La structure de données du serveur sous un curseur ouvert est appelé un
portail. Les noms de portail sont exposés dans le
protocole client : un client peut récupérer les lignes directement
d'un portail ouvert s'il connaît le nom du portail. Lors de la création
d'un curseur avec l'instruction DECLARE, le nom du
portail est le même que le nom du curseur.
Vous pouvez voir tous les curseurs disponibles en exécutant une requête sur
la vue système pg_cursors.
Déclarer un curseur :
DECLARE liahona CURSOR FOR SELECT * FROM films;
Voir FETCH pour plus d'exemples sur l'utilisation des curseurs.
Le standard SQL n'autorise les curseurs que dans le SQL embarqué et dans les modules. PostgreSQL permet une utilisation interactive des curseurs.
Le standard SQL autorise les curseurs à mettre à jour les données d'une table. Tous les curseurs PostgreSQL sont en lecture seule.
D'après le standard SQL, les modifications faites à des curseurs
insensibles par les instructions UPDATE ... WHERE CURRENT
OF et DELETE
... WHERE CURRENT OF sont visibles dans ce même curseur.
PostgreSQL traite ces instructions comme toutes
les autres instructions de changement de données, dans le sens où elles ne
sont pas visibles dans les curseurs insensibles.
Les curseurs binaires sont une extension de PostgreSQL.