Bases de Datos objeto-relacionales

Introducción a las Bases de Datos objeto-relacionales

Las Bases de Datos objeto-relacionales son una extensión de las Bases de Datos relacionales tradicionales, a las cuales se les añaden ciertas características propias de la Programación Orientada a Objetos. Algunas de estas características se comentarán en este bloque, concretamente para el motor de Bases de Datos objeto-relacional PostgreSQL.

Antes de continuar con las extensiones propias de las Bases de Datos objeto-relacionales, hay que destacar las diferencias existentes entre PostgreSQL y el motor MySQL/MariaDB con el que se ha venido trabajando ten los bloques anteriores. Hay que tener en cuenta que, independientemente de que el nuevo motor sea objeto-relacional, existirán algunas diferencias de funcionamiento y de síntaxis a la hora de trabajar con el mismo.

Conectar con PostgreSQL

A la hora de conectar con PostgreSQL, como ocurría con MySQL, tenemos dos opciones:

Conectar mediante su propia consola de comandos (lado cliente)
Utilizar algún cliente con interfaz gráfica (PostgreSQL incluye la suya propia, pgAdmin)

En este apartado veremos como manejarnos con la primera de las opciones, con la consola de comandos de PostgreSQL de forma que al menos sepamos llevar a cabo las funciones básicas como conectarnos, acceder a una Base de Datos y realizar las operaciones más comunes sobre ella.

Conectar con PostgreSQL: Ejecutaremos el comando psql

# Conectarse al servidor con un usuario (por defecto postgres)
psql -U miusuario;

# Listar las bases de datos existentes en el servidor
\list

# Crear una base de datos
CREATE DATABASE mi_basedatos;

# Conectarse a una base de datos existente
\connect mi_basedatos;

# Mostrar las tablas de la base de datos actual (display tables)
\dt

# Conectarse al servidor seleccionando una base de datos
psql -U miusuario -d mi_basedatos;

Cuando instalamos postgreSQL, por defecto se crea siempre el usuario postgres, que es con el que nos debemos de conectar mientras que no tengamos otros usuarios.

Herramienta de administración pgAdmin

PgAdmin es una herramienta que ofrece una interfaz gráfica para facilitar la gestión de las bases de datos en el SGBD PostgreSQL. En el instalador para sistemas Windows de PosgreSQL, se nos permite instalarla directamente. Es una herramienta de aspecto muy parecido a MySQL Workbench, o phpMyAdmin.

Tipos de datos

Tipos de datos básicos

Independientemente de los nuevos tipos de datos que postgreSQL incorpora por ser un motor objeto-relacional, presenta algunas diferencias en cuanto a declarar los tipos tradicionales que ya conociamos en MySQL/MariaDB:

VARCHAR sigue existiendo pero podemos usar TEXT para el que no es necesario indicar la longitud máxima.
INTEGER se declara de la misma forma, como INTEGER o INT.
FLOAT se declara como REAL y se utiliza para indicar números de coma flotante de precisión simple
DOUBLE se declara como DOUBLE PRECISION para indicar números de coma flotante de doble precisión
También muy común utilizar el tipo de datos NUMERIC para almacenar cantidades de moneda o valores donde la precisión es importante.
En el caso de campos autonuméricos se utiliza la palabra reservada SERIAL sin indicar el tipo de dato, aunque internamente se almacena utilizando un campo Integer de 4 bytes

En el siguiente enlace de la documentación ¹⁾ podemos ver los tipos de datos existentes en PostgreSQL y sus alias.

Colecciones

Los arrays ²⁾, como tipo de datos colección, puede ser usado en BBDD objeto-relacionales, creando así estructuras de datos más complejas tal y como se hace con lenguajes de programación de aplicaciones. De este modo podemos almacenar atributos multivaluados:

CREATE TABLE personas (
  id               SERIAL PRIMARY KEY,
  nombre           VARCHAR(40),
  apellidos        TEXT,
  fecha_nacimiento DATE,
  fecha_alta       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  telefonos        TEXT[],
);

Que a la hora de trabajar en SQL sería asi (Presta atención a cómo debe asignarse el valor del array):

-- Inserta una persona con varios números de teléfono
INSERT INTO personas 
  (nombre, apellidos, fecha_nacimiento, telefonos) 
  VALUES ('Peter', 'Parker', '1999-01-08', 
          '{"976654321", "654786556", "976123456"}');
 
-- Muestra todos los teléfonos de una persona
SELECT telefonos 
FROM personas 
WHERE nombre = 'Peter' AND apellidos = 'Parker';
 
