DECLARE — define un cursor
DECLAREnombre[ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FORconsulta
DECLARE permite a un usuario crear cursores, los cuales
se pueden usar para recuperar
un número pequeño de filas a la vez a partir de una consulta más grande.
Después de crear el cursor, las filas se recuperan de él utilizando
FETCH.
Esta página describe el uso de cursores al nivel de comando SQL. Si estás intentando usar cursores dentro de una función PL/pgSQL, las reglas son diferentes — consulta la Section 41.7.
nombreEl nombre del cursor que se va a crear. Este debe ser diferente de cualquier otro nombre de cursor activo en la sesión.
BINARYHace que el cursor devuelva los datos en formato binario en lugar de texto.
ASENSITIVEINSENSITIVE
La sensibilidad del cursor determina si los cambios en los datos subyacentes del
cursor, realizados en la misma transacción después de que el cursor haya sido
declarado, son visibles en el cursor. INSENSITIVE
significa que no son visibles, mientras que ASENSITIVE significa que el
comportamiento depende de la implementación. Un tercer comportamiento,
SENSITIVE, que significa que dichos cambios sí son visibles en
el cursor, no está disponible en PostgreSQL.
En PostgreSQL, todos los cursores son insensibles (insensitive);
por lo tanto, estas palabras clave no tienen efecto y solo se aceptan por
compatibilidad con el estándar SQL.
Especificar INSENSITIVE junto con FOR
UPDATE o FOR SHARE es un error.
SCROLLNO SCROLLSCROLL especifica que el cursor se puede utilizar
para recuperar filas de forma no secuencial (por ejemplo,
hacia atrás). Dependiendo de la complejidad del plan de
ejecución de la consulta, especificar SCROLL podría penalizar
el rendimiento del tiempo de ejecución de la consulta.
NO SCROLL especifica que el cursor no se puede
utilizar para recuperar filas de forma no secuencial. El comportamiento predeterminado es
permitir el desplazamiento (scrolling) en algunos casos; esto no es lo mismo que especificar
SCROLL. Consulta la sección Notas
a continuación para obtener más detalles.
WITH HOLDWITHOUT HOLDWITH HOLD especifica que el cursor puede
seguir utilizándose después de que la transacción que lo creó
se confirme (commit) con éxito. WITHOUT HOLD especifica
qued el cursor no se puede utilizar fuera de la transacción que
lo creó. Si no se especifica WITHOUT HOLD ni
WITH HOLD, el valor predeterminado es WITHOUT
HOLD.
consulta
Un comando SELECT o
VALUES
que proporcionará las filas a ser devueltas por el cursor.
Las palabras clave ASENSITIVE, BINARY,
INSENSITIVE y SCROLL pueden
aparecer en cualquier orden.
Los cursores normales devuelven datos en formato de texto, de la misma manera que lo haría un
SELECT. La opción BINARY
especifica que el cursor debe devolver datos en formato binario.
Esto reduce el esfuerzo de conversión tanto para el servidor como para el cliente,
a costa de un mayor esfuerzo del programador para manejar los formatos de datos
binarios que dependen de la plataforma.
Como ejemplo, si una consulta devuelve un valor de uno de una columna de enteros,
obtendrás una cadena de texto 1 con un cursor predeterminado,
mientras que con un cursor binario obtendrás
un campo de 4 bytes que contiene la representación interna del valor
(en orden de bytes big-endian).
Los cursores binarios deben usarse con cuidado. Muchas aplicaciones, incluyendo psql, no están preparadas para manejar cursores binarios y esperan que los datos se devuelvan en formato de texto.
Cuando la aplicación cliente utiliza el protocolo de “consulta extendida”
para emitir un comando FETCH, el mensaje de protocolo Bind
especifica si los datos se deben recuperar en formato de texto o binario.
Esta elección anula la forma en que se define el cursor. Por lo tanto, el concepto
de cursor binario como tal está obsoleto cuando se utiliza el protocolo de consulta
extendida — cualquier cursor puede tratarse como de texto o binario.
A menos que se especifique WITH HOLD, el cursor
creado por este comando solo se puede usar dentro de la transacción
actual. Por lo tanto, DECLARE sin WITH
HOLD es inútil fuera de un bloque de transacción: el cursor
sobreviviría solo hasta la finalización de la sentencia. Por este motivo,
PostgreSQL reporta un error si se utiliza dicho
comando fuera de un bloque de transacción.
Usa
BEGIN y
COMMIT
(o ROLLBACK)
to define a transaction block.
Si se especifica WITH HOLD y la transacción
que creó el cursor se confirma con éxito, las transacciones posteriores
en la misma sesión podrán seguir accediendo al cursor.
(Pero si la transacción creadora se aborta, el cursor
se elimina). Un cursor creado con WITH HOLD
se cierra cuando se emite un comando CLOSE explícito
sobre él, o cuando finaliza la sesión. En la implementación actual,
las filas representadas por un cursor persistente (held cursor) se copian en un
archivo temporal o en un área de memoria para que permanezcan disponibles para las
transacciones posteriores.
No se puede especificar WITH HOLD cuando la consulta
incluye FOR UPDATE o FOR SHARE.
La opción SCROLL debe especificarse al definir un
cursor que se utilizará para recuperar filas hacia atrás. Esto es un requisito
del estándar SQL. Sin embargo, para compatibilidad con versiones
anteriores, PostgreSQL permitirá
búsquedas hacia atrás sin SCROLL, si el plan de la consulta
del cursor es lo suficientemente simple como para no necesitar un procesamiento
adicional para soportarlo. No obstante, se aconseja a los desarrolladores de
aplicaciones no confiar en el uso de búsquedas hacia atrás desde un cursor
que no haya sido creado con SCROLL. Si se especifica
NO SCROLL, las búsquedas hacia atrás se desactivan en cualquier caso.
Las búsquedas hacia atrás también se desactivan cuando la consulta
incluye FOR UPDATE o FOR SHARE; por lo tanto,
no se puede especificar SCROLL en este caso.
Los cursores desplazables (scrollable) pueden dar resultados inesperados
si invocan funciones volátiles (consulta la Section 36.7). Cuando se vuelve a recuperar una fila recuperada
previamente, las funciones podrían volver a ejecutarse, lo que tal vez daría
resultados diferentes a los de la primera vez. Lo mejor es
especificar NO SCROLL para consultas que involucren funciones
volátiles. Si esto no es práctico, una solución alternativa
es declarar el cursor como SCROLL WITH HOLD y confirmar la
transacción antes de leer cualquier fila de él. Esto forzará a que toda la
salida del cursor se materialice en un almacenamiento temporal, de modo que las
funciones volátiles se ejecuten exactamente una vez para cada fila.
Si la consulta del cursor incluye FOR UPDATE o FOR
SHARE, las filas devueltas se bloquean en el momento en que se recuperan
por primera vez, de la misma manera que para un comando
SELECT normal con
estas opciones.
Además, las filas devueltas serán las versiones más actualizadas.
Generalmente se recomienda usar FOR UPDATE si se tiene la
intención de usar el cursor con UPDATE ... WHERE CURRENT OF o
DELETE ... WHERE CURRENT OF. El uso de FOR UPDATE
evita que otras sesiones cambien las filas entre el momento en que se recuperan
y el momento en que se actualizan. Sin FOR UPDATE, un comando
WHERE CURRENT OF posterior no tendrá efecto si la fila cambió
desde que se creó el cursor.
Otra razón para usar FOR UPDATE es que, sin ella, un
WHERE CURRENT OF posterior podría fallar si la consulta del
cursor no cumple con las reglas del estándar SQL para ser “simplemente
actualizable” (en particular, el cursor debe hacer referencia a una sola tabla
y no usar agrupaciones ni ORDER BY). Los cursores
que no son simplemente actualizables podrían funcionar, o no, dependiendo de los
detalles de la elección del plan; por lo tanto, en el peor de los casos, una aplicación
podría funcionar en pruebas y fallar en producción. Si se especifica
FOR UPDATE, se garantiza que el cursor es actualizable.
La razón principal para no usar FOR UPDATE con WHERE
CURRENT OF es si necesitas que el cursor sea desplazable o que esté
aislado de actualizaciones concurrentes (es decir, que continúe mostrando los datos
antiguos). Si este es un requisito, presta mucha atención a las advertencias mostradas
anteriormente.
El estándar SQL solo prevé el uso de cursores en SQL
embebido. El servidor de PostgreSQL
no implementa una sentencia OPEN para los cursores;
se considera que un cursor está abierto cuando se declara.
Sin embargo, ECPG, el preprocesador de SQL
embebido para PostgreSQL, soporta las
convenciones estándar de cursores de SQL, incluidas las que involucran las
sentencias DECLARE y OPEN.
La estructura de datos del servidor que subyace a un cursor abierto se denomina
portal. Los nombres de los portales se exponen en el
protocolo cliente: un cliente puede recuperar filas directamente de un portal abierto,
si conoce el nombre del portal. Al crear un cursor con
DECLARE, el nombre del portal es el mismo que el
nombre del cursor.
Puedes ver todos los cursores disponibles consultando la vista de sistema
pg_cursors.
Para declarar un cursor:
DECLARE liahona CURSOR FOR SELECT * FROM films;
Consulta la FETCH para obtener más ejemplos de uso de cursores.
El estándar SQL permite cursores solo en SQL embebido y en módulos. PostgreSQL permite utilizar cursores de forma interactiva.
Según el estándar SQL, los cambios realizados en cursores insensibles por
las sentencias UPDATE ... WHERE CURRENT OF y DELETE
... WHERE CURRENT OF son visibles en ese mismo
cursor. PostgreSQL trata estas sentencias como
todas las demás sentencias de modificación de datos, en el sentido de que no son visibles en
cursores insensibles.
Los cursores binarios son una extensión de PostgreSQL.