7. Programando el SGBD¶
En este tema aprenderemos a:
Ver qué alternativas ofrece el SGBD para programar en el SGBD
Entender el uso de funciones, procedimientos y triggers
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
Un SGBD podría considerarse también una plataforma de desarrollo (SDK) que permite:
Extender la funcionalidad programando el comportamiento del servidor (pero implica crear una capa extra de complejidad)
Aunque el estándar SQL lo incluye, en la práctica es muy dependiente del SG (genera incompatibilidad)
En el caso de Postgres es muy complejo, potente y una de las razones para usarlo como SG (server-programming)
En la práctica:
Muy potente si orientado al procesado de datos
Ojo a la incompatibilidad y la arquitectura de la aplicación (mezcla lógica y datos)
Si la complejidad del caso de uso está en los datos mejor programar el SGBD (definir esa capa extra)
Más utilizado en aplicaciones empresariales que en aplicaciones web (aunque depende de la aplicación en sí).
Lo que buscamos es definir un comportamiento personalizado a nuestro caso de uso. Podemos definir:
Tipos de datos (sql-createtype) y dominios (sql-createdomain)
Operadores (sql-createoperator)
Programas (Rutinas):
Funciones (sql-createfunction)
o procedimientos (sql-createprocedure)
y … mucho más (server-programming)
Los procedimientos almacenados (stored procedures) son un caso particular de uso de funciones pero:
Usan CREATE PROCEDURE (en vez de CREATE FUNCTION)
No devuelven un valor (bueno, pueden hacerlo con el truco de parámetros de salida)
Se llaman usando CALL (como instrucción independiente, no dentro de una consulta SQL)
Pueden gestionar transacciones (sólo usando PL/pgSQL)
También podemos definir comportamientos frente a eventos (triggers)
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
Se pueden definir sobre tablas o vistas
Cuando ocurren eventos DML (Insert / Update / Delete)
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:
Tipo de objeto
Nombre
Parámetros de entrada
Parámetros de salida
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
PG usa cuatro tipos diferentes de funciones (xfunc)
De consulta (query language functions). Las funciones están escritas en SQL y quizá las más sencillas de implementar.
De programación (procedural language functions). Las funciones están descritas en un lenguaje de programación específico (PL/SQL u otros)
Nativas (usando C):
Internas (Estáticas). Las funciones estás escritas en C y cargadas en el inicio de forma estática (siempre están)
Dinámicas. Las funciones se cargan como módulos de forma dinámica (se usan bajo demanda)
Usa los conceptos de programación modular (y estructurada):
Usa (o no) parámetros de entrada y salida con fuerte tipado de datos
Tiene un cuerpo de código donde está la programación, usando:
Paradigma declarativo: SQL
Paradigma imperativo: p.ej en PL/SQL (usando Procedural Languages)
Son muy fáciles de utilizar
Llamas a las función, con sus parámetros, en la instruccion SQL
Devuelven un valor (value expression)
-- Sin parámetros SELECT la_funcion(); -- Con parámetros SELECT la_funcion(parametro1, parametro2);
El valor puede ser cualquier tipo de datos SQL (desde lo más simple a uno más complejo)
Funciones en Postgres (ya desarrolladas). Es una de las grandes ventajas de postgres (incluidas en el catálogo):
Te ofrece una biblioteca (library) de funciones internas muy grande (y las posibilidades de extenderla)
Ves las funciones que puedes usar:
Definidas por el usuario en esa base de datos (en ese schema):
\df
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
Ver los datos de una función específica (nativa o definida por el usuario):
\df la_funcion
Hay muchas (pero muchas) funciones en Postgres, por ejemplo:
Matemáticas (functions-math)
De agregación (functions-aggregate)
Gestión de secuencias (functions-sequence)
Del sistema:
De información (functions-info)
De administración (functions-admin)
De fechas y cadenas de texto
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
Formato Fecha/Hora:
Muy habitual (y útil)
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
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
Funciones y Operadores de Fechas (functions-datetime)
Muchísimas alternativas de manipulación
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
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
Dar formato a las Fechas (functions-formatting)
Sirven para convertir (formatear) texto a fechas (y viceversa)
Muy flexibles, y útiles (si está bien almacenado el valor)
Siempre el mismo patrón: valor a formatear, plantilla para usar en el formato (sea de entrada o salida)
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
Hay que revisar el tipo de formatos y patrones (para texto, números y fechas) para definir la conversión
Dar formato a cadenas de texto (functions-string)
Reciben una cadena de texto (varchar) y algún parámetro. Devuelven texto
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
Funciones Condicionales (functions-conditional):
Permiten modificar el flujo de ejecución (como un if/then/else)
Dos muy útiles
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;
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 ...
Las funciones se crean con CREATE FUNCTION (sql-createfunction)
Puede ser muy sencilla o muy compleja
En SQL o lenguajes procedimentales (veremos PL/SQL)
Los componentes son siempre los mismos:
Declaración de la función: nombre y argumentos (posicionales o parámetros)
Valor de retorno
Cuerpo de la función
Funciones Postgres usando SQL (xfunc-sql)
Directamente como una expresión (usando RETURN retype AS definition)
Usando un bloque de código (hay que usar delimitadores $$ o literales)
Se pueden devolver valores o tablas (RETURN table(columns)).
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;
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
Repasa la sintaxis de CREATE FUNCTION para entender el código descrito en la clase
Crea una función que sume los dos primeros argumentos, y lo multiplique por un tercero
Crea una función (contador) que cuente el número de registros de una tabla (una tabla concreta de tu BD)
Crea una función (borrar) que borre todos los registros de una tabla (una tabla concreta de tu BD)
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
Recuerda que son muy similares a las funciones (pero con diferencias importantes)
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); $$;
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
-
Grupo de instrucciones …
… que se ejecutan (commit) de forma completa …
… o se anulan (rollback) bajo ciertas condiciones
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) }
Un sistema transaccional cumple con las propiedades ACID
Atomicity
Consistency
Isolation
Durability
Ejemplo habitual de transacción:
Transacción: enviar dinero de una cuenta bancaria a otra
La concurrencia: no tan fácil cuando varios usuarios acceden a la vez. Las transacciones solucionan (casi siempre) el problema
Transacciones en Postgres (tutorial-transactions):
Modo predeterminado: auto commit (cada instrucción es una transacción)
O decides cuando ejecutarlo (transacción a medida):
Comienza la transacción: sql-begin (START TRANSACTION en SQL estándar)
Se confirma: sql-commit
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)
Anulación total, o parcial, de una transacción:
ROLLBACK (sql-rollback) anula la transacción
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;
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
Es un lenguaje de programación imperativo, que se ejecuta en Postgres
Ejecuta SQL peeeeero usando un paradigma imperativo («paso a paso»)
Declaras, y usas, variables, con los tipos de datos SQL (básicos y complejos, si lo necesitas)
Tiene estructuras de control y estructuras de repetición
Lo usas para crear funciones y procedimientos
Se ejecuta en el servidor (tiene varias ventajas)
Es muy similar a PL/SQL (Oracle)
Estructura de un «programa PL/pgSQL» (plpgsql-structure):
Se crea la función, especificando el lenguaje (plpgsql):
CREATE FUNCTION somefunc(integer, text) RETURNS integer AS 'function body text' LANGUAGE plpgsql;
Se «implementa» el programa (function body text):
Puedes usar comillas o, mejor, $$ para delimitar el cuerpo de la función
Usas características del lenguaje y sentencias SQL (es una mezcla de los dos)
El código va en bloques estructurados (uno, o anidados):
En un contexto (label)
Declaración de variables (DECLARE)
Instrucciones (en bloque BEGIN .. END).
[ <<label>> ] [ DECLARE declarations ] BEGIN statements END [ label ];
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; $$
Declaración de variables (plpgsql-declarations)
name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];
Hay que declarar las variables, para luego poder usarlas
Tienen un nombre y un tipo (cualquier tipo sql)
Puedes asignarle un valor (con :=), uno predeterminado (DEFAULT) o ninguno (y será null hasta que le asignes un valor)
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();
Están disponibles los argumentos de la función (argumentos posicionales: $1, $2 …)
Puedes usar alias en las variables
En PL/SQL una expressión es el resultado de ejecutar un select:
5
ox < y
ocount(id) from usuarios;
(es cómo añadirle select)Instrucciones Básicas:
Asignación (:=, recomendado porque es compatible PL/SQL)
Instrucciones SQL. Se ejecutan tal cual
No devuelven resultado
CREATE TABLE mytable (id int primary key, data text); INSERT INTO mytable VALUES (1,'one'), (2,'two');
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;
Puedes comprobar si hubo resultado:
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;
O revisando cuántas filas ha devuelto usando
GET DIAGNOSTICS filas_devueltas = ROW_COUNT
;
Estructuras de control (hay varias):
Función condicional CASE o …
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;
Estructuras de repetición (hay varias):
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;
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
¿Cual es el nombre, los argumentos y qué valores devuelve?
¿Cuales son las variables que puede usar dentro de la función?
¿Cual es el bloque principal del código?
¿Qué otros bloques de instrucciones hay?
¿Qué resultado me da la función? Pon 3 ejemplos
Ejemplo:
Uno muy básico
Otro muy sencillo
Usando PL/SQL
Desarrolla las siguientes funciones, usando PL/pgSQL:
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).
calcula(integer, integer, integer). Devolverá la suma de los dos primeros argumentos, multiplicada por el tercero.
decide(boolean). Según el argumento de entrada, devolverá Sí o No
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;
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
Un trigger es un objeto de la BD que:
Ejecuta una acción (trigger function)
Cuando se dan una serie de acciones DML (insert/update/delete)
Es programación imperativa (no sql)
Se crean usando CREATE TRIGGER (sql-createtrigger):
Se «dispara» (trigger) en ciertos momentos:
Antes (BEFORE)
Después (AFTER)
En vez de (INSTEAD OF)
Sobre una tabla (o vista) determinada (ON) para cada fila (o por instrucción completa)
Cuando ocurre un evento determinado: INSERT / UPDATE / DELETE (o TRUNCATE)
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
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();
Trigger Functions (plpgsql-trigger), son funciones con algunas características especiales
No tienen argumentos en la definición (bueno, se puede especificar al crear el trigger)
Devuelve un tipo especial (RETURN trigger)
Se definen algunas variables especiales (p.ej OLD y NEW)
Se asignan al trigger en EXECUTE FUNCTION
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