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(); |