Taller PL/pgSQL

Introducción

Ya hemos visto que SQL (DML) es un lenguaje simple y poderoso para manipular datos en una base de datos. PL/pgSQL es un lenguaje procedimental que permite crear funciones, procedimientos y triggers con el fin de realizar operaciones y computaciones más complejas dentro de la base de datos. Realizar dichas operaciones dentro de la base de datos, y no en las aplicaciones, puede mejorar el redimiento del sistema puesto que se disminuye la comunicación entre la aplicación y el SGBD.

Antes de iniciar

Para los ejemplos descritos en este taller se requieren las siguientes tablas y datos:

CREATE TABLE CLIENTES (ID SERIAL PRIMARY KEY, 
   CC VARCHAR(10) NOT NULL,
   NOMBRE VARCHAR(50) NOT NULL);

 CREATE TABLE CUENTAS (ID SERIAL PRIMARY KEY,
   SALDO REAL DEFAULT 0,
   CLI_ID INT NOT NULL REFERENCES CLIENTES(ID),
   FECHA_SOBREGIRO DATE,
   INTERESES REAL DEFAULT 0
 );
 INSERT INTO CLIENTES (CC,NOMBRE) VALUES ('11111','CLIENTE 1');
 INSERT INTO CLIENTES (CC,NOMBRE) VALUES ('22222','CLIENTE 2');
 INSERT INTO CLIENTES (CC,NOMBRE) VALUES ('33333','CLIENTE 3');
 INSERT INTO CUENTAS (SALDO, CLI_ID) VALUES ( 5000,1);
 INSERT INTO CUENTAS (SALDO, CLI_ID) VALUES ( -2000,2);
 INSERT INTO CUENTAS (SALDO, CLI_ID) VALUES ( 3000,3);

Primer Ejemplo

La estructura general de un bloque PL/PgSQL es la siguiente:

 [ <<label>> ]
 [ DECLARE
     declarations ]
 BEGIN
     statements
 END [ label ];
 

Por ejemplo, la siguiente función retorna su argumento multiplicado por 3:

  CREATE OR REPLACE FUNCTION MULT_3(x int) 
     returns INT as $$
  begin 
    RETURN x*3; 
  end; $$
LANGUAGE plpgsql;

La función puede ser utilizada en una cláusula SELECT:

select mult_3(4);
 mult_3 
 --------
   12
 (1 row)
 
 

Las variables, en caso de ser necesarias, deben ser declaradas en la cláusula DECLARE:

 CREATE OR REPLACE FUNCTION CONCAT_NOMBRE(NOMBRE VARCHAR, APELLIDO VARCHAR)
   RETURNS VARCHAR AS $$
   DECLARE
     TEMP VARCHAR;
   BEGIN
     TEMP:= NOMBRE || ' ' || APELLIDO;
     RETURN INITCAP(TEMP);
   END;
   $$
   LANGUAGE plpgsql;
   
   

Un ejemplo de uso:

 
 select concat_nombre('carlos','olarte');
 concat_nombre 
 ---------------
 Carlos Olarte
 
    

Ejemplo (Inserción de Datos en una Tabla)

Esta función inserta un registro en la tabla CLIENTES:

 CREATE FUNCTION INS_CLIENTES(VARCHAR, VARCHAR) 
    RETURNS VOID AS $$
      INSERT INTO CLIENTES (CC,NOMBRE) VALUES ($1,$2);
    $$ 
    LANGUAGE SQL;

Un ejemplo de uso:

 SELECT INS_CLIENTES('44444','CLIENTE 4');

Note que en este caso, los argumentos de la función no tienen nombre y se refieren a ellos como $1 y $2 dentro del cuerpo de la función. Además, como solo lanzamos sentencias SQL, el lenguaje de la función es SQL.

