7. Programando el SGBD

En este tema aprenderemos a:

  1. Ver qué alternativas ofrece el SGBD para programar en el SGBD

  2. Entender el uso de funciones, procedimientos y triggers

  3. Implementar funciones, procedimientos y triggers en un nivel básico

Esfuerzo Necesario

El curso está organizado en 8 sesiones de clase. Cada clase (sesión) implica una dedicación de entre 2 y 4 horas.

La dedicación depende del conocimiento previo, motivación y capacidad de aprendizaje del estudiante para esa sesión en concreto.

Video Clase

7.1. Programando el SGBD

Video Clase

  1. Un SGBD podría considerarse también una plataforma de desarrollo (SDK) que permite:

    1. Extender la funcionalidad programando el comportamiento del servidor (pero implica crear una capa extra de complejidad)

    2. Aunque el estándar SQL lo incluye, en la práctica es muy dependiente del SG (genera incompatibilidad)

    3. En el caso de Postgres es muy complejo, potente y una de las razones para usarlo como SG (server-programming)

  2. En la práctica:

    1. Muy potente si orientado al procesado de datos

    2. Ojo a la incompatibilidad y la arquitectura de la aplicación (mezcla lógica y datos)

    3. Si la complejidad del caso de uso está en los datos mejor programar el SGBD (definir esa capa extra)

    4. Más utilizado en aplicaciones empresariales que en aplicaciones web (aunque depende de la aplicación en sí).

  3. Lo que buscamos es definir un comportamiento personalizado a nuestro caso de uso. Podemos definir:

    1. Tipos de datos (sql-createtype) y dominios (sql-createdomain)

    2. Operadores (sql-createoperator)

    3. Programas (Rutinas):

      1. Funciones (sql-createfunction)

      2. o procedimientos (sql-createprocedure)

    4. y … mucho más (server-programming)

  4. Los procedimientos almacenados (stored procedures) son un caso particular de uso de funciones pero:

    1. Usan CREATE PROCEDURE (en vez de CREATE FUNCTION)

    2. No devuelven un valor (bueno, pueden hacerlo con el truco de parámetros de salida)

    3. Se llaman usando CALL (como instrucción independiente, no dentro de una consulta SQL)

    4. Pueden gestionar transacciones (sólo usando PL/pgSQL)

  5. También podemos definir comportamientos frente a eventos (triggers)

    1. Asociamos una función especial (trigger function) a una acción sobre la BD. Cada vez que ocurre esa acción o evento, se ejecuta (dispara) la función asociada

    2. Se pueden definir sobre tablas o vistas

    3. Cuando ocurren eventos DML (Insert / Update / Delete)

    4. Antes (BEFORE), Después (AFTER) o en vez de (INSTEAD) de la ejecución de la instrucción DML

Distinguir los diferentes elementos de programación en Postgres

Deberías ser capaz de distinguir de qué tipo de elemento de programación estás viendo el código, y sus elementos principales:

  1. Tipo de objeto

  2. Nombre

  3. Parámetros de entrada

  4. Parámetros de salida

  5. Cuerpo de la rutina y una intuición de lo que hace

    Dominio

    -- Creas el tipo de datos us_postal_code
    -- según un patrón concreto
    
    CREATE DOMAIN us_postal_code AS TEXT
         CHECK(
            VALUE ~ '^\d{5}$'
            OR VALUE ~ '^\d{5}-\d{4}$'
    );
    
    CREATE TABLE us_snail_addy (
            address_id SERIAL PRIMARY KEY,
            street1 TEXT NOT NULL,
            street2 TEXT,
            street3 TEXT,
            city TEXT NOT NULL,
            postal us_postal_code NOT NULL
    );
    

    Funciones

    -- Creas una funcion que suma dos enteros
    
    CREATE FUNCTION add(integer, integer) RETURNS integer
         AS 'select $1 + $2;'
         LANGUAGE SQL
         IMMUTABLE
         RETURNS NULL ON NULL INPUT;
    

    Procedimiento Almacenado

    -- Creas un procedimiento que inserta los valores
    -- que le pasas por entrada en la tabla tbl
    
    CREATE PROCEDURE insert_data(a integer, b integer)
    LANGUAGE SQL
    AS $$
            INSERT INTO tbl VALUES (a);
            INSERT INTO tbl VALUES (b);
    $$;
    

    Operador

    -- complex es un tipo de datos ya creado
    -- complex_add es una función ya creada
    
    CREATE OPERATOR + (
          leftarg = complex,
          rightarg = complex,
          function = complex_add,
          commutator = +
    );
    
    SELECT (a + b) AS c FROM test_complex;
    

    Triggers

    -- Creas un trigger
    -- que ejecuatará la función check_account_update
    -- antes, y por cada fila,
    -- que se actualize
    -- de la tabla accounts
    
    CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION check_account_update();
    

