PL/pgSQL

PL/pgSQL es un lenguaje procedural cargable para el sistema de bases de datos Postgres.

Este paquete fue escrito originalmente por Jan Wieck.

Panor�mica

Los objetivos de dise�o de PL/pgSQL fueron crear un lenguaje procedural cargable que

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.

Descripcion

Estructura de PL/pgSQL

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;
Puede haber cualquier numero de subbloques en la secci�n de sentencia de un bloque. Los subloques pueden usarse para ocultar variables a otros bloques de sentencias. Las variables declaradas en la secci�n de declaraciones se inicializan a su valor por defecto cada vez que se inicia el bloque, no cada vez que se realiza la llamada a la funci�n.

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.

Comments

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.

Declaraciones

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:

name [ CONSTANT ] >typ> [ NOT NULL ] [ DEFAULT | := value ];

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.

name class%ROWTYPE;

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.

name RECORD;

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.

name ALIAS FOR $n;

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

RENAME oldname TO newname;

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.

Tipos de datos

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.

Expressions

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
    
usando el gestor SPI. En la expresi�n, las apariciones de los identificadores de variables son sustituidos por par�metros, y los valores reales de las variables son pasadas al ejecutor en la matriz de par�metros. Todas las expresiones usadas en una funci�n PL/pgSQL son preparadas de una sola vez, y guardadas una �nica vez.

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';
    
y
    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 logfunc1(), el analizador principal de Postgres sabe cuando prepara la ejecuci�n de INSERT que la cadena 'now' debe ser interpretada como una fecha, dado que el campo objeto de 'logtable' tiene ese tipo. As�, har� una constante de ese tipo, y el valor de esa constante se emplear� en todas las llamadas a logfunc1(), durante toda la vida �til de ese proceso. No hay que decir que eso no era lo que pretend�a el programador.

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.

Sentencias

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.

Asignaci�n

Una asignaci�n de un valor a una variable o campo de fila o de registro se escribe:

    identifier := expression;
    
Si el tipo de dato resultante de la expresi�n no coincide con el tipo de dato de las variables, o la variable tienen un tama�o o precisi�n conocido (como char(29)), el resultado ser� amoldado impl�citamente por el interprete de bytecode de PL/pgSQL, usando los tipos de las variables para las funciones de entrada y los tipos resultantes en las funciones de salida. N�tese que esto puede potencialmente producir errores de ejecuci�n generados por los tipos de las funciones de entrada.

Una asignaci�n de una selecci�n completa en un registro o fila puede hacerse del siguiente modo:

    SELECT expressions INTO target FROM ...;
    
target puede ser un registro, una variable de fila o una lista separada por comas de variables y campo de de registros o filas.

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;
    
Si la selecci�n devuelve m�ltiples filas, solo la primera se mueve a los campos objetivo. todas las dem�s se descartan.

Llamadas a otra funci�n

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
    
Esto ejecuta 'SELECT query' en el gestor SPI, y descarta el resultado. Los identificadores como variables locales son de todos modos sustituidos en los par�metros.

Volviendo de la funci�n

    RETURN expression
    
La funci�n termina y el valor de expression se devolver� al ejecutor superior. El valor devuelto por una funci�n no puede quedar son definir. Si el control alcanza el fin del bloque de mayor nivel de la funci�n sin encontrar una sentencia RETURN, ocurrir� un error de ejecuci�n.

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.

Abortando la ejecuci�n y mensajes

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 [...]];
    
##################################################################### Dentro del formato, "%" se usa como situaci�n para los subsecuentes identificadores, separados por comas. Los posibles niveles son DEBUG (suprimido en las bases de datos de producci�n), NOTICE (escribe en el registro de la base de datos y lo env�a a la aplicaci�n del cliente) y EXCEPTION (escribe en el registro de la base de datos y aborta la transacci�n).

Condiciones

    IF expression THEN
        statements
    [ELSE
        statements]
    END IF;
    
expression debe devolver un valor que al menos pueda ser adaptado en un tipo booleano.

Bucles

Hay varios tipos de bucles.

    [<<label>>]
    LOOP
        statements
    END LOOP;
    
Se trata de un bucle no condicional que ha de ser terminado de forma explicita, mediante una sentencia EXIT. La etiqueta opcional puede ser usado por las sentencias EXIT de otros bucles anidados, para especificar el nivel del bucle que ha de terminarse.
    [<<label>>]
    WHILE expression LOOP
        statements
    END LOOP;
    
Se trata de un lazo condicional que se ejecuta mientras la evaluaci�n de expression sea cierta.
    [<<label>>]
    FOR name IN [ REVERSE ]
express .. expression LOOP
        statements
    END LOOP;
    
Se trata de un bucle que se itera sobre un rango de valores enteros. La variable name se crea autom�ticamente con el tipo entero, y existe solo dentro del bucle. Las dos expresiones dan el limite inferior y superior del rango y son evaluados s�lo cuando se entra en el bucle. El paso de la iteraci�n es siempre 1.
    [<<label>>]
    FOR record | row IN select_clause LOOP
        statements
    END LOOP;
    
EL registro o fila se asigna a todas las filas resultantes de la clausula de selecci�n, y la sentencia se ejecuta para cada una de ellas. Si el bucle se termina con una sentencia EXIT, la ultima fila asignada es a�n accesible despu�s del bucle.
    EXIT [ label ] [ WHEN expression ];
    
Si no se incluye label, se termina el lazo m�s interno, y se ejecuta la sentencia que sigue a END LOOP. Si se incluye label ha de ser la etiqueta del bucle actual u de otro de mayor nivel. EL bucle indicado se termina, y el control se pasa a la sentencia de despu�s del END del bucle o bloque correspondiente.

Procedimientos desencadenados

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:

NEW

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.

OLD

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.

TG_NAME

Nombre de tipo de dato; es una variable que contiene el nombre del procedimiento desencadenado que se ha activado.

TG_WHEN

Tipo de dato texto; es una cadena de caracteres del tipo 'BEFORE' o 'AFTER', dependiendo de la definici�n del procedimiento desencadenado.

TG_LEVEL

Tipo de dato texto; una cadena de 'ROW' o 'STATEMENT', dependiendo de la definici�n del procedimiento desencadenado.

TG_OP

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.

TG_RELID

Tipo de dato oid; el ID del objeto de la tabla que ha provocado la invocaci�n del procedimiento desencadenado.

TG_RELNAME

Tipo de dato nombre; el nombre de la tabla que ha provocado la activaci�n del procedimiento desencadenado.

TG_NARGS

Tipo de dato entero; el numero de argumentos dado al procedimiento desencadenado en la sentencia CREATE TRIGGER.

TG_ARGV[]

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.

Excepciones

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.

Ejemplos

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.

Algunas funciones sencillas en PL/pgSQL

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';
    

Funciones PL/pgSQL para tipos compuestos

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';
    

Procedimientos desencadenados en PL/pgSQL

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