Ejemplo (Transfiriendo Dinero entre dos Cuentas)

 
 /* Función que transfiere dinero de una cuenta a otra */
 CREATE FUNCTION TRANSFER (CTA1 CUENTAS.ID%TYPE , CTA2 CUENTAS.ID%TYPE , MONTO CUENTAS.SALDO%TYPE)
  RETURNS CUENTAS.SALDO%TYPE AS $$
  DECLARE
    NUEVO_SALDO CUENTAS.SALDO%TYPE;
  BEGIN
   UPDATE CUENTAS SET SALDO = SALDO - MONTO WHERE ID=CTA1;
   UPDATE CUENTAS SET SALDO = SALDO + MONTO WHERE ID=CTA2;
   SELECT SALDO INTO NUEVO_SALDO FROM CUENTAS WHERE ID=CTA2;
   RETURN NUEVO_SALDO;
  END
  $$ 
  LANGUAGE PLPGSQL;

Ejemplo de uso:

 postgres=# select transfer(3,2,1000);
 transfer 
 ----------
     -1000
(1 row)

En este ejemplo hemos introducido algunos características interesantes del lenguaje:

  • Los tipos de las variables se pueden declarar de acuerdo con los tipos de datos en las tablas como en CTA1 CUENTAS.ID%TYPE (es decir, CT1 es una variable del tipo del campo ID en la tablas CUENTAS).
  • Hemos lanzado sentencias del DML como parte del procedimiento (UPDATE)
  • Utilizamos SELECT … INTO VAR. Cuando un SELECT retorna una UNICA fila, esta se puede almacenar en una variable local.

Es posible realizar algunas validaciones. Por ejemplo, se debe verificar que las dos cuentas existan y que el monto de la segunda sea suficiente para realizar la transferencia.

 /* Versión2: Validacion de los datos de entrada */
 CREATE FUNCTION TRANSFER2 (CTA1 CUENTAS.ID%TYPE, CTA2 CUENTAS.ID%TYPE, MONTO CUENTAS.SALDO%TYPE)
  RETURNS CUENTAS.SALDO%TYPE AS  $$
  DECLARE
    NUEVO_SALDO CUENTAS.SALDO%TYPE;
    SALDO_DISPONIBLE CUENTAS.SALDO%TYPE;
    TEST_EXISTS INT;
  BEGIN
   SELECT COUNT(*) INTO TEST_EXISTS FROM CUENTAS WHERE ID=CTA1;
   IF TEST_EXISTS <> 1 THEN
      RAISE EXCEPTION 'La cuenta de origen % no existe', CTA1;
   ELSE
     SELECT COUNT(*) INTO TEST_EXISTS FROM CUENTAS WHERE ID=CTA2;
     IF TEST_EXISTS <> 1 THEN
      RAISE EXCEPTION 'La cuenta destino % no existe', CTA2;
     ELSE
          SELECT SALDO INTO SALDO_DISPONIBLE FROM CUENTAS WHERE ID = CTA1;
          IF SALDO_DISPONIBLE < MONTO THEN
             RAISE EXCEPTION 'No hay fondos suficientes para la transferencia';
          ELSE
            /* realizando la transferencia */
            UPDATE CUENTAS SET SALDO = SALDO - MONTO WHERE ID=CTA1;
            UPDATE CUENTAS SET SALDO = SALDO + MONTO WHERE ID=CTA2;
            /* Consultando y retornando el nuevo saldo de CTA2 */
            SELECT SALDO INTO NUEVO_SALDO FROM CUENTAS WHERE ID=CTA2;
            RETURN NUEVO_SALDO;
          END IF;
     END IF;
   END IF;
  END
  $$ 
  LANGUAGE PLPGSQL;

Ejemplo (Actualizando los intereses)

Antes de presentar el ejemplo, vamos a adicionar algunas filas a la tabla cuentas:

INSERT INTO CUENTAS (SALDO,CLI_ID, FECHA_SOBREGIRO) VALUES (-3000,3,'2010-01-01'), (-7000,2,'2010-03-01');

La siguiente función actualiza los intereses de mora en las cuentas que están sobregiradas. Los intereses que se cobran son proporcionales al saldo de la cuenta y se utiliza el porcentaje que se pasa como parámetro.