7.2. Uso de Funciones en Postgres

Video Clase

  1. PG usa cuatro tipos diferentes de funciones (xfunc)

    1. De consulta (query language functions). Las funciones están escritas en SQL y quizá las más sencillas de implementar.

    2. De programación (procedural language functions). Las funciones están descritas en un lenguaje de programación específico (PL/SQL u otros)

    3. Nativas (usando C):

      1. Internas (Estáticas). Las funciones estás escritas en C y cargadas en el inicio de forma estática (siempre están)

      2. Dinámicas. Las funciones se cargan como módulos de forma dinámica (se usan bajo demanda)

  2. Usa los conceptos de programación modular (y estructurada):

    1. Usa (o no) parámetros de entrada y salida con fuerte tipado de datos

    2. Tiene un cuerpo de código donde está la programación, usando:

      1. Paradigma declarativo: SQL

      2. Paradigma imperativo: p.ej en PL/SQL (usando Procedural Languages)

  3. Son muy fáciles de utilizar

    1. Llamas a las función, con sus parámetros, en la instruccion SQL

    2. Devuelven un valor (value expression)

      -- Sin parámetros
      SELECT la_funcion();
      
      -- Con parámetros
      SELECT la_funcion(parametro1, parametro2);
      
    3. El valor puede ser cualquier tipo de datos SQL (desde lo más simple a uno más complejo)

  4. Funciones en Postgres (ya desarrolladas). Es una de las grandes ventajas de postgres (incluidas en el catálogo):

    1. Te ofrece una biblioteca (library) de funciones internas muy grande (y las posibilidades de extenderla)

    2. Ves las funciones que puedes usar:

      1. Definidas por el usuario en esa base de datos (en ese schema): \df

      2. Todas (están en pg_catalog), en la tabla pg_proc (catalog-pg-proc). Por ejemplo, las que tienen «round» en el nombre:

        SELECT
          proname AS functionname,
          prosrc AS source,
          nspname AS schema
          FROM
             pg_proc
          INNER JOIN pg_namespace ON (pg_proc.pronamespace = pg_namespace.oid)
          WHERE
            prosrc ~* 'round'; -- Usa expresión regular, pero puedes usar like u otro operador
        
      3. Ver los datos de una función específica (nativa o definida por el usuario): \df la_funcion

    3. Hay muchas (pero muchas) funciones en Postgres, por ejemplo:

      1. Matemáticas (functions-math)

      2. De agregación (functions-aggregate)

      3. Gestión de secuencias (functions-sequence)

      4. Del sistema:

        1. De información (functions-info)

        2. De administración (functions-admin)

      5. De fechas y cadenas de texto

      6. y … muchas más (functions)

Usando funciones matemáticas, de agregación y de sistema

Haz los ejercicios en Usando Funciones en Postgres

7.3. Usando funciones con fechas y texto