-- Muestra el primer teléfono de una persona
SELECT telefonos[1] 
FROM personas 
WHERE nombre = 'Peter' AND apellidos = 'Parker';
 
-- Muestra desde el primero al segundo teléfono de una persona
SELECT telefonos[1:2] 
FROM personas 
WHERE nombre = 'Peter' AND apellidos = 'Parker';
 
-- Muestra todos los teléfonos de una persona
SELECT telefonos[1:array_length(telefonos, 1)]   -- equivalente a telefonos
FROM personas 
WHERE nombre = 'Peter' AND apellidos = 'Parker';
 
-- Muestra el nombre y apellidos de las personas que 
-- tienen un número determinado
SELECT nombre, apellidos
FROM personas
WHERE '97654321' = ANY(telefonos);

Además PostgreSQL proporciona una serie de funciones para operar sobre los array ³⁾:

Suponemos un vector compuesto por 3 ciudad españolas

ciudades = {'madrid', 'zaragoza', 'barcelona'}

array_length(unarray, dimension): Devuelve la longitud del array de la dimensión especificada

SELECT array_length(ciudades, 1)
> 3

array_cat(unarray, otroarray): Concatena ambos array y devuelve el resultado

SELECT array_cat(ciudades, ciudades)
>{'madrid', 'zaragoza', 'barcelona', 'madrid', 'zaragoza', 'barcelona'}

array_replace(unarray, valor_viejo, valor_nuevo): Reemplaza un valor por otro en todo el array

SELECT array_replace(ciudades, 'zaragoza', 'teruel')
>{'madrid', 'teruel', 'barcelona'}

array_to_string(unarray, texto): Convierte un vector en una cadena de texto

SELECT array_to_string(ciudades, ',')
> madrid,zaragoza,barcelona

string_to_array(texto, texto): Convierte un texto es un vector

SELECT string_to_array('madrid,zaragoza,barcelona', ',')
> {madrid,zaragoza,barcelona}

Datos estructurados

Con este tipo de datos, Composite⁴⁾ en PostgreSQL, existe la posibilidad de crear estructuras de datos más complejas, compuestas de varios campos de información. Se utilizan para definir la estructura de una fila o un registro. De este modo podemos almacenar atributos estructurados:

CREATE TYPE direccion_postal AS (
  calle     TEXT,
  numero    INT,
  provincia TEXT,
  cp        TEXT
);
 
CREATE TABLE personas (
  id               SERIAL PRIMARY KEY,
  nombre           TEXT,
  apellidos        TEXT,
  direccion        direccion_postal
)

Así, en el caso de una inserción, sería:

INSERT INTO personas (nombre, apellidos, direccion)
  VALUES ('Peter', 'Parker', ROW('mi calle', 23, 'Zaragoza', 50018));

La palabra ROW es opcional, siempre que indiquemos la cantidad necesaria de valores.

Que a la hora de consultar sería como sigue:

SELECT direccion
FROM personas
WHERE nombre = 'Peter' AND apellidos = 'Parker';
 
SELECT (direccion).cp 
FROM personas 
WHERE nombre = 'Peter' AND apellidos = 'Parker';

En el caso de necesitar un array de estructuras sería de la siguiente manera:

CREATE TYPE direccion_postal AS (
  calle TEXT,
  cp TEXT
);

CREATE TABLE alumnos (
  id SERIAL PRIMARY KEY,
  nombre TEXT NOT NULL,
  apellidos TEXT NOT NULL,
  email TEXT UNIQUE,
  direcciones direccion_postal[],
);

Que a la hora de insertar datos quedaría:

INSERT INTO alumnos (nombre, apellidos, email, direcciones)
    VALUES ('nombre', 'apellidos', 'asda@asda.es',
    array[ROW('calle', '4663'), ROW('calle2', '34334')]::direccion_postal[]);

Enumeraciones

Las enumeraciones⁵⁾ en PostgreSQL se definen como un nuevo tipo de datos compuesto de una lista fija de valores constantes.

CREATE TYPE colores AS ENUM ('rojo', 'amarillo', 'verde', 'negro', 'blanco');

Además PostgreSQL proporciona una serie de funciones⁶⁾ para trabajar sobre este tipo de datos:

enum_first: Devuelve el primer valor de una enumeración

SELECT enum_first(NULL::colores)
> rojo

enum_last: Devuelve el último valor de una enumeración

SELECT enum_last(NULL::colores)
> blanco

enum_range: Devuelve el rango de valores entre dos dados

SELECT enum_range(NULL::colores)
> {rojo, amarillo, verde, negro, blanco}
 
SELECT enum_range('amarillo'::colores, 'negro'::colores)
> {amarillo, verde, negro}