CREATE FUNCTION COBRAR_INTERESES(PORCENTAJE REAL) RETURNS VOID AS $$
BEGIN
  UPDATE CUENTAS 
    SET INTERESES = SALDO * PORCENTAJE * 
       EXTRACT('DAYS'FROM ( NOW()-FECHA_SOBREGIRO))  
       WHERE FECHA_SOBREGIRO IS NOT NULL; 
 END;
 $$ LANGUAGE plpgsql;

Ejemplo de uso:

 bdi00=> SELECT COBRAR_INTERESES(0.01);

Ejemplo (Utilizando Row Types)

Utilizando %ROWTYPE es posible declarar RECORDS del mismo tipo de una tabla. En el siguiente ejemplo, se crean RECORDS del tipo de las tablas CUENTAS y CLIENTES. La función simplemente imprime información relacionada con la cuenta que se pasa como parámetro.

CREATE FUNCTION INF_CUENTA(NUMCUENTA INT) RETURNS VARCHAR AS $$
DECLARE
  RESULTADO VARCHAR;
  cta_row CUENTAS%ROWTYPE; --cta_row es del mismo tipo de la tabla CUENTAS
  cli_row CLIENTES%ROWTYPE;
BEGIN
  -- Puede utilizar SELECT .. INTO cuando el resultado trae una sola fila
  SELECT * INTO CTA_ROW FROM CUENTAS WHERE ID = NUMCUENTA;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'La cuenta % no existe. ', NUMCUENTA;
  ELSE
     SELECT * INTO CLI_ROW FROM CLIENTES WHERE ID = CTA_ROW.CLI_ID;

     RESULTADO := ' La cuenta pertenece a ' || CLI_ROW.Nombre || 
       '. El saldo es ' || CTA_ROW.SALDO;
     RETURN RESULTADO;
  END IF;
END;
$$ LANGUAGE plpgsql;

Ejemplo de uso:

bdi00=> Select INF_CUENTA(1);
                     inf_cuenta                     
----------------------------------------------------
  La cuenta pertenece a CLIENTE 1. El saldo es 5000
(1 row)

Note el uso de IF NOT FOUND THEN … para verificar si el SELECT trajo o no algún resultado. Obviamente la función anterior se hubiera podido escribir de manera mucho más simple ejecutando una única consulta que traiga la información del cliente y de la cuenta:

CREATE FUNCTION INF_CUENTA2(NUMCUENTA INT) RETURNS VARCHAR AS $$
DECLARE
  SALDO_CTA CUENTAS.SALDO%TYPE;
  NOMBRE_CLI VARCHAR;
BEGIN
  SELECT CLI.NOMBRE, CTA.SALDO INTO NOMBRE_CLI,SALDO_CTA FROM CUENTAS CTA
    INNER JOIN CLIENTES CLI ON (CLI.ID = CTA.CLI_ID)
  WHERE ID = NUMCUENTA;
  
  IF NOT FOUND THEN
    RAISE EXCEPTION 'La cuenta % no existe. ', NUMCUENTA;
  ELSE
     RETURN ' La cuenta pertenece a ' || NOMBRE_CLI || 
       '. El saldo es ' || SALDO_CTA;
     RETURN RESULTADO;
  END IF;
END;
$$ LANGUAGE plpgsql;

En este caso, note que la cláusula SELECT … INTO puede asignar varias variables al tiempo.

Cursores

Un cursor es una estructura que permite recuperar los datos de una consulta fila por fila.

Asuma una tabla con una serie de transacciones bancarias “pendientes”, i.e., transacciones que deben ser realizadas y afectar las cuentas de los clientes:

CREATE TABLE PENDIENTES (
  ID SERIAL PRIMARY KEY,
  MONTO REAL NOT NULL,
  CTA_ID INT NOT NULL REFERENCES CUENTAS(ID),
  FECHA DATE DEFAULT NOW(),
  OPERACION CHAR(1),
  REALIZADA BOOL DEFAULT FALSE);
  INSERT INTO PENDIENTES (MONTO,CTA_ID,OPERACION)  VALUES 
   (300,1,'D'), (400,2,'R'), (233,3,'D');

