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

PostgreSQL postgres trigger disparador logo elefante banner

En esta entrada haremos un trigger que en cada operación de inserción en una tabla especifica de nuestra base de datos PostgreSQL, llame a 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.

Crear Trigger que ejecute una función en PostgreSQL


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

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


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'

Explicación de la función PostgreSQL

  • En las líneas 4, 5, 6 declaramos variables de tipo texto.
  • En la línea 8 validamos 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.

trigger funcion postgres codigo compuesto insertEl 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 codigo 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.

Anuncios

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

  1. CREATE OR REPLACE FUNCTION tgcompras()
    RETURNS trigger AS
    $compras$
    begin
    IF(NEW.cantidad_producto>0) then
    UPDATE inventario set saldo=saldo+new.cantidad_producto where id_inventario=1;
    UPDATE compras_totales set saldo= saldo+new.monto where id_compra_total=1;
    END IF;
    IF(NEW.tipo_de_pago=’efectivo’) then
    INSERT INTO asientos_de_compra (consepto,efectivo,credito,cuenta,fecha) values(new.detalle_producto,new.monto,0,’CAJA’,now());
    update cuentas set saldo=saldo+new.monto where id_cuenta=3;
    UPDATE cuentas set saldo=saldo-new.monto where id_cuenta=1;

    END IF;
    IF (NEW.tipo_de_pago=’credito’) then
    INSERT INTO asientos_de_compra (consepto,efectivo,credito,cuenta,fecha) values(new.detalle_producto,0,new.monto,’CUENTAS POR COBRAR’,now());

    UPDATE cuentas set saldo=saldo-new.monto where id_cuenta=2;
    update cuentas set saldo=saldo+new.monto where id_cuenta=3;
    END IF;
    return new;
    END;

    $compras$ LANGUAGE PLPGSQL;

    create trigger tgcompras after insert or update on compras for each row
    execute procedure tgcompras();

    insert into compras(id_inventario,id_compra_total,id_gasto_de_compra,id_asiento_de_compra,id_cuenta,detalle_producto,marca_producto,cantidad_producto,
    precio_producto,monto,tipo_de_pago,fecha_compra) values
    (1,1,1,1,1,’abc’,’xface’,2,100,200,’efectivo’,now());

  2. –CREAR UN TRIGGER PARA UNA VENTA
    CREATE OR REPLACE FUNCTION tgventas() RETURNS TRIGGER AS $ventas$
    begin
    IF(NEW.cantidad_producto>0) then
    UPDATE inventario set saldo=saldo-new.cantidad_producto where id_inventario=1;
    update inventario set saldo=saldo+new.cantidad_producto where id_inventario=2;
    update ventas_totales set saldo=saldo+new.monto where id_venta_total=1;

    END IF;

    IF(new.tipo_de_pago=’efectivo’) THEN
    insert into asientos_de_venta(consepto,efectivo,cuenta,fecha) values(new.detalle_producto,new.monto,’CAJA’,now());
    UPDATE cuentas set saldo=saldo+new.monto where id_cuenta=4;
    update cuentas set saldo=saldo+new.monto where id_cuenta=6;
    END IF;

    IF(new.tipo_de_pago=’credito’) THEN
    insert into asientos_de_venta(consepto,credito,cuenta,fecha) values(new.detalle_producto,new.monto,’CUENTAS POR COBRAR’,now());
    UPDATE cuentas set saldo=saldo+new.monto where id_cuenta=5;
    update cuentas set saldo=saldo+new.monto where id_cuenta=6;
    END IF;
    RETURN NEW;
    end;
    $ventas$ LANGUAGE PLPGSQL;

    create trigger tgventas after insert or update on ventas for each row
    execute procedure tgventas();

    insert into ventas(id_cliente,id_inventario,id_venta_total,id_gasto_de_venta,id_cuenta,id_asiento_de_venta,
    detalle_producto,marca_producto,cantidad_producto,precio_producto,monto,tipo_de_pago,fecha_venta) values
    (1,1,1,1,1,1,’abc’,’xface’,2,300,600,’efectivo’,now());

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s