Así, es posible utilizar enumeraciones previamente declaradas como tipos de datos para las columnas de una tabla

CREATE TABLE muebles (
  id     SERIAL PRIMARY KEY,
  nombre TEXT,
  peso   REAL,
  color  colores
);

Restricciones Check

En PostgreSQL recuperamos las restricciones de tipo check que existen en otros SGBD. Con ella podemos obligar a que los datos de un campo cumplan unas condiciones, rechazando la inserción o modificación si no cumple las condiciones:

CREATE TABLE muebles (
   id     SERIAL PRIMARY KEY,
  nombre TEXT,
  fecha_nacimiento DATE,
  fecha_alta DATE,
  peso   REAL CHECK (peso > 50 AND peso < 100),
  precio DOUBLE PRECISION CHECK (precio >= 0),
  CHECK (fecha_alta > fecha_nacimiento)
);

Claves ajenas

Para definir las claves ajenas en una tabla lo podemos hacer del mismo modo en que lo hacíamos en MySQL. Al igual que en MySQL habrá que tener en cuenta que la tabla a la que se hace referencia debe haber sido creada ya, por lo que tendremos cuidado de colocarla siempre por delante en el script de creación de las mismas. Pero tambien podemos crearla sin indicar las palabras FOREIGN KEY.

CREATE TABLE ciudades (
  id        SERIAL PRIMARY KEY,
  nombre    TEXT,
  extension REAL
);
 
CREATE TABLE habitantes (
  id        SERIAL PRIMARY KEY,
  nombre    TEXT,
  apellidos TEXT,
  ciudad    INT REFERENCES ciudades(id)
);

Si no indicamos el campo al que queremos que haga referencia la clave ajena, referenciará por defecto a la clave primaria de la tabla indicada:

CREATE TABLE habitantes (
  id        SERIAL PRIMARY KEY,
  nombre    TEXT,
  apellidos TEXT,
  ciudad    INT REFERENCES ciudades
);

Herencia

La herencia⁷⁾ entre tablas permite, de forma similar a lo que ocurre en Programación Orientada a Objetos entre diferentes clases, que una tabla incorpore los campos de otra indicando que una hereda de la otra. Además, conlleva algún comportamiento adicional que veremos a continuación:

CREATE TABLE articulos (
  id          SERIAL PRIMARY KEY,
  nombre      TEXT,
  descripcion TEXT,
  precio      REAL
);
 
CREATE TABLE comestibles (
  fecha_caducidad TIMESTAMP,
  ingredientes    TEXT[]
) INHERITS (articulos);

En este caso, si queremos listar el nombre y descripción de todos los artículos con un precio superior a 10 euros, incluyendo los comestibles:

SELECT nombre, descripcion 
FROM articulos 
WHERE precio > 10;

Por otro lado, puede ser útil mostrar solamente aquellos artículos que no son comestibles

SELECT nombre, descripcion
FROM ONLY articulos
WHERE precio > 10;

En el caso de que sólo queramos mostrar aquellos artículos que son comestibles, simplemente accedemos a la tabla correspondiente:

SELECT nombre, descripcion
FROM comestibles
WHERE precio > 10;

Limitaciones de la herencia

A pesar de los beneficios y la utilidad de estos conceptos de herencia que incorpora PostgreSQL, hay que tener en cuenta que a su vez presenta ciertas limitaciones⁸⁾ que habrá que considerar. La principal limitación es que los índices (PRIMARY, UNIQUE, etc) y las claves ajenas se aplican a cada tabla de forma independiente, pero no a sus herederas. Esto se aplica tanto a las tablas referenciadas como a las que referencian. Lo vemos en los siguientes ejemplos.

Caso 1. Supongamos, siguiendo con el ejemplo anterior, la siguiente Base de Datos:

CREATE TABLE proveedores (
  id        SERIAL PRIMARY KEY,
  nombre    TEXT NOT NULL,
  categoria TEXT
);
 
CREATE TABLE articulos (
  id           SERIAL PRIMARY KEY,
  nombre       TEXT,
  codigo       TEXT UNIQUE,
  descripcion  TEXT,
  id_proveedor INT REFERENCES proveedores
);
 
CREATE TABLE comestibles (
  fecha_caducidad TIMESTAMP,
  ingredientes    TEXT[]
) INHERITS (articulos);

En el caso anterior:

La tabla comestibles hereda la columna id_proveedor sin embargo la restricción de clave ajena no es heredada.
Esto quiere decir que no hay ningún control sobre los valores de la columna id_proveedor de la tabla comestibles, pudiendo tener valores que no existen en la clave primaria de la tabla proveedores.
Del mismo modo, no hay control de valores únicos en la tabla comestibles para el campo codigo ni para la clave primaria.