Video Clase

  1. Formato Fecha/Hora:

    1. Muy habitual (y útil)

    2. Si es texto almacenar siempre en formato ISO 8601 y luego lo muestras como quieras

      Fecha  2023-04-20
      Fecha y hora en UTC
                     2023-04-20T18:28:33+00:00
                     2023-04-20T18:28:33Z
                     20230420T182833Z
      Semana 2023-W17
      Fecha con número de semana
                     2023-W17-2
      Fecha sin año
                     --04-251​
      Fecha ordinal
                     2023-115
      timestamp
                     1997-12-17 07:37:16-08
      
    3. Tipos de datos específicos en el SG (siempre ISO 8601). Repasa por ejemplo los de Postgres (datatype-datetime)

      timestamp with time zone    -- 1999-01-08 04:05:06 -8:00
      date  -- 1999-01-08
      time without time zone -- 04:05:06.789
      interval
      
  2. Funciones y Operadores de Fechas (functions-datetime)

    1. Muchísimas alternativas de manipulación

    2. Hay estos operadores , por ejemplo:

      date '2001-09-28' + 7           -- 2001-10-05
      date '2001-09-28' + interval  '1 hour' -- 2001-09-28 01:00:00
      date '2001-10-01' - date '2001-09-28'  -- 3
      time '05:00' - interval '2 hours' -- 03:00:00
      timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00' -- 63 days 15:00:00
      
    3. Hay estas funciones , por ejemplo:

      age(timestamp '2001-04-10', timestamp '1957-06-13') --  43 years 9 mons 27 days
      age(timestamp '1957-06-13') --  62 years 6 mons 10 days
      current_date --  2019-12-23
      current_timestamp --  2019-12-23 14:39:53.662522-05
      date_part('month', interval '2 years 3 months') --  3
      date_trunc('hour', timestamp '2001-02-16 20:38:40') --  2001-02-16 20:00:00
      
  3. Dar formato a las Fechas (functions-formatting)

    1. Sirven para convertir (formatear) texto a fechas (y viceversa)

    2. Muy flexibles, y útiles (si está bien almacenado el valor)

    3. Siempre el mismo patrón: valor a formatear, plantilla para usar en el formato (sea de entrada o salida)

    4. Hay muchas , por ejemplo:

      to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS') --  05:31:12
      to_char(interval '15h 2m 12s', 'HH24:MI:SS') --  15:02:12
      to_char(125.8::real, '999D9') --  125.8
      to_date('05 Dec 2000', 'DD Mon YYYY') --  2000-12-05
      to_number('12,454.8-', '99G999D9S') -- -12454.8
      
    5. Hay que revisar el tipo de formatos y patrones (para texto, números y fechas) para definir la conversión

  4. Dar formato a cadenas de texto (functions-string)

    1. Reciben una cadena de texto (varchar) y algún parámetro. Devuelven texto

    2. Hay muchas también, por ejemplo

      'Value: ' || 42 --  Value: 42
      char_length('josé') --  4
      position('om' in 'Thomas') --  3
      substring('Thomas' from '...$') --  mas
      trim(both 'xyz' from 'yxTomxx') --  Tom
      upper('tom') --  TOM
      concat('abcde', 2, NULL, 22) --  abcde222
      format('Hello %s, %1$s', 'World') --  Hello World, World
      left('abcde', 2) --  ab
      length('jose') --  4
      

Usando funciones de fecha y texto

Haz los ejercicios en Usando Funciones en Postgres

7.4. Creando Funciones (con SQL)

