Indices y triggers en postgres

Indices

Los índices se utilizan principalmente para incrementar el rendimiento de una base de datos en relación con las búsquedas, pues estos proporcionan mayor rapidez al motor. Sin embargo, su uso inapropiado dará lugar a una base de datos más lenta

La sintaxis para crear un índice es la siguiente:

CREATE [ UNIQUE ] INDEX nombre_indice ON tabla

[ USING nombre_acceso ] (columna [ nombre_operador] [, ...] )

CREATE [ UNIQUE ] INDEX nombre_indice ON tabla

[ USING nombre_acceso ] ( nombre_funcion( r">columnale> [, ... ]) nombre_operador )

UNIQUE

Provoca que el sistema compruebe si existen valores duplicados en la tabla cuando se crea el índice (si ya existen datos) y cada vez que se añaden datos. Los intentos de insertar o actualizar datosduplicados generarán un error.

nombre_indice

El nombre del índice que se desea crear.

tabla

Nombre de la tabla en la cual desea crear un índice.

nombre_acceso

Nombre del método de acceso que se utilizara para el índice. El método de acceso de defecto es BTREE. Postgres proporciona tres métodos de acceso para índices secundarios y son los siguientes:

• BTREE: Unaimplementación de los btrees de alta concurrencia de Lehman-Yao.

• RTREE: Implementa rtrees estándar utilizando el algoritmo de partición cuadrática de Guttman.

• HASH: Una implementación de las dispersiones lineales de Litwin.

columna

El nombre de la columna de la tabla a la cual desea asociar el índice.

nombre_operador

Una clase de operadores asociada.nombre_funcion

Una función definida por el usuario, que devuelve un valor que puede ser indexado.

.

Creación de un Índice

Para crear un índice en el campo nombre de una tabla personas, se ejecuta la siguiente instrucción:

CREATE TABLE personas (

nombre VARCHAR(30) NOT NULL,
apellido VARCHAR(30) NOT NULL

);

• CREATE UNIQUE INDEX personas_nombre_idx ONpersonas (nombre);

En este ejemplo, el uso de UNIQUE no permitirá nombres repetidos en la tabla nombre. Utilice como buena práctica prefijos que le ayuden a identificar y mantener el orden de los índices, en el ejemplo se usa el nombre de la tabla, un guión como separador y el nombre del campo al cual referenciaremos el índice seguido de un guión y un idx , que quiere abreviar index (índice).Eliminación de un Índice

Para eliminar un índice, utilice la siguiente instrucción:

• DROP INDEX nombre_indice;

nombre_indice : El nombre del índice que desee eliminar.

Triggers

Los Triggers son funciones que se ejecutan de forma automática en respuesta a ciertos eventos que ocurren sobre una tabla en una base de datos.

La definición de un trigger consta básicamente de dospartes,

1. La definición de la función asociada al trigger que es la que ejecuta la acción en respuesta al evento (INSERT, UPDATE, DELETE)

2. La definición formal de trigger que indicará:

a) De que tabla se esperarán los eventos
b) A qué tipo de evento se responderá

Una función tipo trigger es similar a una función normal, salvo por unos detalles:

CREATE OR REPLACEFUNCTION nombreFuncion(param,param)

RETURNS triggers AS $$

DECLARE
variable;
variable;
BEGIN
sentencia;
sentencia;
RETURN retorno;
END;
$$
Language 'plpgsql';

Primero, el tipo de retorno ya no es un tipo normal de PostgreSQL, sino el tipo especial trigger.

Segundo, dependiendo de las acciones del trigger, el parámetro en elcomando RETURN.

Luego de elaborada la función trigger, debemos definir formalmente el trigger de este modo:

CREATE OR REPLACE TRIGGER nombretrigger

[AFTER | BEFORE ] [INSERT | UPDATE | DELETE]

ON nombretabla

FOR EACH [ROW | STATEMENT]

EXECUTE PROCEDURE funcion;

Detalle Parámetros

CREATE TRIGGER nombretrigger = Creación del trigger

[AFTER | BEFORE ] = Antes o después...