El siguiente procedimiento “recorre” la tabla pendientes y realiza la operación indicada en la tabla CUENTAS.

 CREATE OR REPLACE FUNCTION UPD_PENDIENTES() RETURNS VOID AS $$
 DECLARE
   -- Declaración del Cursor
   -- Note la forma del Select ... FOR UPDATE! 
   CUR_PEN  CURSOR FOR SELECT * FROM PENDIENTES WHERE REALIZADA=FALSE FOR UPDATE;
   ACCION PENDIENTES%ROWTYPE;
 BEGIN
   -- Abrir el cursor
   OPEN CUR_PEN;
   LOOP
     -- Extraer una fila del cursor
     FETCH CUR_PEN INTO ACCION;
     IF ACCION IS NULL THEN EXIT;
     END IF;
  
   IF ACCION.OPERACION = 'D' THEN
     UPDATE CUENTAS SET SALDO = SALDO + ACCION.MONTO WHERE ID = ACCION.CTA_ID;
   ELSE
     UPDATE CUENTAS SET SALDO = SALDO - ACCION.MONTO WHERE ID = ACCION.CTA_ID;
   END IF;
      
      -- Actualizar una fila de acuerdo con la posicion del cursor
      UPDATE PENDIENTES SET REALIZADA=TRUE WHERE CURRENT OF  CUR_PEN;
   END LOOP;
-- Cierre del cursor. 
CLOSE CUR_PEN;
END;
$$ LANGUAGE plpgsql;

La cláusula FOR UPDATE evita que otras transacciones estén modificando las filas seleccionadas.

Ejemplo de uso:

bdi00=> select * from CUENTAS;
 id | saldo | cli_id | fecha_sobregiro | intereses 
----+-------+--------+-----------------+-----------
  1 |  5000 |      1 |                 |         0
  2 | -2000 |      2 |                 |         0
  3 |  3000 |      3 |                 |         0
(3 rows)
bdi00=> select * from PENDIENTES;
 id | monto | cta_id |   fecha    | operacion | realizada 
----+-------+--------+------------+-----------+-----------
  1 |   300 |      1 | 2010-04-26 | D         | f
  2 |   400 |      2 | 2010-04-26 | R         | f
  3 |   233 |      3 | 2010-04-26 | D         | f
(3 rows)
bdi00=> SELECT UPD_PENDIENTES();
 upd_pendientes 
----------------

(1 row)

bdi00=> select * from CUENTAS;
 id | saldo | cli_id | fecha_sobregiro | intereses 
 ----+-------+--------+-----------------+-----------
   1 |  5300 |      1 |                 |         0
   2 | -2400 |      2 |                 |         0
   3 |  3233 |      3 |                 |         0
bdi00=> select * from PENDIENTES;
 id | monto | cta_id |   fecha    | operacion | realizada 
----+-------+--------+------------+-----------+-----------
  1 |   300 |      1 | 2010-04-26 | D         | t
  2 |   400 |      2 | 2010-04-26 | R         | t
  3 |   233 |      3 | 2010-04-26 | D         | t
(3 rows)

Otra forma de recorrer tablas

Otra forma de utilizar cursores “implícitamente” es utilizando FOR <VAR> IN <QUERY>. Por ejemplo, asuma que se tiene una tabla con la lista de usuarios que han efectuado mal uso de sus cuentas y deben ser desactivadas:

CREATE TABLE USUARIOS_FRAUDE(ID SERIAL, CLI_ID INT);
INSERT INTO USUARIOS_FRAUDE (CLI_ID) VALUES (1), (3);

Ahora adicionamos un atributo en cuenta para saber si está activa o no:

ALTER TABLE CUENTAS ADD INACTIVA BOOL DEFAULT FALSE;

La siguiente función inhabilita las cuentas cuyo usuario se encuentra en la tabla de USUARIOS_FRAUDE