Video Clase

  1. Funciones Condicionales (functions-conditional):

    1. Permiten modificar el flujo de ejecución (como un if/then/else)

    2. Dos muy útiles

      1. CASE : devuelve un resultado según una condición

        -- Si condition = t => result
        
        CASE WHEN condition THEN result
        [WHEN ...]
        [ELSE result]
        END
        
        -- Si test es una tabla sencilla con números
        -- Añade una columna con valores texto
        
        SELECT a,
        CASE WHEN a=1 THEN 'uno'
        WHEN a=2 THEN 'dos'
        ELSE 'y más'
        END
        FROM test;
        
      2. COALESCE : devuelve el primer resultado no nulo

        -- Devuelve campo1, campo2 o '(nada)' si, hay algún nulo (por orden)
        
        SELECT COALESCE(campo1, campo2, '(nada)') FROM ...
        
  2. Las funciones se crean con CREATE FUNCTION (sql-createfunction)

    1. Puede ser muy sencilla o muy compleja

    2. En SQL o lenguajes procedimentales (veremos PL/SQL)

    3. Los componentes son siempre los mismos:

      1. Declaración de la función: nombre y argumentos (posicionales o parámetros)

      2. Valor de retorno

      3. Cuerpo de la función

  3. Funciones Postgres usando SQL (xfunc-sql)

    1. Directamente como una expresión (usando RETURN retype AS definition)

    2. Usando un bloque de código (hay que usar delimitadores $$ o literales)

    3. Se pueden devolver valores o tablas (RETURN table(columns)).

    4. La última instrucción (lenguaje DML) debe devolver un valor (o una tabla)

      CREATE FUNCTION one()
      RETURNS integer AS
      $$ -- Quizá más útil usar estos delimitadores
      SELECT 1 AS result;
      $$
      LANGUAGE SQL;
      
      -- Alternative syntax for string literal:
      CREATE FUNCTION one()
      RETURNS integer AS '
      SELECT 1 AS result;
      ' LANGUAGE SQL;
      
      SELECT one();
      
      CREATE FUNCTION add_em(x integer, y integer)
      RETURNS integer AS
      $$
      SELECT x + y;
      $$
      LANGUAGE SQL;
      
      SELECT add_em(1, 2) AS answer;
      
      CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS
      $$
      UPDATE bank
      SET balance = balance - debit
      WHERE accountno = tf1.accountno;
      SELECT 1;
      $$
      LANGUAGE SQL;
      
  4. Se pueden usar argumentos de entrada (con IN) y salida (con OUT) directamente en la lista de parámetros (según la complejidad de la función, puede ser más claro).

Crear una función usando SQL

  1. Repasa la sintaxis de CREATE FUNCTION para entender el código descrito en la clase

  2. Crea una función que sume los dos primeros argumentos, y lo multiplique por un tercero

  3. Crea una función (contador) que cuente el número de registros de una tabla (una tabla concreta de tu BD)

  4. Crea una función (borrar) que borre todos los registros de una tabla (una tabla concreta de tu BD)

  5. En una tabla con dos campos, int y varchar(20), crear una función que inserte un registro con los dos valores que le pasas por campo. Es decir, algo como insertar(1,”uno”) o insertar(2,”otro”). Insertará ese valor y devolverá el número de registros que hay en la tabla.

7.5. Procedimientos Almacenados

Video Clase

  1. Recuerda que son muy similares a las funciones (pero con diferencias importantes)

  2. Se crean usando CREATE PROCEDURE (sql-createprocedure)

    -- Similar a la creación de funciones (sin el RETURN)
    -- El bloque es del tipo "definition"
    
    CREATE PROCEDURE insert_data(a integer, b integer)
    LANGUAGE SQL
    AS $$
        INSERT INTO tbl VALUES (a);
        INSERT INTO tbl VALUES (b);
    $$;
    
  3. Se ejecutan con la instrucción CALL (sql-call)

    -- Inserta datos en dos instrucciones diferentes
    -- Se podría hacer directamente, sólo es para entender la sintaxis
    -- create table ejemplo(n integer, s varchar(20));
    
    CREATE OR REPLACE PROCEDURE informa_insert(a integer)
    LANGUAGE SQL
    AS
    $$
       INSERT INTO ejemplo(n) VALUES (a);
       UPDATE ejemplo SET s = concat('Y ahora ',a) WHERE n = a;
    $$ ;
    
    
    CALL informa_insert(5);
    CALL informa_insert(8);
    

Implementar Procedimientos Almacenados

Crea procedimientos almacenados en la BD Hubway: Ejercicios con la BD Hubway

7.6. Transacciones

