PL/pgSQL es un lenguaje procedural cargable para el sistema de bases de datos Postgres.
Este paquete fue escrito originalmente por Jan Wieck.
Los objetivos de diseño de PL/pgSQL fueron crear un lenguaje procedural cargable que
pueda usarse para crear funciones y procedimientos disparados por eventos,
añada estructuras de control al lenguaje SQL,
pueda realizar cálculos complejos,
herede todos los tipos definidos por el usuario, las funciones y los operadores,
pueda ser definido para ser fiable para el servidor,
sea fácil de usar,
El gestor de llamadas PL/pgSQL analiza el texto de las funciones y produce un árbol de instrucciones binarias interno la primera vez que la función es invocada por una aplicación. El bytecode producido es identificado por el manejador de llamadas mediante el ID de la función. Esto asegura que el cambio de una función por parte de una secuencia DROP/CREATE tendrá efecto sin tener que establecer una nueva conexión con la base de datos.
Para todas y las expresiones y sentencias SQL usadas en la función, el interprete de bytecode de PL/pgSQL crea un plan de ejecución preparado usando los gestores de SPI, funciones SPI_prepare() y SPI_saveplan(). Esto se hace la primera vez que las sentencias individuales se procesan en la función PL/pgSQL. Así, una función con código condicional que contenga varias sentencias que puedan ser ejecutadas, solo preparará y almacenará las opciones que realmente se usarán durante el ámbito de la conexión con la base de datos.
Excepto en el caso de funciones de conversión de entrada/salida y de cálculo para tipos definidos, cualquier cosa que pueda definirse en funciones de lenguaje C puede ser hecho con PL/pgSQL. Es posible crear funciones complejas de calculo y después usarlas para definir operadores o usarlas en índices funcionales.
El lenguaje PL/pgSQL no es sensible a las mayúsculas. Todas las palabras clave e identificadores pueden usarse en cualquier mexcla de mayúsculas y minúsculas.
PL/pgSQL es un lenguaje orientado a bloques. Un bloque se define como
[<<label>>]
[DECLARE
declarations]
BEGIN
statements
END; |
Es importante no confundir el significado de BEGIN/END en la agrupación de sentencias de OL/pgSQl y las ordenes de la base de datos para control de transacciones. Las funciones y procedimientos disparadores no pueden iniciar o realizar transacciones y Postgres no soporta transacciones anidadas.
Hay dos tipos de comentarios en PL/pgSQl. Un par de guiones '--' comienza un comentario que se extiende hasta el fin de la linea. Los caracteres '/*' comienzan un bloque de comentarios que se extiende hasta que se encuentre un '*/'. Los bloques de comentarios no pueden anidarse pero un par de guiones pueden encerrarse en un bloque de comentario, o ocultar los limitadores de estos bloques.
Todas las variables, filas y columnas que se usen en un bloque o subloque ha de ser declarado en la sección de declaraciones del bloque, excepto las variables de control de bucle en un bucle FOR que se itere en un rango de enteros. Los parámetros dados a una función PL/pgSQL se declaran automáticamente con los identificadores usuales, $n. Las declaraciones tienen la siguiente sintaxis:
Esto declara una variable de un tipo base especificado. Si la variable es declarada como CONSTANT, su valor no podrá ser cambiado. Si se especifica NOT NULL, la asignación de un NULL producirá un error en timepo de ejecución. Dado que el valor por defecto de todas las variables es el valor NULL de SQL, todas las variables declaradas como NOT NULL han de tener un valor por defecto.
El valor por defecto es evaluado cada vez que se invoca la función. Así que asignar 'now' a una variable de tipo datetime hace que la variable tome el momento de la llamada a la función, no el momento en que la función fue compilada a bytecode.
Esto declara una fila con la estructura de la clase indicada. La clase ha de ser una tabla existente, o la vista de una base de datos. Se accede a los campos de la fila mediante la notación de punto. Los parámetros de una función pueden ser de tipos compuestos (filas de una tabla completas). En ese caso, el correspondiente identificador $n será un tipo de fila, pero ha de ser referido usando la orden ALIAS que se describe más adelante. Solo los atributos de usuario de una fila de tabla son accesibles en la fila, no se puede acceder a Oid o a los otros atributos de sistema (dado que la fila puede ser de una vista, y las filas de una vista no tienen atributos de sistema útiles).
Los campos de un tipo de fila heredan los tipos de datos, tamaños y precisiones de las tablas.
Los registros son similares a los tipos de fila, pero no tienen una estructura predefinida. Se emplean en selecciones y bucles FOR, para mantener una fila de la actual base de datos en una operación SELECT. El mismo registro puede ser usado en diferentes selecciones. El acceso a un campo de registro cuando no hay una fila seleccionada resultará en un error de ejecución.
Las filas NEW y OLD en un disparador se pasan a los procedimientos como registros. Esto es necesario porque en Postgres un mismo procedimiento desencadenado puede tener sucesos disparadores en diferentes tablas.
Para una mejor legibilidad del código, es posible definir un alias para un parámetro posicional de una función.
Estos alias son necesarios cuando un tipo compuesto se pasa como argumento a una función. La notación punto $1.salary como en funciones SQL no se permiten en PL/pgSQL
Esto cambia el nombre de una variable, registro o fila. Esto es útil si NEW o OLD ha de ser referenciado por parte de otro nombre dentro de un procedimiento desencadenado.
Los tipos de una variable pueden ser cualquiera de los tipos básicos existentes en la base de datos. type en la sección de declaraciones se define como:
Postgres-basetype
variable%TYPE
class.field%TYPE
variable es el nombre de una variable, previamente declarada en la misma función, que es visible en este momento.
class es el nombre de una tabla existente o vista, donde field es el nombre de un atributo.
El uso de class.field%TYPE hace que PL/pgSQl busque las definiciones de atributos en la primera llamada a la función, durante toda la vida de la aplicación final. Supongamos que tenemos una tabla con un atributo char(20) y algunas funciones PL/pgSQL, que procesan el contenido por medio de variables locales. Ahora, alguien decide que char(20) no es suficiente, cierra la tabla, y la recrea con el atributo en cuestión definido como char(40), tras lo que restaura los datos. Pero se ha olvidado de las funciones. Los cálculos internos de éstas truncarán los valores a 20 caracteres. Pero si hubieran sido definidos usando las declaraciones class.field%TYPE automáticamente se adaptarán al cambio de tamaño, o a si el nuevo esquema de la tabla define el atributo como de tipo texto.
Todas las expresiones en las sentencias PL/pgSQL son procesadas usando backends de ejecución. Las expresiones que puedan contener constantes pueden de hecho requerir evaluación en tiempo de ejecución (por ejemplo, 'now' para el tipo 'datatime'), dado que es imposible para el analizador de PL/pgSQL identificar los valores constantes distintos de la palabra clave NULL. Todas las expresiones se evalúan internamente ejecutando una consulta
SELECT expression
|
La comprobación de tipos hecha por el analizador principal de Postgres tiene algunos efectos secundarios en la interpretación de los valores constantes. En detalle, hay una diferencia entre lo que hacen estas dos funciones
CREATE FUNCTION logfunc1 (text) RETURNS datetime AS '
DECLARE
logtxt ALIAS FOR $1;
BEGIN
INSERT INTO logtable VALUES (logtxt, ''now'');
RETURN ''now'';
END;
' LANGUAGE 'plpgsql';
|
CREATE FUNCTION logfunc2 (text) RETURNS datetime AS '
DECLARE
logtxt ALIAS FOR $1;
curtime datetime;
BEGIN
curtime := ''now'';
INSERT INTO logtable VALUES (logtxt, curtime);
RETURN curtime;
END;
' LANGUAGE 'plpgsql';
|
En el caso de logfunc2(), el analizador principal de Postgres no sabe cual es el tipo de 'now', por lo que devuelve un tipo de texto, que contiene la cadena 'now'. Durante la asignación a la variable local 'curtime', el interprete PL/pgSQL asigna a esta cadena el tipo fecha, llamando a las funciones text_out() y datetime_in() para realizar la conversión.
esta comprobación de tipos realizada por el analizador principal de Postgres fue implementado antes de que PL/pgSQL estuviera totalmente terminado. Es una diferencia entre 6.3 y 6.4, y afecta a todas las funciones que usan la planificación realizada por el gestor SPI. El uso de variables locales en la manera descrita anteriormente es actualmente la única forma de que PL/pgSQL interprete esos valores correctamente.
Si los campos del registro son usados en expresiones o sentencias, los tipos de datos de campos no deben cambiarse entre llamadas de una misma expresión. Tenga esto en cuenta cuando escriba procedimientos disparadores que gestionen eventos en más de una tabla.
Cualquier cosa no comprendida por el analizador PL/pgSQL tal como se ha especificado será enviado al gestor de bases de datos, para su ejecución. La consulta resultante no devolverá ningún dato.
Una asignación de un valor a una variable o campo de fila o de registro se escribe:
identifier := expression;
|
Una asignación de una selección completa en un registro o fila puede hacerse del siguiente modo:
SELECT expressions INTO target FROM ...;
|
Si una fila o una lista de variables se usa como objetivo, los valores seleccionados han de coincidir exactamente con la estructura de los objetivos o se producirá un error de ejecución. La palabra clave FROM puede preceder a cualquier calificador válido, agrupación, ordenación, etc. que pueda pasarse a una sentencia SELECT.
Existe una variable especial llamada FOUND de tipo booleano, que puede usarse inmediatamente después de SELECT INTO para comprobar si una asignación ha tenido éxito.
SELECT * INTO myrec FROM EMP WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION ''employee % not found'', myname;
END IF;
|
Todas las funciones definidas en una base de datos Postgres devuelven un valor. Por lo tanto, la forma normal de llamar a una función es ejecutar una consulta SELECT o realizar una asignación (que de lugar a un SELECT interno de PL/pgSQL). Pero hay casos en que no interesa saber los resultados de las funciones.
PERFORM query
|
RETURN expression
|
Las expresiones resultantes serán amoldadas automáticamente en los tipos devueltos por la función, tal como se ha descrito en el caso de las asignaciones.
Como se ha indicado en los ejemplos anteriores, hay una sentencia RAISE que puede enviar mensajes al sistema de registro de Postgres. ###################### ATENCION WARNING ACHTUNG ##################### ¡Aquí puede haber una errata! Comparad con el original
RAISE level
for'' [,
identifier [...]];
|
IF expression THEN
statements
[ELSE
statements]
END IF;
|
Hay varios tipos de bucles.
[<<label>>]
LOOP
statements
END LOOP;
|
[<<label>>]
WHILE expression LOOP
statements
END LOOP;
|
[<<label>>]
FOR name IN [ REVERSE ]
express .. expression LOOP
statements
END LOOP;
|
[<<label>>]
FOR record | row IN select_clause LOOP
statements
END LOOP;
|
EXIT [ label ] [ WHEN expression ];
|
PL/pgSQL puede ser usado para definir procedimientos desencadenados por eventos. Estos se crean con la orden CREATE FUNCTION, igual que una función, pero sin argumentos, y devuelven un tipo OPAQUE.
Hay algunos detalles específicos de Postgres cuando se usan funciones como procedimientos desencadenados.
En primer lugar, disponen de algunas variables especiales que se crean automáticamente en los bloques de mayor nivel de la sección de declaración. Son:
Tipo de dato RECORD; es una variable que mantienen la fila de la nueva base de datos en las operaciones INSERT/UPDATE, en los desencadenados ROW.
Tipo de dato RECORD; es una variable que mantiene la fila de la base de datos vieja en operaciones UPDATE/DELETE, en los desencadenados ROW.
Nombre de tipo de dato; es una variable que contiene el nombre del procedimiento desencadenado que se ha activado.
Tipo de dato texto; es una cadena de caracteres del tipo 'BEFORE' o 'AFTER', dependiendo de la definición del procedimiento desencadenado.
Tipo de dato texto; una cadena de 'ROW' o 'STATEMENT', dependiendo de la definición del procedimiento desencadenado.
Tipo de dato texto; una cadena de 'INSERT', 'UPDATE' o 'DELETE', que nos dice la operación para la que se ha disparado el procedimiento desencadenado.
Tipo de dato oid; el ID del objeto de la tabla que ha provocado la invocación del procedimiento desencadenado.
Tipo de dato nombre; el nombre de la tabla que ha provocado la activación del procedimiento desencadenado.
Tipo de dato entero; el numero de argumentos dado al procedimiento desencadenado en la sentencia CREATE TRIGGER.
Tipo de dato matriz de texto; los argumentos de la sentencia CREATE TRIGGER. El índice comienza por cero, y puede ser dado en forma de expresión. Índices no validos dan lugar a un valor NULL.
En segundo lugar, han de devolver o NULL o una fila o registro que contenga exactamente la estructura de la tabla que ha provocado la activación del procedimiento desencadenado. Los procedimientos desencadenados activados por AFTER deben devolver siempre un valor NULL, sin producir ningún efecto. Los procedimientos desencadenados activados por BEFORE indican al gestor de procedimientos desencadenados que no realice la operación sobre la fila actual cuando se devuelva NULL. En cualquier otro caso, la fila o registro devuelta sustituye a la fila insertada o actualizada. Es posible reemplazar valores individuales directamente en una sentencia NEW y devolverlos, o construir una nueva fila o registro y devolverla.
Postgres no dispone de un modelo de manejo de excepciones muy elaborado. Cuando el analizador, el optimizador o el ejecutor deciden que una sentencia no puede ser procesada, la transacción completa es abortada y el sistema vuelve al lazo principal para procesar la siguiente consulta de la aplicación cliente.
Es posible introducirse en el mecanismo de errores para detectar cuando sucede esto. Pero lo que no es posible es saber qué ha causado en realidad el aborto (un error de conversión de entrada/salida, un error de punto flotante, un error de análisis). Y es posible que la base de datos haya quedado en un estado inconsistente, por lo que volver a un nivel de ejecución superior o continuar ejecutando comandos puede corromper toda la base de datos. E incluso aunque se pudiera enviar la información a la aplicación cliente, la transacción ya se abría abortado, por lo que carecería de sentido el intentar reanudar la operación.
Por todo esto, lo único que hace PL/pgSQL cuando se produce un aborto de ejecución durante la ejecución de una función o procedimiento disparador es enviar mensajes de depuración al nivel DEBUG, indicando en qué función y donde (numero de línea y tipo de sentencia) ha sucedido el error.
Se incluyen unas pocas funciones para demostrar lo fácil que es escribir funciones en PL/pgSQL. Para ejemplos más complejos, el programador debería consultar el test de regresión de PL/pgSQL.
Un detalle doloroso a la hora de escribir funciones en PL/pgSQL es el manejo de la comilla simple. El texto de las funciones en CREATE FUNCTION ha de ser una cadena de texto. Las comillas simples en el interior de una cadena literal deben de duplicarse o anteponerse de una barra invertida. Aún estamos trabajando en una alternativa más elegante. Mientras tanto, duplique las comillas sencillas como en los ejemplos siguientes. Cualquier solución a este problema en futuras versiones de Postgres mantendrán la compatibilidad con esto.
Las dos funciones siguientes son idénticas a sus contrapartidas que se verán cuando estudiemos el lenguaje C.
CREATE FUNCTION add_one (int4) RETURNS int4 AS '
BEGIN
RETURN $1 + 1;
END;
' LANGUAGE 'plpgsql';
|
CREATE FUNCTION concat_text (text, text) RETURNS text AS '
BEGIN
RETURN $1 || $2;
END;
' LANGUAGE 'plpgsql';
|
De nuevo, estas funciones PL/pgSQL tendrán su equivalente en lenguaje C.
CREATE FUNCTION c_overpaid (EMP, int4) RETURNS bool AS '
DECLARE
emprec ALIAS FOR $1;
sallim ALIAS FOR $2;
BEGIN
IF emprec.salary ISNULL THEN
RETURN ''f'';
END IF;
RETURN emprec.salary > sallim;
END;
' LANGUAGE 'plpgsql';
|
Estos procedimientos desencadenados aseguran que, cada vez que se inserte o actualice un fila en la tabla, se incluya el nombre del usuario y la fecha y hora. Y asegura que se proporciona un nombre de empleado y que el salario tiene un valor positivo.
CREATE TABLE emp (
empname text,
salary int4,
last_date datetime,
last_user name);
CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS
BEGIN
-- Check that empname and salary are given
IF NEW.empname ISNULL THEN
RAISE EXCEPTION ''empname cannot be NULL value'';
END IF;
IF NEW.salary ISNULL THEN
RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
END IF;
-- Who works for us when she must pay for?
IF NEW.salary < 0 THEN
RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := ''now'';
NEW.last_user := getpgusername();
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
|