Ejemplo de Postgresql DECLARE

DECLARAR – definir un cursor

Sinopsis

DECLARE name [BINARY][ INSENSITIVE ][[NO] SCROLL ]CURSOR[  WITH HOLD ]FOR query

Descripción

DECLARE permite a un usuario crear cursores, que se pueden utilizar para recuperar una pequeña cantidad de filas a la vez de una consulta más grande. Una vez creado el cursor, las filas se obtienen mediante FETCH.

Nota

Esta página describe el uso de cursores en el nivel de comando SQL. Si está intentando utilizar cursores dentro de una función PL / pgSQL, las reglas son diferentes; consulte Sección 42.7.

Parámetros

name

El nombre del cursor que se creará.

BINARY

Hace que el cursor devuelva datos en formato binario en lugar de en formato de texto.

INSENSITIVE

Indica que los datos recuperados del cursor no deben verse afectados por las actualizaciones de las tablas subyacentes al cursor que se producen después de que se crea el cursor. En PostgreSQL, este es el comportamiento predeterminado; así que esto key word no tiene ningún efecto y solo se acepta por compatibilidad con el estándar SQL.

SCROLLNO SCROLL

SCROLL 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, especificando SCROLL podría imponer una penalización de rendimiento en el 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 valor predeterminado es permitir el desplazamiento en algunos casos; esto no es lo mismo que especificar SCROLL. Consulte las notas a continuación para obtener más detalles.

WITH HOLDWITHOUT HOLD

WITH HOLD especifica que el cursor puede seguir utilizándose después de que la transacción que lo creó se confirme correctamente. WITHOUT HOLD especifica que el cursor no se puede utilizar fuera de la transacción que lo creó. Si ninguno WITHOUT HOLD ni WITH HOLD está especificado, WITHOUT HOLD es el predeterminado.

query

Un comando SELECT o VALUES que proporcionará las filas que devolverá el cursor.

los key palabras BINARY, INSENSITIVE, y SCROLL puede aparecer en cualquier orden.

Notas

Los cursores normales devuelven datos en formato de texto, al igual que un SELECT produciría. los BINARY La opción 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 lidiar con formatos de datos binarios dependientes de la plataforma. Por ejemplo, si una consulta devuelve un valor de uno de una columna de números enteros, obtendría un string de 1 con un cursor predeterminado, mientras que con un cursor binario obtendría 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, incluido psql, no están preparadas para manejar cursores binarios y esperan que los datos regresen en formato de texto.

Nota

Cuando la aplicación cliente utiliza el “consulta extendida“ protocolo para emitir un FETCH comando, el mensaje de protocolo Bind especifica si los datos se recuperarán en formato de texto o binario. Esta elección anula la forma en que se define el cursor. El concepto de cursor binario como tal es obsoleto cuando se usa el protocolo de consulta extendido: cualquier cursor puede tratarse como texto o binario.

A no ser que WITH HOLD se especifica, 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 transacciones: el cursor sobreviviría solo hasta la finalización de la declaración. Por lo tanto, PostgreSQL informa de un error si dicho comando se usa fuera de un bloque de transacción. Utilice BEGIN y COMMIT (o ROLLBACK) para definir un bloque de transacción.

Si WITH HOLD se especifica y la transacción que creó el cursor se confirma con éxito, el cursor puede seguir siendo accedido por transacciones posteriores en la misma sesión. (Pero si se cancela la transacción de creación, se quita el cursor). Un cursor creado con WITH HOLD se cierra cuando un explícito CLOSE se emite un comando sobre él, o la sesión finaliza. En la implementación actual, las filas representadas por un cursor retenido se copian en un archivo temporal o área de memoria para que permanezcan disponibles para transacciones posteriores.

WITH HOLD no se puede especificar cuando la consulta incluye FOR UPDATE o FOR SHARE.

los SCROLL La opción debe especificarse al definir un cursor que se utilizará para buscar hacia atrás. Esto es requerido por el estándar SQL. Sin embargo, por compatibilidad con versiones anteriores, PostgreSQL permitirá recuperaciones hacia atrás sin SCROLL, si el plan de consulta del cursor es lo suficientemente simple como para que no se necesite una sobrecarga adicional para respaldarlo. Sin embargo, se recomienda a los desarrolladores de aplicaciones que no confíen en el uso de recuperaciones hacia atrás de un cursor que no se haya creado con SCROLL. Si NO SCROLL se especifica, las recuperaciones hacia atrás no se permiten en ningún caso.

Las recuperaciones hacia atrás tampoco se permiten cuando la consulta incluye FOR UPDATE o FOR SHARE; por lo tanto SCROLL puede que no se especifique en este caso.

Precaución

Desplazable y WITH HOLD los cursores pueden dar resultados inesperados si invocan funciones volátiles (ver Sección 37.7). Cuando se recupera una fila previamente obtenida, las funciones pueden volver a ejecutarse, lo que quizás lleve a resultados diferentes a los de la primera vez. Una solución para tales casos es declarar el cursor WITH HOLD y confirme la transacción antes de leer sus filas. Esto obligará 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 obtienen 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; por lo tanto, estas opciones proporcionan el equivalente de lo que el estándar SQL llama un “cursor sensible“. (Especificando INSENSITIVE Juntos con FOR UPDATE o FOR SHARE es un error.)

Precaución

Generalmente se recomienda usar FOR UPDATE si el cursor está destinado a utilizarse con UPDATE ... WHERE CURRENT OF o DELETE ... WHERE CURRENT OF. Utilizando 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, una subsecuente WHERE CURRENT OF El comando no tendrá ningún efecto si se cambió la fila desde que se creó el cursor.

Otra razón para usar FOR UPDATE es que sin ella, una subsecuente WHERE CURRENT OF puede 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 utilizar agrupaciones o ORDER BY). Los cursores que no son simplemente actualizables pueden funcionar, o no, dependiendo de los detalles de la elección del plan; por lo que en el peor de los casos, una aplicación podría funcionar en pruebas y luego fallar en producción. Si FOR UPDATE se especifica, se garantiza que el cursor es actualizable.

La principal razón para no usar FOR UPDATE con WHERE CURRENT OF es si necesita que el cursor sea desplazable o que sea insensible a las actualizaciones posteriores (es decir, que continúe mostrando los datos antiguos). Si esto es un requisito, preste mucha atención a las advertencias que se muestran arriba.

El estándar SQL solo prevé cursores en SQL incorporado. El servidor PostgreSQL no implementa un OPEN declaración para cursores; un cursor se considera abierto cuando se declara. Sin embargo, ECPG, el preprocesador de SQL incorporado para PostgreSQL, admite las convenciones de cursor SQL estándar, incluidas las que involucran DECLARE y OPEN declaraciones.

Puede ver todos los cursores disponibles consultando el pg_cursors vista del sistema.

Ejemplos de

Para declarar un cursor:

DECLARE liahona CURSORFORSELECT*FROM films;

Consulte FETCH para obtener más ejemplos del uso del cursor.

Compatibilidad

El estándar SQL dice que depende de la implementación si los cursores son sensibles a las actualizaciones simultáneas de los datos subyacentes de forma predeterminada. En PostgreSQL, los cursores son insensibles de forma predeterminada y se pueden hacer sensibles especificando FOR UPDATE. Otros productos pueden funcionar de manera diferente.

El estándar SQL permite cursores solo en SQL incorporado y en módulos. PostgreSQL permite que los cursores se utilicen de forma interactiva.

Los cursores binarios son una extensión de PostgreSQL.

Ver también

CERRAR, BUSCAR, MOVER