Video Clase

  1. El concepto de transacción :

    1. Grupo de instrucciones …

    2. … que se ejecutan (commit) de forma completa …

    3. … o se anulan (rollback) bajo ciertas condiciones

    4. Es decir: o todas las instrucciones, o ninguna

    iniciar transacción (lista de recursos a bloquear)
    ejecución de las operaciones individuales.
    if (todo_ok) {
      ''aplicar_cambios'' (commit)
    }
    else {
      ''cancelar_cambios'' (rollback)
    }
    
  2. Un sistema transaccional cumple con las propiedades ACID

    1. Atomicity

    2. Consistency

    3. Isolation

    4. Durability

  3. Ejemplo habitual de transacción:

    1. Transacción: enviar dinero de una cuenta bancaria a otra

    2. La concurrencia: no tan fácil cuando varios usuarios acceden a la vez. Las transacciones solucionan (casi siempre) el problema

  4. Transacciones en Postgres (tutorial-transactions):

    1. Modo predeterminado: auto commit (cada instrucción es una transacción)

    2. O decides cuando ejecutarlo (transacción a medida):

      1. Comienza la transacción: sql-begin (START TRANSACTION en SQL estándar)

      2. Se confirma: sql-commit

      3. Se anula: sql-rollback

    -- TRANSACTION es opcional (pero recomiendo)
    
    BEGIN TRANSACTION;
    
    UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
    UPDATE branches SET balance = balance - 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
    UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
    UPDATE branches SET balance = balance + 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
    
    COMMIT; -- Hasta este momento no se confirman todas las  instrucciones
    
    -- Podríamos incluir ROLLBACK en alguna parte del proceso       controlando
    -- algún tipo de situación (por ejemplo que Alice tenga  saldo)
    
  5. Anulación total, o parcial, de una transacción:

    1. ROLLBACK (sql-rollback) anula la transacción

    2. Pero se pueden utilizar «puntos intermedios» (savepoints) usando sql-rollback-to

    BEGIN;
    UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
    SAVEPOINT my_savepoint;
    UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
    
    -- Supongamos que el traspaso es a la cuenta de Wally, no Bob
    
    ROLLBACK TO my_savepoint;
    UPDATE accounts SET balance = balance + 100.00
    
    WHERE name = 'Wally';
    COMMIT;
    
  6. Postgres tiene diferentes sistema de control del bloqueo, para asegurar la concurrencia (que garantiza el soporte transaccional).

Creando Transacciones

Crea una transacción que simule la inserción en la tabla ejemplo(campo1, campo2) en dos fases, es decir, primero un campo y luego otro.

Solución
BEGIN; -- Deshabilita autocommit
INSERT INTO ejemplo(campo1) values(valor1);
INSERT INTO ejemplo(campo2) values(valor2);
COMMIT;

7.7. Usando PL/pgSQL