Podemos intentar solucionar el problema anterior definiendo los mismos campos con las mismas restricciones en la tabla hija comestibles. Cuando una tabla hija tiene campos iguales a su tabla padre, estos campos se fusionan en uno solo. Aunque seguiremos permitiendo datos repetidos entre las dos tablas.

CREATE TABLE comestibles (
  id SERIAL PARIMARY KEY,
  fecha_caducidad TIMESTAMP,
  ingredientes    TEXT[]
  codigo       TEXT UNIQUE,
  id_proveedor INT REFERENCES proveedores
) INHERITS (articulos);

Caso 2. Ahora planteamos otra situación: la tabla artículos es referenciada por otra tabla pedidos.

CREATE TABLE articulos (
  id           SERIAL PRIMARY KEY,
  nombre       TEXT,
  descripcion  TEXT
);
 
CREATE TABLE comestibles (
  fecha_caducidad TIMESTAMP,
  ingredientes    TEXT[]
) INHERITS (articulos);
 
CREATE TABLE pedidos (
  id SERIAL PRIMARY KEY,
  fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  id_articulo INT REFERENCES articulos
);

La tabla pedidos tiene un campo id_articulo que hace referencia a la tabla articulos, por lo que pueden contener valores de la clave primaria de articulos.
Sin embargo no existe una clave primaria que referencia a la tabla hija comestibles por lo que no podremos introducir valores para relacionar los pedidos con los comestibles.
No existe solución para esta cuestión. Si queremos permitir las relaciones debemos omitir la restricción de clave ajena.

Funciones

Funciones matemáticas

abs(numero): Devuelve el valor absoluto de un número

SELECT abs(-4)
> 4

Funciones de cadena

concat(cadena1, cadena2): Concatena dos cadena y devuelve el resultado

SELECT concat('esto será ', 'una cadena')
> esto será una cadena

length(cadena): Devuelve la longitud de una cadena

SELECT LENGTH('una cadena')
> 10

md5(texto): Devuelve el hash del texto que se pasa como parámetro

SELECT md5('texto')
> 62059a74e9330e9dc2f537f712b8797c

substr(cadena, indice, cantidad): Devuelve la subcadena que resulta de extraer desde el índice especificado el número de caracteres indicados por cantidad

SELECT substr('una cadena', 5, 3)
> cad

Funciones de Fecha

age(timestamp): Devuelve el tiempo pasado entre hoy y la fecha que se pasa como parámetro

SELECT age('2000-01-01')
> 17 years 3 mons 11 days

age(timestamp1, timestamp2): Devuelve el tiempo pasado entre las dos fechas

SELECT age('2010-04-05', '2000-01-01')
> 10 years 3 mons 4 days

current_date: Devuelve la fecha de hoy

SELECT CURRENT_DATE
> 2012-12-03

current_time: Devuelve la hora actual

SELECT CURRENT_TIME
> 18:05:25.13039485

current_timestamp: Devuelve la fecha y hora de hoy

SELECT CURRENT_TIMESTAMP
> 2022-12-03 11:35:32.58700

date(timestamp): Devuelve la fecha de un timestamp (sin la hora)

SELECT DATE(CURRENT_TIMESTAMP)
> 2022-12-03

extract(datepart): Extrae una parte de fecha de una fecha determinada

SELECT EXTRACT(MONTH FROM CURRENT_DATE)
> 12
SELECT EXTRACT(DAY FROM CURRENT_DATE)
> 03

date [+, -] entero: Obtiene la fecha resultante de sumar o restar la cantidad de días indicados

SELECT CURRENT_DATE + 7;
> 2022-05-17

date - date: indica la diferencia en días entre dos fechas

SELECT CURRENT_DATE - '2022-04-06';
> 45

date [+, -] interval: Devuelve la fecha y hora resultante de sumar o restar el intervalo indicado a la fecha

SELECT CURRENT_DATE + INTERVAL '3 year';
> 2025-05-17

Funciones de información del sistema

current_database()

SELECT current_database()
> prueba

current_user

SELECT CURRENT_USER
> postgres

version()

SELECT version()
PostgreSQL 9.5.3 ON x86_64-pc-linux-gnu, compiled BY gcc (Debian 5.3.1-19) 5.3.1 20160509, 64-bit

Programación en PostgreSQL

Lenguajes en PostgreSQL

A la hora de crear funciones o procedimientos, en PostgreSQL podemos utilizar diferentes lenguajes. En la claúsula <lenguaje> debemos indicar el lenguaje que queramos usar para escribir el código, y nosotros nos centraremos en dos de ellos: SQL y PL/pgsql. Language se puede indicar antes o después del código de la función.

