Si usted
necesita crear una validación en la base de datos postgres que verifique los
valores de una columna sean únicos excepto uno o más valores, a continuación se
presenta como hacerlo mediante función trigger con su respectivo ejemplo.
Caso
ejemplo
Se tiene una
tabla que almacena los datos de los equipos informáticos llamados activos, los
cuales se identifican mediante un serial asignado por el fabricante. La tabla
se crea con la siguiente estructura en el esquema activo:
CREATE TABLE activo.activo
(
co_activo
numeric(9,0),
tx_serial
character varying(30) NOT NULL DEFAULT 'S/S',
tx_descripcion character varying(300) NOT NULL
);
Obsérvese
que la columna tx_serial tiene como valor por defecto la cadena 'S/S'
la cual representa Sin Serial.
Considerando
que los activos para el usuario serán identificados por el serial y no por el
código de identificación asignado por la base de datos (co_activo), y que
existen activos que de fábrica no tienen serial, se requiere permitir que el
único valor que se permite repetir en la columna es 'S/S'. Cualquier
otro valor diferente al indicado, se tomará como el valor serial que identifica
unívocamente el activo. Justamente es allí donde se presenta el problema, dado
que, si creamos una clave de chequeo único (unique key) se asegura que ningún
valor se repita, pero no se podrán identificar aquellos activos que no poseen
serial con el valor S/S. Por ende, se procede a crear la clave única con
excepción utilizando una función con disparador (function trigger).
Creación
de la función
Para crear
la función se deben considerar los siguientes aspectos:
1.
Primero
se debe crear la función y luego el disparador.
2.
La
función devolverá un valor de tipo trigger.
3.
La
función será llamada para eventos Insert/Update.
4.
Siempre
será devuelto el registro NEW que contiene los valores que se desean insertar o
actualizar en al fila.
A
continuación el código de la función encontrarSerial:
CREATE OR REPLACE FUNCTION activo.encontrarSerial()
RETURNS
"trigger" AS
$BODY$
DECLARE
registro
activo.activo%ROWTYPE;
BEGIN
IF (TG_OP
= 'INSERT') THEN
SELECT
* INTO registro FROM activo.activo A
WHERE
A.tx_serial <>'S/S' and A.tx_serial = NEW.tx_serial LIMIT 1;
IF FOUND THEN
RAISE EXCEPTION 'El serial % ya se encuentra
registrado. Intente con otro valor.',NEW.tx_serial;
END IF;
END IF;
IF (TG_OP
= 'UPDATE') THEN
SELECT
* INTO registro FROM activo.activo A
WHERE
A.tx_serial <>'S/S' and A.tx_serial = NEW.tx_serial and
A.co_activo<>NEW.co_activo LIMIT 1;
IF FOUND THEN
RAISE EXCEPTION 'El serial % ya se encuentra
registrado. Intente con otro valor.',NEW.tx_serial;
END IF;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE
plpgsql VOLATILE STRICT;
Explicación:
•
Se
declara una variable llamada 'registro' para almacenar la fila encontrada. El
tipo de dato de la variable se debe indicar que es del mismo tipo de la fila
contrada. Ejm: registro activo.activo%ROWTYPE;
•
Se
verifica el tipo de operación capturada por el disparador (trigger) para
ejecutar la consulta correspondiente. Ejm: IF (TG_OP = 'INSERT') THEN
•
Se
realiza la consulta Select a la tabla activo almacenando el valor encontrado en
la variable registro. Ejm: SELECT * INTO registro FROM activo.activo A
•
En
la clausula Where de la consulta se busca un valor existente diferente al 'S/S'
e igual al nuevo serial. Ejm: WHERE A.tx_serial <>'S/S' and A.tx_serial =
NEW.tx_serial LIMIT 1;
•
Se
verifica la existencia de registro encontrado. Ejem: IF FOUND THEN
•
Si
el valor es encontrado, se dispara un error de excepción y se detiene la
operación del insert. Ejm: RAISE EXCEPTION 'El serial % ya se encuentra
registrado. Intente con otro valor.',NEW.tx_serial;
•
Si
la operación es UPDATE solo cambiará la condición del WHERE agregando que el
código de identificación no sea el mismo. Ejm: WHERE
A.tx_serial <>'S/S' and A.tx_serial = NEW.tx_serial and
A.co_activo<>NEW.co_activo LIMIT 1;
•
Finalmente
se retorna el registro NEW para garantizar que el registro devuelto siempre
contenga valores. Si se utiliza como valor de retorno la variable registro, se
presentaran problemas de ejecución cuando la consulta no encuentra valor
repetido y la variable contendrá NULL.
Una vez
creada la función encontrarSerial, se procede a crear el disparador:
CREATE TRIGGER triggerEncontrarSerial
BEFORE INSERT
OR UPDATE
ON
activo.activo
FOR EACH ROW
EXECUTE PROCEDURE
activo.encontrarSerial();
Obsérvese
que el disparador se crea para capturar los eventos Insert o Update para cada
fila afectada y antes de ejecutar la operación se realizarán las instrucciones
indicadas en la función encontrarSerial() especificada en el esquema activo.
Con esta
función y disparador se tendrá la seguridad que los valores de la columna no se
repetirán excepto el valor indicado 'S/S'.
Conclusión:
Con la
creación de la función que retorne trigger la cual contendrá el diseño de la
consulta SELECT con las condiciones ajustadas a su necesidad, porque puede
agregar tantas excepciones de valores repetidos como los requiera las reglas de
negocio de su base de datos y la creación del disparador en la tabla para que
se ejecuta la revisión previa a la inserción o modificación del valor de una
fila de la tabla activo.