Video Clase

  1. Es un lenguaje de programación imperativo, que se ejecuta en Postgres

    1. Ejecuta SQL peeeeero usando un paradigma imperativo («paso a paso»)

    2. Declaras, y usas, variables, con los tipos de datos SQL (básicos y complejos, si lo necesitas)

    3. Tiene estructuras de control y estructuras de repetición

    4. Lo usas para crear funciones y procedimientos

    5. Se ejecuta en el servidor (tiene varias ventajas)

    6. Es muy similar a PL/SQL (Oracle)

  2. Estructura de un «programa PL/pgSQL» (plpgsql-structure):

    1. Se crea la función, especificando el lenguaje (plpgsql):

      CREATE FUNCTION somefunc(integer, text) RETURNS integer
      AS 'function body text'
      LANGUAGE plpgsql;
      
    2. Se «implementa» el programa (function body text):

      1. Puedes usar comillas o, mejor, $$ para delimitar el cuerpo de la función

      2. Usas características del lenguaje y sentencias SQL (es una mezcla de los dos)

      3. El código va en bloques estructurados (uno, o anidados):

        1. En un contexto (label)

        2. Declaración de variables (DECLARE)

        3. Instrucciones (en bloque BEGIN .. END).

        [ <<label>> ]
        [ DECLARE
        declarations ]
        BEGIN
        statements
        END [ label ];
        
      4. Un ejemplo:

        CREATE OR REPLACE FUNCTION llamada_no_interesa(integer)
        RETURNS integer
        AS
        $$
        
        DECLARE
        _llamada_id ALIAS FOR $1;
        _contacto_id integer;
        
        BEGIN
        
        -- _llamada_id es un alias del primer argumento de la función
        --  _contact_id se utilizará, como resultado de la primera  consulta (se declara como variable)
        
        SELECT _contacto_id INTO _contacto_id FROM llamadas WHERE id = _llamada_id;
        UPDATE llamadas SET atendida = true WHERE id = _llamada_id;
        UPDATE contactos SET no_interesado = true WHERE id = _contacto_id;
        
        RETURN _contacto_id;
        
        END;
        $$
        
  3. Declaración de variables (plpgsql-declarations)

    name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];
    
    1. Hay que declarar las variables, para luego poder usarlas

    2. Tienen un nombre y un tipo (cualquier tipo sql)

    3. Puedes asignarle un valor (con :=), uno predeterminado (DEFAULT) o ninguno (y será null hasta que le asignes un valor)

    4. Puedes asignarlo y que no pueda modificarse en el programa (CONSTANT).

      DECLARE
        user_id integer;
        quantity numeric(5);
        url varchar;
      
        quantity integer DEFAULT 32;
        url varchar := 'http://mysite.com';
        transaction_time CONSTANT timestamp with time zone := now();
      
      1. Están disponibles los argumentos de la función (argumentos posicionales: $1, $2 …)

      2. Puedes usar alias en las variables

  4. En PL/SQL una expressión es el resultado de ejecutar un select: 5 o x < y o count(id) from usuarios; (es cómo añadirle select)

  5. Instrucciones Básicas:

    1. Asignación (:=, recomendado porque es compatible PL/SQL)

    2. Instrucciones SQL. Se ejecutan tal cual

      1. No devuelven resultado

        CREATE TABLE mytable (id int primary key, data text);
        INSERT INTO mytable VALUES (1,'one'), (2,'two');
        
      2. Devuelven un resultado. Usas SELECT .. INTO

        -- Puede ser un valor, o toda la fila (sólo la primera)
        
        SELECT * INTO myrec FROM emp WHERE empname = myname;
        
      3. Puedes comprobar si hubo resultado:

        1. Usando la variable local booleana FOUND

          SELECT * INTO myrec FROM emp WHERE empname = myname;
          IF NOT FOUND THEN
            RAISE EXCEPTION 'employee % not found', myname;
          END IF;
          
        2. O revisando cuántas filas ha devuelto usando GET DIAGNOSTICS filas_devueltas = ROW_COUNT;

  6. Estructuras de control (hay varias):

    1. Función condicional CASE o …

    2. IF .. THEN .. ELSE (y sus variantes):

      IF v_count > 0 THEN
        INSERT INTO users_count (count) VALUES (v_count);
        RETURN 't';
      ELSE
        RETURN 'f';
      END IF;
      
  7. Estructuras de repetición (hay varias):

    1. WHILE

      WHILE valor1 > 0 AND valor2 > 0 LOOP
      -- ejecutas estas instrucciones
      -- mientras es cumple la condicion
      END LOOP;
      
      WHILE NOT valor2 LOOP
      -- ejecutas estas instrucciones
      -- mientras valor2 es false (NOT false = true)
      END LOOP;
      
    2. FOR .. IN .. LOOP (recorre un resultado de más de una fila)

      CREATE FUNCTION refresh_mviews() RETURNS integer AS $$
      DECLARE
      mviews RECORD;
      BEGIN
      RAISE NOTICE 'Refreshing all materialized views...';
      
      FOR mviews IN
      SELECT n.nspname AS mv_schema,
      c.relname AS mv_name,
      pg_catalog.pg_get_userbyid(c.relowner) AS owner
      FROM pg_catalog.pg_class c
      LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
      WHERE c.relkind = 'm'
      ORDER BY 1
      LOOP
      
      -- Now "mviews" has one record with information about the materialized view
      
      RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',
                  quote_ident(mviews.mv_schema),
                  quote_ident(mviews.mv_name),
                  quote_ident(mviews.owner);
      EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
      END LOOP;
      
      RAISE NOTICE 'Done refreshing materialized views.';
      RETURN 1;
      END;
      $$ LANGUAGE plpgsql;
      

Entendiendo la estructura del código PL/pgSQL

Se busca entender la estructura de una función desarrollada en PL/pgSQL

  1. ¿Cual es el nombre, los argumentos y qué valores devuelve?

  2. ¿Cuales son las variables que puede usar dentro de la función?

  3. ¿Cual es el bloque principal del código?

  4. ¿Qué otros bloques de instrucciones hay?

  5. ¿Qué resultado me da la función? Pon 3 ejemplos