Si empleamos el primero podremos prescindir de las marcas de inicio y final de bloque (BEGIN y END) y no podremos declarar variables, por lo que el bloque DECLARE no puede aparecer. PL/pgsql al contrario, es un lenguaje procedural que permite utilizar mecanismos más potentes de programación como las sentencias de control de flujo o la declaración de variables.

A grandes rasgos, utilizaremos SQL:

problemas más sencillos y directos
funciones que se llamen dentro de consultas SQL
menos código

El lenguaje PL/PgSQL lo centraremos en:

necesitamos elementos no disponibles en SQL (bucles, if-else, variables, control de errores)
necesitamos control de errores
se van a ejecutar repetidamente, ya que se pueden almacenar en caché
para crear triggers

A continuación se muestran algunos ejemplos de funciones almacenadas de PostgreSQL con los diferentes lenguajes que se han comentado.

La primera función es un simple ejemplo que lanza una sentencia SQL y no devuelve ningún valor. Será suficiente con una simple sentencia SQL por lo que podemos utilizar únicamente dicho lenguaje y especificarlo asi al final de la función.

CREATE FUNCTION limpiar_articulos() RETURNS void AS $$
  DELETE FROM articulos WHERE precio < 0;
  . . .
$$ LANGUAGE SQL;

La función equivalente utilizando plpgsql como lenguaje sería la siguiente.

CREATE FUNCTION limpiar_articulos() RETURNS void AS $$
BEGIN
  DELETE FROM articulos WHERE precio < 0;
  . . .
END;
$$ LANGUAGE plpgsql;

La siguiente función devuelve un valor entero, pero no es necesario indicar RETURN:

CREATE FUNCTION cantidad_articulos(p_pedido INT) RETURNS INT
LANGUAGE SQL AS $$
  SELECT COUNT(*) FROM articulos WHERE id = p_pedido;
$$;

Funciones almacenadas

Las funciones de PostgreSQL, como ocurre con las de MySQL, quedan almacenadas en la Base de Datos donde se crean y pueden ser luego utilizadas en otras estructuras de código o bien directamente desde las consultas SQL. Además, como ocurre en todos los lenguajes de programación, las funciones de PostgreSQL deben devolver siempre un valor, aunque, como ocurre en lenguajes como Java, es posible indicar void como palabra reservada en el tipo de devolución y entonces la función ya no tiene que devolver un valor.

CREATE [OR REPLACE] FUNCTION <nombre_funcion>(<param1> <tipo>, <param2> <tipo>)
 RETURNS <tipo> AS $$
DECLARE
 -- Declaración de variables
BEGIN
 -- Instrucciones
END;
$$ LANGUAGE <lenguaje>;

Lenguaje PL/pgsql

Si queremos poder utilizar un lenguaje de programación completo para realizar funciones almacenadas tendremos que indicar, como ya se ha adelantado anteriormente, que usamos el lenguaje plpgsql que permitirá emplear sentencias de flujo de código, declaración de variables, asignaciones, . . .

La siguiente función incrementa en una cantidad el precio de un articulo y devuelve el precio final del mismo

CREATE FUNCTION subir_precio(id_articulo INTEGER, subida REAL) 
  RETURNS REAL AS $$
DECLARE
  precio_final INT;
BEGIN
  UPDATE articulos
    SET precio = precio + subida
    WHERE id = id_articulo;
  precio_final := (SELECT precio FROM articulos WHERE id = id_articulo);
  RETURN precio_final;
END;
$$ LANGUAGE plpgsql;

La siguiente función realiza la misma operación pero no se ha dado nombre a los parámetros sino que se ha utilizado su posición en la declaración de la función para identificarlos

CREATE FUNCTION subir_precio(INTEGER, REAL) 
  RETURNS REAL AS $$
DECLARE
  precio_final INT;
BEGIN
  UPDATE articulos
    SET precio = precio + $2
    WHERE id = $1;
  precio_final := (SELECT precio FROM articulos WHERE id = $1);
  RETURN precio_final;
END;
$$ LANGUAGE plpgsql;

La siguiente función vuelve a realizar la misma operación pero se utiliza una claúsula de la propia sentencia UPDATE para devolver el precio final del articulo

CREATE FUNCTION subir_precio(INTEGER, REAL) 
  RETURNS REAL AS $$
BEGIN
  UPDATE articulos
    SET precio = precio + $2
    WHERE id = $1
    RETURNING precio;
END;
$$ LANGUAGE plpgsql;

