PL-PGSQL

PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language) es un lenguaje imperativo provisto por el gestor de base de datos PostgreSQL. Permite ejecutar comandos SQL mediante un lenguaje de sentencias imperativas y uso de funciones, dando mucho más control automático que las sentencias SQL básicas.

Desde PL/pgSQL se pueden realizar cálculos complejos y crear nuevos tipos de datos de usuario. Como un verdadero lenguaje de programación, dispone de estructuras de control repetitivas y condicionales, además de la posibilidad de creación de funciones que pueden ser llamadas en sentencias SQL normales o ejecutadas en eventos de tipo disparador (trigger).

Una de las principales ventajas de ejecutar programación en el servidor de base de datos es que las consultas y el resultado no tienen que ser transportadas entre el cliente y el servidor, ya que los datos residen en el propio servidor. Además, el gestor de base de datos puede planificar optimizaciones en la ejecución de la búsqueda y actualización de datos.

Wikipedia

Las funciones escritas en PL/pgSQL aceptan argumentos y pueden devolver valores de tipo básico o de tipo complejo (por ejemplo, registros, vectores, conjuntos o incluso tablas), permitiéndose tipificación polimórfica para funciones abstractas o genéricas (referencia a variables de tipo objeto).

Propiedades de una función programada:

Nombre de la función
Lenguaje de programación (en el curso utilizaremos plpgsql, pero se pueden utilizar otros)
Tipo de dato devuelto (int8, ...)

Parámetros

Se crean los nombres y tipos de variables que recibe la función
Se define la función

Ejercicio 1: Función que recibe parámetros y devuelve una operación realizada con ellos

primera(int a,int b)
DECLARE
    c int8;
BEGIN
    c=3000;
    return a+b+c;
END

Ejercicio 2: Función que utiliza datos provinientes de una consulta SQL

segunda()
DECLARE
    total int8;
BEGIN
    total=0;
    SELECT INTO total sum(salario) FROM templa;
    return total;
END

Ejercicio 3: Función que recibe un parámetro y realiza una consulta SQL con ese valor

tercera(ape character varying(50))
DECLARE
    n int8;
BEGIN
    n=0;
    SELECT INTO n salario FROM templa WHERE apellido=ape;
    return n;
END

SELECT tercera('Yarza') devuelve el sueldo de ese empleado

Ejercicio 4: Uso de la sentencia IF...THEN...ELSE...END

cuarta(int a)
BEGIN
    IF (a<10) THEN
       return 1;
    ELSE
       return 0;
    END IF;
END

Devuelve 0 ó 1 dependiendo de que el parámetro que reciba sea mayot o menor que 10

Ejercicio 5:

Se dispone de una tabla llamada resultados que contiene los resultados de partidos de voley. Esta tabla tiene la siguiente estructura:

num_partido int PK
jornada int
eq_local char(3)
set_local int
eq_visitante char(3)
set_visitante int

Por ejemplo, 1-1-NUM-3-ALM-2, significa que el partido número 1, perteneciente a la primera jornada el Numancia ganó por 3 a 2 a Almería
Sabiendo que el sistema de puntuación de esta competición hace que se den 3 puntos a quien gana por 3-0 ó 3-1, 2 puntos a quien gana por 3-2, 1 punto a quien pierde por 2-3 y 0 puntos a quier pierde por 0-3 ó 1-3, realizar la función puntos() a la que se pasa el código de un equipo y devuelve los puntos conseguidos de acuerdo a sus resultados

puntos (e char(3))
DECLARE
    puntuacion_total int8;
    puntos int 8;
BEGIN
    puntuacion_total=0;

    -- Partidos que el equipo e ha jugado en casa
    SELECT count(*)*3 into puntos FROM partidos WHERE eq_local=e AND set_local=3 AND (set_visitante=0 OR set_visitante=1);
    puntuacion_total=puntuacion_total+puntos;
    SELECT count(*)*2 into puntos FROM partidos WHERE eq_local=e AND set_local=3 AND set_visitante=2;
    puntuacion_total=puntuacion_total+puntos;
    SELECT count(*)*1 into puntos FROM partidos WHERE eq_local=e AND set_local=2 AND set_visitante=3;
    puntuacion_total=puntuacion_total+puntos;

    -- Partidos que el equipo e ha jugado fuera
    SELECT count(*)*3 into puntos FROM partidos WHERE eq_visitante=e AND (set_local=0 OR set_local=1) AND set_visitante=3;
    puntuacion_total=puntuacion_total+puntos;
    SELECT count(*)*2 into puntos FROM partidos WHERE eq_visitante=e AND set_local=2 AND set_visitante=3;
    puntuacion_total=puntuacion_total+puntos;
    SELECT count(*)*1 into puntos FROM partidos WHERE eq_visitante=e AND set_local=3 AND set_visitante=2;
    puntuacion_total=puntuacion_total+puntos;

    return puntuacion_total;
END

Ejercicio 6: Función que maneja una matriz

DECLARE
    a integer[];
BEGIN
    a[0]=35;
    a[1]=44;
    return a;
END;

Funciones que recorren un resultset

La sintaxis para recorrer un resultset registro a registro es:

FOR reg IN sql LOOP
// Acciones con el registro
END LOOP;

Se evalúa la consulta SQL que genera un resultset.
La variable reg (del tipo RECORD) recoge los campos del SQL de forma que se pueden manejar con el formato reg.campo1, reg.campo2, ...
Se repite el proceso para cada registro del resultset

Ejemplo: supongamos que se dispone de una tabla que recoje los movimientos diarios de caja de una empresa y se desea que el campo saldo tenga actualizado su valor

Tabla: diario

id integer PK
fecha date
ingreso double
gasto double
saldo double

DECLARE
    reg RECORD;
    valor double precision;
BEGIN
    valor=0;
    FOR reg IN SELECT id,ingreso,gasto FROM diario ORDER BY id LOOP
        if(reg.ingreso is not null) then
            valor=valor+reg.ingreso;
        end if;
        if(reg.gasto is not null) then
            valor=valor-reg.gasto;
        end if;
        UPDATE diario SET saldo=valor WHERE id=reg.id;
    END LOOP;
    return 1;
END;

Ejercicio de SQL en postgres
Ejercicio de funciones en postgres