Ejemplo:

  1. Uno muy básico

  2. Otro muy sencillo

Usando PL/SQL

Desarrolla las siguientes funciones, usando PL/pgSQL:

  1. probando_plpgsql(). Sin argumentos, y que devuelva la multiplicación de dos números, que se declaran como variables estáticas en el programa (n1 y n2).

  2. calcula(integer, integer, integer). Devolverá la suma de los dos primeros argumentos, multiplicada por el tercero.

  3. decide(boolean). Según el argumento de entrada, devolverá Sí o No

  4. repite(integer, varchar). Escribirá en pantalla el texto del segundo argumento, tantas veces como diga el primero. P.ej repite(4,”Hola”). Devuelve el número de veces que lo repite;

  5. calculadora(integer, integer,operacion). Devolverá una suma o una multiplicación de los dos primeros argumentos. Si la operación no es ni suma ni multiplicar, devolverá un 0

Una vez implementes estas funciones y tengas clara la sintaxis básica, puedes implementar las funciones en Ejercicios con la BD Hubway

7.8. Creando Triggers

Video Clase

  1. Un trigger es un objeto de la BD que:

    1. Ejecuta una acción (trigger function)

    2. Cuando se dan una serie de acciones DML (insert/update/delete)

    3. Es programación imperativa (no sql)

  2. Se crean usando CREATE TRIGGER (sql-createtrigger):

    1. Se «dispara» (trigger) en ciertos momentos:

      1. Antes (BEFORE)

      2. Después (AFTER)

      3. En vez de (INSTEAD OF)

    2. Sobre una tabla (o vista) determinada (ON) para cada fila (o por instrucción completa)

    3. Cuando ocurre un evento determinado: INSERT / UPDATE / DELETE (o TRUNCATE)

    4. Y puedes especificar que se dispare (la función trigger), para ese evento en cualquier caso o sólo cuando (WHEN) ocurre una condición

    CREATE TRIGGER name
    { BEFORE | AFTER | INSTEAD OF }
    { event [ OR ... ] }
    ON table_name
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE FUNCTION function_name
    
    where event can be one of:
    
    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE
    
  3. Algunos ejemplos:

    CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION check_account_update();
    
    CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
    EXECUTE FUNCTION check_account_update();
    
  4. Trigger Functions (plpgsql-trigger), son funciones con algunas características especiales

    1. No tienen argumentos en la definición (bueno, se puede especificar al crear el trigger)

    2. Devuelve un tipo especial (RETURN trigger)

    3. Se definen algunas variables especiales (p.ej OLD y NEW)

    4. Se asignan al trigger en EXECUTE FUNCTION

  5. Un ejemplo completo:

    -- En este caso creas una tabla en la que
    -- insertas unos campos, y añades los dos últimos en
    -- la inserción (una inserción en dos fases)
    
    CREATE TABLE emp (
      empname text,
      salary integer,
      last_date timestamp,
      last_user text
    );
    
    -- Creas la función trigger
    
    CREATE FUNCTION emp_stamp() RETURNS trigger AS
    $$
    BEGIN
    -- Simula una restricción not null sobre empname y salary
    -- Usa la variable especial NEW
    
      IF NEW.empname IS NULL THEN
          RAISE EXCEPTION 'empname no puede ser nulo';
      END IF;
      IF NEW.salary IS NULL THEN
          RAISE EXCEPTION '% no puede tener salario sin asignar', NEW.empname;
      END IF;
    
      -- Registra quien y cuando cambió el registro
      NEW.last_date := current_timestamp;
      NEW.last_user := current_user;
      RETURN NEW;
    END;
    $$
    LANGUAGE plpgsql;
    
    -- Creas el trigger y su contexto: nombre, evento, objeto supervisado y función
    
    CREATE TRIGGER emp_stamp
    BEFORE INSERT OR UPDATE
    ON emp
    FOR EACH ROW
    EXECUTE FUNCTION emp_stamp();
    

Creando triggers (disparadores)

Una vez que entiendes la sintaxis de construcción de la función de tipo trigger y como crear un trigger, puedes implementar los disparadores en Ejercicios con la BD Hubway