CREATE OR REPLACE FUNCTiON INACTIVA () RETURNS VOID AS $$
DECLARE
   USR USUARIOS_FRAUDE%ROWTYPE;
BEGIN
  FOR USR IN SELECT * FROM USUARIOS_FRAUDE LOOP
    UPDATE CUENTAS SET INACTIVA = TRUE WHERE CLI_ID = USR.CLI_ID;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Para este caso particular, un lector atento hubiera podido descubrir que el ejercicio se podía realizar con una sola sentencia del DML:

 UPDATE CUENTAS SET INACTIVA = FALSE WHERE CLI_ID IN (SELECT CLI_ID FROM USUARIOS_FRAUDE);

Triggers

Un trigger es una acción que se lanza cuando se inserta, elimina o actualiza una o varios registros de una tabla. En PL/pgSQL, los triggers son funciones sin argumentos en las cuales se crean las siguientes variables:

  • NEW: Es un Record con los datos del registro que se está insertando (actualizando).
  • OLD: Datos del registro que se está eliminando (o actualizando).
  • TG_OP: Operación que se está realizando, puede ser INSERT, UPDATE, DELETE, o TRUNCATE.

Un ejemplo de Auditoria

Suponga que cada que se modifica una cuenta, se debe registrar la acción en la siguiente tabla:

CREATE TABLE AUDITORIA(
 OPERACION CHAR(1), 
 FECHAUPD TIMESTAMP,
 USUARIO TEXT,
 ID INT,
 SALDO REAL,
 CLI_ID INT,
 FECHA_SOBREGIRO DATE,
 INTERESES REAL,
  INACTIVA BOOL);

La siguiente función deja un rastro en la tabla AUDITORIA cada que se realiza una modificación en la tabla CUENTAS:

CREATE OR REPLACE FUNCTION process_cue_audit() RETURNS TRIGGER AS $$
  BEGIN
      IF (TG_OP = 'DELETE') THEN
          INSERT INTO AUDITORIA SELECT 'D', now(), user, OLD.*;
          RETURN OLD;
      ELSIF (TG_OP = 'UPDATE') THEN
          INSERT INTO AUDITORIA SELECT 'U', now(), user, NEW.*;
          RETURN NEW;
      ELSIF (TG_OP = 'INSERT') THEN
          INSERT INTO AUDITORIA SELECT 'I', now(), user, NEW.*;
          RETURN NEW;
      END IF;
  END;
$$ LANGUAGE plpgsql;

Ahora se crea el trigger sobre la tabla CUENTAS que se activa cada que se inserta, actualiza o elimina una (o varias) fila(s) en CUENTAS:

CREATE TRIGGER cue_audit
  AFTER INSERT OR UPDATE OR DELETE ON CUENTAS
      FOR EACH ROW EXECUTE PROCEDURE process_cue_audit();

Algunos ejemplos:

bdi00=> insert into cuentas (saldo,cli_id) values (3500,2);
INSERT 0 1
bdi00=> select * from auditoria;
 operacion |          fechaupd          | usuario | id | saldo | cli_id | fecha_sobregiro | intereses 
-----------+----------------------------+---------+----+-------+--------+-----------------+-----------
 I         | 2010-04-26 10:30:18.081372 | bdi00   |  6 |  3500 |      2 |                 |         0
(1 row)
bdi00=> update cuentas set fecha_sobregiro = now() where id =1;
UPDATE 1
bdi00=> select * from auditoria;
 operacion |          fechaupd          | usuario | id | saldo | cli_id | fecha_sobregiro | intereses 
-----------+----------------------------+---------+----+-------+--------+-----------------+-----------
 I         | 2010-04-26 10:30:18.081372 | bdi00   |  6 |  3500 |      2 |                 |         0
 U         | 2010-04-26 10:31:23.2547   | bdi00   |  1 |  4067 |      1 | 2010-04-26      |         0
(2 rows)

Un ejemplo de una Validación

