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