El siguiente bloque (función y su llamada en una consulta) muestra cómo es posible pasar como parámetro una fila completa para, desde la función, acceder a los campos que sean necesarios para realizar la operación que se desee

CREATE FUNCTION precio_iva(articulos) 
  RETURNS REAL AS $$
BEGIN
  SELECT $1.precio * 1.16;
END;
$$ LANGUAGE plpgsql;

SELECT nombre, precio_iva(articulos.*)
FROM articulos

Variables

Las variables se deben declarar antes del bloque BEGIN-END del cuerpo de la función o procedimiento.

DECLARE
   variable1 INT;
   variable2 TEXT;
BEGIN
. . .
END

Para asignar valor a una variable utilizamos la siguiente sintaxis:

   variable1 := 67;
   variable1 := (SELECT COUNT(*) FROM usuarios);

En el lenguaje plpgsql no podemos usar la sentencia SELECT, sin la clausula INTO:

   SELECT nombre INTO variable2 FROM usuarios WHERE id = $1;

Sentencias de control de flujo

Sentencia IF

IF condicion-1 THEN
  . . .
ELSIF condicion-2 THEN
  . . .
ELSE
  . . .
END IF:

Sentencia LOOP

<etiqueta>
LOOP
 -- Instrucciones
 EXIT [<etiqueta>] WHEN <condicion>;
END LOOP;

Sentencia FOR

[ <etiqueta> ]
FOR <variable_contador> IN [REVERSE] <valor_inicial>.. <valor_inicial> [BY <expresion>] LOOP
    -- Instrucciones
END LOOP [etiqueta];

Sobrecarga de funciones

PostgreSQL permite lo que se conoce como sobrecarga de funciones, que consiste en que es posible declarar más de una función con el mismo nombre siempre y cuando cambie el número de parámetros de la misma.

La siguiente función incrementa el precio de un artículo en una cantidad determinada pero comprueba además que el precio final no sobrepase un precio impuesto como límite. En ese caso no realiza ninguna acción y devuelve un valor NULL. En este caso esta segunda función sobrecarga a la primera y en función de los parámetros que se pasen PostgreSQL ejecutará una u otra.

CREATE FUNCTION subir_precio(id_articulo INTEGER, subida REAL, precio_maximo REAL) RETURNS REAL AS $$
DECLARE
  precio_final REAL;
BEGIN
  precio_final := (SELECT precio FROM articulos WHERE id = id_articulo) + subida;
  IF precio_final > precio_maximo THEN
    RETURN NULL;
  END IF;
  UPDATE articulos
    SET precio = precio + subida
    WHERE id = id_articulo;
  RETURN precio_final;
END;
$$ LANGUAGE plpgsql;

Eliminar una función sobrecargada

Para eliminar una función se utiliza la instrucción DROP FUNCTION de la siguiente manera.

DROP FUNCTION [IF EXISTS] <nombre_funcion>(tipo_param1, tipo_param2, . . .);

Por ejemplo, si quisieramos eliminar las últimas dos funciones creadas justo arriba

DROP FUNCTION subir_precio(INTEGER, REAL);
DROP FUNCTION subir_precio(INTEGER, REAL, REAL);

Procedimientos almacenados

Desde la versión 11 de PostgreSQL, se permite crear procedimientos almacenados. A pesar de que hasta ahora teníamos funciones de tipo void, los procedimientos tienen algunas diferencias notables:

Los procedimientos no devuelven nada, las funciones de tipo void devuelven NULL.
En un procedimiento sí podemos indicar la sentencia RETURN para terminar su ejecución.
Los procedimientos pueden validar (commit) o anular (rollback) transacciones, mientras que las funciones no.
Se invocan mediante la palabra CALL.
No permiten ser usamos en otras consultas(SELECT, UPDATE, etc …) a diferencia de las funciones.

CREATE [OR REPLACE] PROCEDURE desactivar_cuentas_sin_balance()
LANGUAGE SQL AS $$
  UPDATE cuentas SET activa = FALSE WHERE balance < 0;
  . . .
$$;
 
CALL desactivar_cuentas_sin_balance();

Transacciones

En PostgreSQL una transacción se define entre las instrucciones BEGIN y COMMIT de la siguiente manera. Si utilizo el lenguaje plpgsql es probable que ya haya iniciado un bloque BEGIN por lo que solo me faltará indicar COMMIT en el momento en que quiera validar la transacción:

BEGIN;
  UPDATE ciudades SET extension = 100 WHERE id = 3;
  UPDATE habitantes SET ciudad = 1 WHERE id = 2;
  . . .
  COMMIT;
END;

Si ocurre algún error en alguna de las instrucciones de la transacción, ninguna de las operaciones realizadas se validan.

