PDF de programación - Introducción a PostgreSQL

Vistas Materializadas

Introducción a PostgreSQL

Álvaro Herrera

1er Foro Mundial de Tecnolog’ia Libre

Álvaro Herrera

Introducción a PostgreSQL

Vistas Materializadas

La Situación de Ejemplo

Una compañía de teléfonos tiene una tabla con todas las llamadas
telefónicas efectuadas por sus clientes, y desea tener una tabla con los
totales de llamadas.

Álvaro Herrera

Introducción a PostgreSQL

Vistas Materializadas

Ejemplo: La Tabla de Llamadas

Tabla «public.llamadas»

Columna
cliente_id
inicio
fin

Tipo
integer
timestamp with time zone
timestamp with time zone

Modificadores
not null REFERENCES clientes
not null
not null

Álvaro Herrera

Introducción a PostgreSQL

Vistas Materializadas

Ejemplo: La Vista Materializada

Tabla «public.total_llamadas»

Columna
cliente_id
anno
mes
duracion_total

Tipo
integer
integer
integer
interval

Modificadores
not null REFERENCES clientes
not null
not null
not null

Álvaro Herrera

Introducción a PostgreSQL

Vistas Materializadas

Ejemplo: El Trigger de Inserción

CREATE OR REPLACE FUNCTION agrega_al_total()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN

LOOP

UPDATE total_llamadas

SET duracion_total = duracion_total +

(NEW.fin - NEW.inicio)

WHERE cliente_id = NEW.cliente_id AND

anno = extract(year FROM NEW.inicio) AND
mes = extract(month FROM NEW.inicio);

IF FOUND THEN

RETURN;

END IF;

Álvaro Herrera

Introducción a PostgreSQL

Vistas Materializadas

Ejemplo: El Trigger de Inserción (cont.)

BEGIN

INSERT INTO total_llamadas

(cliente_id, anno, mes, duracion_total)

VALUES

(NEW.cliente_id,
extract(year FROM NEW.inicio),
extract(month FROM NEW.inicio),
NEW.fin - NEW.inicio);

EXCEPTION WHEN unique_violation THEN

-- no hacer nada

END;

END LOOP;

END;

Álvaro Herrera

Introducción a PostgreSQL

Vistas Materializadas

Demo

INSERT INTO llamadas (cliente_id, inicio, fin)

VALUES (1,

’2005-10-21 10:00’,
’2005-10-21 10:45’);

SELECT * FROM total_llamadas;

cliente_id
1

anno mes
2005
10

duracion_total
00:45:00

(1 fila)

Álvaro Herrera

Introducción a PostgreSQL

Vistas Materializadas

Demo 2

INSERT INTO llamadas (cliente_id, inicio, fin)

VALUES (1,

’2005-10-22 00:00’,
’2005-10-22 03:00’);

SELECT * FROM llamadas;

cliente_id
1
1

(2 filas)

inicio
2005-10-21 10:00:00-03
2005-10-22 00:00:00-03

fin
2005-10-21 10:45:00-03
2005-10-22 03:00:00-03

SELECT * FROM duracion_mensual;

cliente_id
1

anno mes
2005
10

duracion_total
03:45:00

(1 fila)

Álvaro Herrera

Introducción a PostgreSQL

Vistas Materializadas

Demo 3

INSERT INTO llamadas (cliente_id, inicio, fin)

VALUES (1,

’2005-11-22 00:00’,
’2005-12-22 03:00’);

SELECT * FROM duracion_mensual;

cliente_id
1
1

(2 filas)

anno mes
10
2005
2005
11

duracion_total
03:45:00
30 days 03:00:00

Álvaro Herrera

Introducción a PostgreSQL