Los triggers son muy útiles cuando se requieren hacer validaciones que que no se pueden realizar con las restricciones de integridad referencial (PK, FK, UK,CK). Por ejemplo, asume que un cliente por política del banco, no puede tener más de 3 cuentas activas. Cada que se inserta una nueva cuenta, o se actualiza el titular de la cuenta, se debe verificar que no se incumple esta propiedad. Primero implementamos la función:

 CREATE OR REPLACE FUNCTION FUN_CK_NUM_CLIENTES() RETURNS TRIGGER AS $$
   DECLARE
    NUM_CUENTAS INT;
   BEGIN
       IF (TG_OP = 'UPDATE' AND OLD.CLI_ID <> NEW.CLI_ID ) OR TG_OP='INSERT'  THEN
       	 SELECT COUNT(*) INTO NUM_CUENTAS FROM CUENTAS
               WHERE CLI_ID = NEW.CLI_ID;
          IF NUM_CUENTAS >= 3 THEN
            RAISE EXCEPTION 'LIMITE DEL NUMERO DE CUENTAS ALCANZADO POR EL CLIENTE';
          ELSE
            RETURN NEW;
          END IF;
       ELSE
         RETURN NEW;
      END IF;
 END;
 $$ LANGUAGE plpgsql;
  /* Creacion del Trigger */
 CREATE TRIGGER TRG_NUM_CLIENTES
    AFTER INSERT OR UPDATE ON CUENTAS
    FOR EACH ROW EXECUTE PROCEDURE FUN_CK_NUM_CLIENTES();
    
    

Valores Calculados

Los triggers también pueden ser utilizados para mantener valores calculados. Por ejemplo, asumamos que en la tabla CLIENTES se desea mantener el acumulado en dinero que tiene el cliente en sus distintas cuentas. Para esto, modificamos la tabla:

 ALTER TABLE CLIENTES ADD COLUMN SALDO REAL DEFAULT 0.0;

Como no habíamos implementado el trigger que se encargara de llevar este saldo, por la primera vez lo calculamos “manualmente”:

CREATE OR REPLACE FUNCTION UPDATE_SALDOS() RETURNS VOID AS $$

 DECLARE
    CLIENTE_SALDO REAL;
    CLIENTE_ID REAL;
 BEGIN
    FOR CLIENTE_ID, CLIENTE_SALDO  IN SELECT CLI_ID, SUM(SALDO) FROM CUENTAS GROUP BY CLI_ID LOOP
      UPDATE CLIENTES SET SALDO=CLIENTE_SALDO WHERE ID = CLIENTE_ID;
    END LOOP;
    RETURN;
 END;$$
 LANGUAGE plpgsql;

Ahora, por cada modificación del saldo en una cuenta, actualizamos el saldo en el cliente:

CREATE OR REPLACE FUNCTION SET_SALDO_CLIENTE() RETURNS TRIGGER AS $$
BEGIN
  IF(TG_OP = 'INSERT') THEN
    UPDATE CLIENTES SET SALDO = SALDO + NEW.SALDO WHERE ID = NEW.CLI_ID;
    RETURN NEW;
  ELSIF (TG_OP = 'UPDATE') THEN
    IF NEW.CLI_ID = OLD.CLI_ID THEN
      UPDATE CLIENTES SET SALDO = SALDO + NEW.SALDO - OLD.SALDO WHERE ID = NEW.CLI_ID;
      RETURN NEW;
    ELSE /* LA CUENTA CAMBIO DE TITULAR */
     UPDATE CLIENTES SET SALDO = SALDO - OLD.SALDO WHERE ID = OLD.CLI_ID;
     UPDATE CLIENTES SET SALDO = SALDO + NEW.SALDO WHERE ID = NEW.CLI_ID;
     RETURN NEW;
    END IF;
 END IF;
 RETURN NEW;
END;
$$  LANGUAGE plpgsql;
 /* Creacion del Trigger */
 CREATE TRIGGER TRG_SALDO_CLIENTES
    AFTER INSERT OR UPDATE ON CUENTAS
    FOR EACH ROW EXECUTE PROCEDURE SET_SALDO_CLIENTE();

