Crear trigger y función PL/PGSQL que genere código compuesto al realizar Insert en PostgreSQL

En esta entrada aprenderemos a crear un trigger y función PL/PGSQL para los Insert que se ejecuten en PostgreSQL,

Haremos un trigger que en cada operación de inserción en una tabla especifica de nuestra base de datos PostgreSQL ejecute una función hecha en PL/PGSQL, la cual generara é insertara en un campo de nuestra tabla seleccionada un código compuesto por el año actual, el valor de un campo (siglas de un estado) en una tabla relacionada y un incremental que debe ser único para los parámetros previos.

Es decir el código debe ser como 2015AM0006 2015SU0001 2015AM0007 y se debe comportar de tal manera que cada año se inicialice el valor incremental y dicho valor se incrementa en base al año y siglas de estado.

Funciones y Triggers en PostgreSQL

Como crear un trigger y función PL/PGSQL para Insert en PostgreSQL

Crear Trigger que ejecute una función en PostgreSQL

[sourcecode language="sql"]

CREATE TRIGGER trigger_codigo_compuesto
BEFORE INSERT ON obra
FOR EACH ROW
EXECUTE PROCEDURE crear_codigo_compuesto()

[/sourcecode]

Indica que ejecutara la función crear_codigo_compuesto cada vez que se inserte un registro en la tabla «obra».

Función que genera código compuesto por AÑO ACTUAL+ SIGLA DE ESTADOS + NUMERO INCREMENTAL

[sourcecode language="sql"]

CREATE OR REPLACE FUNCTION crear_codigo_compuesto()
RETURNS trigger AS '
DECLARE
anio text;
siglas_estado text;
incremental text;
BEGIN
IF NEW.campocodigocompuesto IS NULL OR NEW.campocodigocompuesto = '''' THEN
anio = ( SELECT (date_part(''year'', NOW())::text) );
siglas_estado = ( select siglas from estado where id_estado = NEW.fk_estado );
incremental = ( select to_char( ( max(substring(campocodigocompuesto,7,4))::integer + 1), ''FM0999'')
from obra where anio = substring(campocodigocompuesto,0,5) AND substring(campocodigocompuesto,5,2) = siglas_estado );
NEW.campocodigocompuesto := anio || siglas_estado || incremental;
END IF;
RETURN NEW;
END' LANGUAGE 'plpgsql'

[/sourcecode]

Explicación de la función PostgreSQL

• En las líneas 4, 5, 6 declaramos variables de tipo texto.
• La línea 8 valida que el campo «campocodigocompuesto» de la tabla obra sea nulo ó este vacío.
• En la línea 9 extraemos  el año usando date_part de la fecha actual que capturamos usando now() y lo guardamos en la variable anio. Vea las funciones de fecha y hora que tiene postgres http://www.postgresql.org/docs/9.4/static/functions-datetime.html
• En la línea 10 buscamos las siglas en la tabla relacionada «estado».
• Línea 12. Seleccionaremos de la tabla obra aquellos registros cuyo campocodigocompuesto correspondan al año en curso y que correspondan al estado que se pretende insertar al momento de ejecutar el insert que llama a esta función. Para esto usamos la función substring con lo cual extraemos del código el año sabiendo que este se encontrara en los primeros cuatro caracteres del campo y las siglas que se son el sexto y séptimo carácter.
• Línea 11. Teniendo ya todos los registros usaremos la función max que nos retornara el valor máximo de los valores incrementales que obtenemos con la función substring que nos extraerá solamente los valores incrementales del campocodigocompuesto. Hacemos una conversión a tipo integer para adicionar un valor, con lo cual ya tenemos el valor que deberemos insertar y le damos formato de rellenar ceros a la izquierda con la función to_char. El resultado lo guardamos en la variable «incremental»
• En la línea 13 finalmente le asignaremos el valor a nuestro campo objetivo en la inserción (NEW.campocodigocompuesto), para ello concatenamos las variables que hemos trabajado previamente.

El resultado final en pgAdmin. Códigos generados automáticamente al realizar insert sobre la tabla objetivo.

Podemos ver que se probo sobre dos estados Monagas y Amazonas (MO y AM), también que existiendo un registro del mismo estado y igual próximo valor secuencial, se realiza discriminación por el año actual (últimos tres registros).

En pocas lineas realizamos un trabajo de calidad, que garantiza la integridad del código compuesto que la regla del negocio requiere, para ello usamos PL/PGSQL y varias funciones de PostgreSQL, en otro escenario generar e insertar este código desde el aplicativo hubiera resultado tedioso, engorroso e ineficiente.