PLPGSQL: conteo, registro y triggers

Está buena la explicación, sin embargo me parece que hubiera quedado mucho mejor si se usara un ejemplo del mundo real para nombrar los atributos, tablas y procedimientos/funciones en lugar de letras aleatorias del abecedario (‘aaa’, ‘bbb’ o ‘efghi’).

Comparto mi implementación:

-- Creando Tabla Master
CREATE TABLE public.tabla_master
(
    nombre_master character varying,
    apellido_master character varying,
    CONSTRAINT tabla_master_pkey PRIMARY KEY (nombre_master)
);

-- Creando Tabla Réplica
CREATE TABLE public.tabla_replica
(
    nombre_replica character varying,
    apellido_replica character varying,
    CONSTRAINT tabla_replica_pkey PRIMARY KEY (nombre_replica)
);

-- Creando Function
CREATE OR REPLACE FUNCTION duplicate_records()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
	INSERT INTO tabla_replica(nombre_replica, apellido_replica)
	VALUES (NEW.nombre_master, NEW.apellido_master);
	RETURN NEW;
END
$$;


-- Creando Trigger
CREATE TRIGGER duplicate_master_to_replica
	BEFORE INSERT
	ON tabla_master
	FOR EACH ROW
	EXECUTE PROCEDURE duplicate_records();

-- Haciendo prueba de insert 
INSERT INTO tabla_master(nombre_master, apellido_master)
VALUES('Juanito', 'Alimaña');

-- Validando que los datos esten en ambas tablas
SELECT * FROM public.tabla_master;
SELECT * FROM public.tabla_replica;```

Se recomienda tener la documentación oficial cerca para diseñar de acuerdo con las opciones que ofrece, en este caso PostgreSQL.

Tipos de disparadores que pueden usar las tablas, vistas, etc:

Documentación:
https://www.postgresql.org/docs/

Me agrada mucho como explica todo al detalle; convirtiendo temas un poco enredados a algo más fácil de digerir, como en el curso de fundamentos de bases de datos.

Me parece fantástico como por medio de triggers y funciones podemos automatizar un backup de información de una tabla principal, será interesante ver las diferentes aplicaciones a las cuales podemos llegar al finalizar este curso. Gracias Israel!

PLPGSQL: conteo, registro y triggers

count_total_movies function

CREATE OR REPLACE FUNCTION count_total_movies()
RETURNS int
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN COUNT(*) FROM peliculas;
END
$$;

SELECT count_total_movies();

duplicar registros

-- al haber un insert en un tabla duplica ese registro en otra tabla
CREATE OR REPLACE FUNCTION duplicate_records()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  -- NEW es el registro que se acaba de hacer insert
  INSERT INTO aaab(bbba, ccca)
  VALUES (NEW.bbb, NEW.ccc);
  
  RETURN NEW;
END
$$;

-- creando el trigger
CREATE TRIGGER aaa_changes
  BEFORE INSERT
  ON aaa
  FOR EACH ROW
  EXECUTE PROCEDURE duplicate_records();

-- insertando valores para probar el trigger
INSERT INTO aaa(bbb, ccc)
VALUES ('abcde', 'efghi');

Funcion 1

CREATE OR REPLACE FUNCTION count_total_movies()
RETURNS int
LANGUAGE plpgsql
AS $$
BEGIN
	RETURN COUNT(*)
	FROM peliculas;
END
$$;

SELECT count_total_movies();

Funcion con Trigger*

CREATE OR REPLACE FUNCTION duplicate_records()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
	INSERT INTO aaab(bbba, ccca)
	VALUES (NEW.bbb, NEW.ccc);
	RETURN NEW;
END
$$;

CREATE TRIGGER aaa_changes
	BEFORE INSERT
	ON aaa
	FOR EACH ROW
	EXECUTE PROCEDURE duplicate_records();

INSERT INTO aaa(bbb, ccc)
VALUES ('abcde','efghi');

SELECT *
FROM aaa;

SELECT *
FROM aaab;

Crear el disparador para el Trigger con: CREATE TRIGGER

Muy interesante, también se debe tener en cuenta que los trigger forman parte atómica de las operaciones de SQL

Genial va quedando mucho mas claro los temas de store procedure (procedimientos almacenados), functions (funciones) y Triggers ( disparadores).

¿Alguien me podría decir cual es la diferencia entre RETURNS y RETURN, o en que casos uso cada uno de ellos, se agradece?

Excelente

Excelente, entender el funcionamiento

Les comparto como quedó mi código:

CREATE OR REPLACE FUNCTION duplicate_records()
RETURNS TRIGGER -- Función tipo trigger.
LANGUAGE plpgsql
AS $$
BEGIN
	INSERT INTO ab_tabla(campo_a, campo_b) --Inserta en los campos campo_a y campo_b de la tabla ab_tabla.
	VALUES (NEW.campo_b, NEW.campo_c); -- Inserta el nuevo valor que tiene el campo_b (a_tabla) en campo_a (ab_tabla).
	RETURN NEW; -- Regresa los campos que tendrá la tabla modificada, en este caso los nuevos.
END
$$;

CREATE TRIGGER a_tabla_changes -- Trigger para inserciones en a_tabla.
	BEFORE INSERT -- Realiza el llamado antes de hacer el insert.
	ON a_tabla -- Tabla que al cambiar se llamará el trigger.
	FOR EACH ROW -- Para cada fila define que ejecutar.
	EXECUTE PROCEDURE duplicate_records(); -- Ejecuta la función tipo trigger para copiar los valores a la tabla ab_tabla.

INSERT INTO a_tabla(campo_b, campo_c)
VALUES ('valA','valB');

SELECT * FROM a_tabla, ab_tabla;

Evitar el uso del -->> * <<-- en los SELECT:

Algo que he notado es que ningún profesor se detiene a explicar porqué evitar el *, y esto ya lo vienen arrastrando en todos los cursos de BD que he visto.

CREATE OR REPLACE FUNCTION count_total_movies()

-- 1 Especificar el valor de retorno
RETURNS int
-- 2 El lenguaje en el dialecto de postgres
LANGUAGE plpgsql
AS $$
BEGIN   
-- 3 Evitar el * en los SELECTS y esto ya lo vienen arrastrando en todos los cursos de BD
    RETURN( SELECT count(pelicula_id) FROM peliculas );
END
$$;

Que buen curso y excelente profesor, me ha está quedando mucho más claro los conceptos de procedures, functions y triggers 👏

Para ampliar información de los triggers:
https://www.postgresql.org/docs/9.1/plpgsql-trigger.html