Hay que tener en cuenta que algunas aplicaciones clientes engloban todas las instrucciones como transacciones de forma implícita, por lo que conviene leer detenidamente la documentación de dichas aplicaciones.

Último valor secuencial

En caso de que necesite el id secuencial de una columna de tipo SERIAL, puedo utilizar la cláusula RETURNING de la sentencia INSERT INTO.

En caso de que el nombre de la columna de tipo SERIAL sea 'id':

. . .
DECLARE v_id_generado INT;
 
BEGIN;
   INSERT INTO jugadores(nombre, apellidos)
   VALUES ('Laura', 'Llanos')
   RETURNING id INTO v_id_generado;
 
   INSERT INTO jugador_mision
   VALUES(v_id_generado, 8)
 
   COMMIT;
. . .
END;

Triggers

De forma similar a como ocurren en MySQL, los triggers en PostgreSQL se ejecutan siempre asociados a un evento que ha ocurrido sobre una tabla. A diferencia de MySQL los triggers ejecutan una función para realizar las operaciones requeridas. Estas funciones se conocen como trigger functions.

CREATE TRIGGER trigger_name {BEFORE | AFTER } {event [ OR ... ]} 
   [OF campo] ON TABLE_NAME
   [FOR [EACH] {ROW | STATEMENT}]
   [WHEN {condicion}]
   EXECUTE FUNCTION trigger_function

OF nombre_campo : Podemos indicar que solo se active cuando un campo de una tabla sea el objeto de la operación. Por ejemplo UPDATE reservas SET precio = 15 …
WHEN : Podemos indicarle que solo ejecute la función cuando se cumple una condición.
FOR EACH STATEMENT : Se ejecuta una sola vez por cada sentencia ejecutada, independientemente del número de filas afectadas.

CREATE TRIGGER nombre_mayusculas
  BEFORE INSERT OR UPDATE
  ON articulos
  FOR EACH ROW
  EXECUTE FUNCTION nombre_mayusculas();

CREATE OR REPLACE TRIGGER check_update
    BEFORE UPDATE OF balance ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION funcion1();

CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
    EXECUTE FUNCTION funcion1();

Funciones de tipo Trigger

Son las funciones en las que implementamos las operaciones que debe realizar un trigger. Se caracterizan porque su valor de retorno es de tipo TRIGGER. Además, cuando estas funciones son llamadas desde un trigger, se crean automáticamente algunas variables que pueden resultar útiles:

Nombre de la variable	Información contenida
NEW	Representa el registro completo afectado en las operaciones INSERT y UPDATE
OLD	Representa el registro completo afectado en las operaciones DELETE y UPDATE
TG_NAME	Nombre del trigger que se ha activado
TG_WHEN	Momento de activación del trigger (BEFORE, AFTER, INSTEAD OF)
TG_OP	Evento que ha activado el trigger (INSERT, DELETE, UPDATE)
TG_TABLE_NAME	Nombre de la tabla que ha causado la activación del trigger

Valor de retorno

Las funciones de tipo TRIGGER devuelven ese tipo de datos. Por lo tanto, como toda función, debe devolver un valor para poder ejecutarse:

Si el trigger es BEFORE debemos devolver la columna afectada: NEW en triggers INSERT y UPDATE, y OLD en triggers DELETE. Esto permite modificar los valores del registro afectado (triggers INSERT y UPDATE). Si devolvemos NULL en estos casos, anularemos la operación que activó el trigger.

En los triggers AFTER el valor de retorno no se tiene en cuenta, por lo que podemos devolver simplemente NULL.

En los triggers FOR EACH STATEMENT siempre devolveremos NULL.

CREATE OR REPLACE FUNCTION nombre_mayusculas()
  RETURNS TRIGGER AS
$$
BEGIN
  NEW.nombre := UPPER(NEW.nombre);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql

CREATE OR REPLACE FUNCTION nombre_mayusculas()
  RETURNS TRIGGER AS
$$
BEGIN
   IF (TG_OP = 'UPDATE') THEN
 
      UPDATE usuarios
      SET cantidad_reservas = cantidad_reservas + 1
      WHERE id = NEW.id;
 
      UPDATE usuarios
      SET cantidad_reservas = cantidad_reservas - 1
      WHERE id = OLD.id;
 
   ELSIF (TG_OP = 'INSERT') THEN
 
      UPDATE usuarios
      SET cantidad_reservas = cantidad_reservas + 1
      WHERE id = NEW.id;
 
   END IF;
 
   RETURN NULL;
END;
$$ LANGUAGE plpgsql

Ejercicios

Se desea gestionar la información correspondiente a un centro de enseñanza:
- Por cada alumno se almacenará la siguiente información: DNI, apellidos, nombre, domicilio, teléfono y ciclo que estudia. También se precisa conocer en cada momento las asignaturas en las que está matriculado, así como la nota en cada una de ellas
- Por cada asignatura guardaremos un código, título y número de horas
- Cada asignatura puede estar impartida por uno o más profesores. Del profesor se deben conocer los mismos datos que para los alumnos, salvo el del ciclo que estudia. El número máximo de asignaturas que puede impartir un profesor es de 6, aunque puede que no imparta ninguna
- Algunos profesores tienen un supervisor (sólo uno) que es otro profesor
- Cada asignatura tendrá un aula asignada, que se identifica mediante el número de edificio y el número de aula. Los números de aulas se pueden repetir entre edificios
La Base de Datos COMPAÑÍA se ocupa de los empleados, departamentos y proyectos de una empresa, de acuerdo con los siguientes requisitos:
- La compañía está organizada en departamentos. Cada departamento tiene un nombre único, un número único y un empleado que lo dirige. Se debe almacenar la fecha en que dicho empleado comenzó a dirigir ese departamento. Hay que tener en cuenta que un departamento puede tener diferentes localizaciones.
- Cada departamento controla un cierto número de proyectos, cada uno de los cuales tiene un nombre y un número únicos y se realizan en un solo lugar
- Se almacena el nombre, número de la Seguridad Social, dirección, salario, sexo y fecha de nacimiento de cada empleado. Todo empleado está asignado a un departamento, pero puede trabajar en varios proyectos que no tienen por que ser del mismo departamento. Nos interesa saber el número de horas que un empleado dedica a cada uno de los proyectos asignados
- También se quiere guardar la relación de las cargas familiares de cada empleado para administrar el seguro médico. Almacenaremos el nombre, sexo y fecha de nacimiento de cada una de las cargas familiares y su parentesco con el empleado
Se quiere diseñar una Base de Datos para almacenar todos los datos de un campeonato de fútbol sala que se organiza este año en la ciudad. Aquellos que quieran participar deberán formar un equipo (nombre, patrocinador, color\_camiseta, color\_2\_camiseta, categoría, . . .) e inscribirse en el campeonato. A medida que transcurran los partidos se irán almacenando los resultados de éstos, así como qué equipos lo jugaron, en qué campo se jugó, quién lo arbitró y alguna incidencia que pudiera haber ocurrido (en caso de que no ocurran incidencias no se anotará nada. Además, los participantes deberán rellenar una ficha de suscripción con algunos datos personales (nombre, apellidos, edad, dirección, teléfono, . . .)
Se quiere diseñar una Base de Datos para controlar el acceso a las pistas deportivas de Zaragoza. Se tendrán en cuenta los siguientes supuestos:
- Todo aquel que quiera hacer uso de las instalaciones tendrá que registrarse y proporcionar su nombre, apellidos, email, teléfono, dni y fecha de nacimiento
- Hay varios polideportivos en la ciudad, identificados por nombre, dirección, extensión (en m2)
- En cada polideportivo hay varias pistas de diferentes deportes. De cada pista guardaremos un código que la identifica, el tipo de pista (tenis, fútbol, pádel, . . .), si está operativa o en mantenimiento, el precio y la última vez que se reservó
- Cada vez que un usuario registrado quiera utilizar una pista tendrá que realizar una reserva previa a través de la web que el ayuntamiento ha creado. De cada reserva queremos registrar la fecha en la que se reserva la pista, la fecha en la que se usará y el precio. Hay que tener en cuenta que todos los jugadores que vayan a hacer uso de la pista deberán estar registrados en el sistema y serán vinculados con la reserva

Prácticas

Práctica 5.1 El modelo objeto-relacional en PostgreSQL
Práctica 5.2 Creación de una Base de Datos O-R sobre PostgreSQL
Práctica 5.3 Consultas y Programación de una Base de Datos O-R sobre PostgreSQL

¹⁾

https://www.postgresql.org/docs/current/datatype.html#DATATYPE-TABLE

²⁾

https://www.postgresql.org/docs/current/arrays.html

³⁾

https://www.postgresql.org/docs/current/functions-array.html

⁴⁾

https://www.postgresql.org/docs/current/static/rowtypes.html

⁵⁾

https://www.postgresql.org/docs/current/datatype-enum.html

⁶⁾

https://www.postgresql.org/docs/current/static/functions-enum.html

⁷⁾

https://www.postgresql.org/docs/current/static/tutorial-inheritance.html

⁸⁾

https://www.postgresql.org/docs/current/static/ddl-inherit.html

Tabla de Contenidos