Realice algunas operaciones sobre la tabla CUENTAS (cambiando el titular y/o el saldo) para verificar que la cuenta se lleva correctamente.

Manejo de Errores

Es posible lanzar y capturar errores de la siguiente manera:

CREATE OR REPLACE FUNCTION TEST(int, int) RETURNS INT AS $$
DECLARE 
  X INT;
BEGIN
  X := $1/$2;
  RETURN X;
EXCEPTION
    WHEN division_by_zero THEN
     RAISE EXCEPTION 'Division por cero';
     RETURN 0;
    WHEN others THEN
      RAISE NOTICE 'Error';
      RETURN 0;
END;
$$ LANGUAGE plpgsql;

Ejemplo de uso:

bdi00=> select test(6,2);
 test 
------
    3
(1 row)
bdi00=> select test(6,0);
NOTICE:  Division por cero
 test 
------
    0
(1 row)

Red Social

Asuma el siguiente esquema de bases de datos:

 usuarios(*id,nombre,email, num_amigos)
 amigos(*id_usr1, *id_usr2)
 invitaciones(*id, fecha, *id_usr1, *id_usr2, mensaje, estado)

Cuando un usuario quiere ser amigo de otro, debe enviar una invitación. Los estados de la invitación pueden ser “pendiente”, “aceptado”, “rechazado”.

  1. Cree el script de tablas
  2. Implemente un procedimiento que cada que se acepte una invitación, se adicione la la relación de amistad en la tabla amigos.
  3. Por cada nuevo amigo se debe actualizar el número de amigos en el campo USUARIOS.NUM_AMIGOS
  4. La relación de amigos se asume que es simétrica, es decir, si A es amigo de B entonces B es amigo de A. Realice un trigger que evite la inserción de (A,B) en la tabla AMIGOS si la tuple (B,A) ya se encuentra. Además, se deben rechazar invitaciones de A,B si A,B ya son amigos (o B,A ya son amigos)
  5. Adicione un trigger que rechace nuevas invitaciones de A a B si B ha rechazado previamente la invitación.

Solucion

Ejercicio

Asuma el siguiente esquema de bases de datos:

cargos(id,nombre,salario);
empleados(id,cc,nombre,car_id);
deducciones(id,nombre, ran_inf,ran_sup, porcentaje);
nomina(id,fecha, emp_id,basico, neto);
novedades(id,observacion,fecha,monto, emp_ID, nom_id);
descuentos(ded_id,nom_id,valor);

Se asume que cada empleado tiene un único cargo y por cada cargo se conoce el salario básico.

La tabla deducciones tiene la información de los impuestos/deducciones que se deben cobrar a cada empleado si su salario se encuentra en el rango ran_inf - ran_sup.

La tabla novedades tiene la información de las pagos adicionales que se deben realizar a los empleados. El atributo nom_id es nulo al principio. Una vez se genera la nomina y la novedad es tenida en cuenta, el valor de este campo debe ser el id de la nomina donde se adicionó la novedad.

La tabla nomina debe tener el salario básico del empleado así como el neto (después de deducciones y novedades).

La tabla descuentos es el detalle de las deducciones realizadas a cada empleado en el periodo actual.

Realice un procedimiento que calcule la nómina con sus deducciones y novedades. Más precisamente, se debe:

  • Crear los registros en la tabla nomina para cada uno de los empleados.
  • Adicionar en la tabla descuentos las deducciones para cada empleado.
  • Calcular el salario neto teniendo en cuento las deducciones y las novedades.
  • Actualizar el campo nom_id en novedades cuando la novedad se tenga en cuenta en la nomina.

Se debe entregar los scripts para:

  • Creación de tablas
  • Creación de restricciones de integridad
  • Inserción de datos de prueba
  • Funciones/triggers necesarios para el cálculo de la nomina
  • Ejemplos de ejecución de las funciones creadas.

Más información en el Manual de Postgres.

 
materias/taller_pl_pgsql.txt · Última modificación: 2012/10/16 14:24 por caolarte
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki