Introducción a PostgreSQL

Introducción a PostgreSQL

Facultad de Ingeniería

Enrique Felipe Anastacio

Base de Datos

¿Qué es PostgreSQL?

PostgreSQL es un sistema manejador de bases de datos relacionales

basado en postgres 4.2, desarrollado en el departamento Berkeley de

Ciencias de la computación en la universidad de California. Postgres es

Open Source descendiente del código original “Berkeley”, soporta el

estándar SQL y ofrece otros recursos tales como:

○ complex queries

○ foreign keys

○ Triggers

○ Views

○ transactional integrity

○ multiversion concurrency control

○ index methods

○ procedural languages

Postgres puede ser usado, modificado o distribuido para uso privado,

comercial o Académico



Base de Datos


Ahora probaremos un poco la terminal

interactiva llamada psql: para probarla

teclee las siguientes consultas:

SELECT version();

SELECT current_date;

SELECT 2 + 2;



Base de Datos


Ahora crearemos una tabla llamada climas, este ejemplo nos servirá para verificar su estructura:

CREATE TABLE climas (

ciudad varchar(80),

temp_baja int, -- baja temperatura

temp_alta int, -- alta temperatura

prcp real, -- precipitacion

fecha date

);



Base de Datos


Create.Ahora crearemos otra tabla llamada ciudades, una de las cualidades de esta es que utilizara un tipo de dato especifico de postgres, el tipo POINT.

CREATE TABLE ciudades (

nombre varchar(80),

localizacion point

);

Nota: Si desea eliminar una tabla utilice el siguiente comando:

DROP TABLE tablename;



Base de Datos


Insert.Ya creadas nuestras tablas ahoraprocederemos a utilizar INSERT el Statement(Sentencia) que nos servirá para insertardatos en nuestras tablas, su sintaxis puedeser alguna de las siguientes:

INSERT INTO climas ( ciudad, temp_baja , temp_alta, prcp, fecha)

VALUES ('Mexico', 5, 30, 0.25, '2009-06-23');

INSERT INTO climas VALUES ('Atlautla', 5, 30, 0.25, '2009-06-23');



Base de Datos

Introducción a PostgreSQL.

Select.Como se menciono anteriormente postgres soporta el estándar

SQL así que para realizar una consulta, puede utilizar el SELECT

con sus respectivas cláusulas FROM, WHERE y los operadores.

Por ejemplo:

--Esto nos Arrojara una tabla con todos los registros

SELECT * FROM climas;

--Esto nos una tabla con los campos especificados

SELECT Ciudad, temp_baja,temp_alta FROM climas;

--Esto nos mostrará solo la ciudad llamado Atlautla solo si su

prcp(precipitación) es mayor a 20

SELECT * FROM climas WHERE Ciudad = 'Atlautla' AND prcp >

0.20;



Base de Datos


Operadores.

--Muestra todos los registros ordenados por el nombre de la ciudad

SELECT * FROM climas ORDER BY ciudad;

--Elimina registros duplicados

SELECT DISTINCT ciudad FROM climas;

AND Y lógica o Conjunción

OR O lógica o Disyunción

= Igual a

>= Mayor o Igual

<= Menor o Igual

<> o != Diferente



Base de Datos


Join.Muchas veces necesitamos acceder a mas de unatabla al mismo tiempo, para ello utilizares los joinsque nos permitirán acceder a múltiples registros dedos o mas tablas al mismo tiempo. Un JOIN es launión de dos o mas tablas. Por ejemplo:

SELECT * FROM climas, ciudades WHERE ciudad= 'Mexico';

SELECT climas.ciudad, climas.temp_baja, climas.temp_alta, climas.prcp, climas.fecha, ciudades.localizacion

FROM climas, ciudades

WHERE ciudades.nombre = climas.ciudad;



Base de Datos


Funciones.Las funciones de agregación realizanoperaciones con un conjunto de valores. Porejemplo:

Encontrar promedios o encontrar el máximo o mínimo valor. SELECT max(temp_baja) FROM climas;

SELECT ciudad FROM climas WHERE temp_baja= (SELECT max(temp_baja) FROM climas);

SELECT ciudad, max(temp_baja) FROM climas WHERE ciudad LIKE 'M%‘ GROUP BY ciudad HAVING max(temp_baja) < 40;



Base de Datos


Update.

Esta sentencia se utiliza para actualizar

algunos datos o cuando estos han

cambiado o simplemente por algún error

en la inserción Por ejemplo:

SELECT * FROM climas;

UPDATE climas SET temp_alta =

temp_alta - 2, temp_baja = temp_baja -

5 WHERE fecha >= '2009-06-23';



Base de Datos


Delete.

La sentencia Delete se utiliza para eliminar registros de las tablas, Por ejemplo:

DELETE FROM climas WHERE ciudad='Mexico';

Su sintaxis es:

DELETE FROM NombreTabla(Condicion);



Base de Datos


Herencia.Este es un concepto básico de las base de datosorientados a objetos, por ejemplo considere dostablas:

CREATE TABLE ciudad (

nombre text,

poblacion real,

altitud int -- (in ft)

);

CREATE TABLE capital (

estado char(2)

) INHERITS (ciudad);



Base de Datos


Llave Primaria (PK) y Foránea (FK)Una llave foránea es una referencia entre tablas (campocoincidente), si ahora agregamos llaves foráneas anuestras tablas de ejemplo, su estructura quedaría de lasiguiente manera:


ciudad varchar(80) primary key,

localizacion point

);


ciudad varchar(80) references ciudades(ciudad),

temp_baja int,

temp_alta int,

prcp real,

fecha date);



Base de Datos


Llave Primaria (PK) y Foránea (FK)Para crear las PK‟s y FK‟s a tablas yaexistentes, usamos la siguiente sentencia:


ciudad varchar(80) primary key,

localizacion point

);

ALTER TABLE ciudades

ADD CONSTRAINT ciudad_pkPRIMARY KEY (ciudad);



Base de Datos


Llave Primaria (PK) y Foránea (FK)


ciudad varchar(80) referencesciudades(ciudad),

temp_baja int,

temp_alta int,

prcp real,

fecha date);

ALTER TABLE climas

ADD CONSTRAINT ciudad_fk FOREIGN KEY (ciudad) REFERENCES ciudades (ciudad) ON DELETE CASCADE;



Base de Datos


Llave Primaria (PK) y Foránea (FK)Hay tres parámetros cuando definimos una clave foránea que son muy importantes y que definen como la base de datos se va a comportar para salvaguardar la integridad de nuestros datos.

ALTER TABLE [ ONLY ] name [ * ] action [, ... ]

Estos parámetros son:

MATCH tipo

ON DELETE accion

ON UPDATE accion

En donde tipo puede tener estos valores:

FULL: No permite que una columna tenga el valor NULL en una clave foránea compuesta por varias columnas

SIMPLE: Permite que una columna tenga el valor NULL en una clave foránea compuesta por varias columnas



Base de Datos


Llave Primaria (PK) y Foránea (FK)Y accion puede tener estos valores:

NO ACTION: Produce un error indicando que un DELETE óUPDATE creará una violación de la clave foránea definida.

RESTRICT: Produce un error indicando que un DELETE óUPDATE creará una violación de la clave foránea definida.

CASCADE: Borra ó actualiza automáticamente todas lasreferencias activas

SET NULL: Define las referencias activas como NULL

SET DEFAULT: Define las referencias activas como el valor pordefecto (si está definido) de las mismas



Base de Datos


Llave Primaria (PK) y Foránea (FK)ALTER TABLE

ALTER TABLE [ ONLY ] name [ * ] action [, ... ]

ALTER TABLE [ ONLY ] name [ * ] RENAME [ COLUMN ] column TO new_column

ALTER TABLE name RENAME TO new_name

ALTER TABLE name SET SCHEMA new_schema

Action :

ADD [ COLUMN ] column type [ column_constraint [ ... ] ]

DROP [ COLUMN ] column [ RESTRICT | CASCADE ]

ALTER [ COLUMN ] column [ SET DATA ] TYPE type [ USING expression ]

ALTER [ COLUMN ] column SET DEFAULT expression

ALTER [ COLUMN ] column DROP DEFAULT

ALTER [ COLUMN ] column { SET | DROP } NOT NULL

ALTER [ COLUMN ] column SET STATISTICS integer

ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }

ADD table_constraint

DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]

DISABLE TRIGGER [ trigger_name | ALL | USER ]

ENABLE TRIGGER [ trigger_name | ALL | USER ]

ENABLE REPLICA TRIGGER trigger_name

ENABLE ALWAYS TRIGGER trigger_name

DISABLE RULE rewrite_rule_name

ENABLE RULE rewrite_rule_name

ENABLE REPLICA RULE rewrite_rule_name

ENABLE ALWAYS RULE rewrite_rule_name

CLUSTER ON index_name SET WITHOUT

CLUSTER SET WITH OIDS



Base de Datos


Llave Primaria (PK) y Foránea (FK)

Ahora si tratáramos de hacer una inserción en la tabla climas postgres lanzará un error indicando que ese campo no existe en la tabla ciudades, por tanto nuestros datos quedaran íntegros y no abra información demás.

NOTA: Un campo para poder ser llave foránea deberá ser del mismo tipo de dato de la otra tabla.



Base de Datos


Vistas.Una vista es una tabla virtual que puede contenercampos de una o varias tablas, y estas a su vez semanipulan como tablas, cualquier Statement SELECTpuede ser una vista. Para crear una vista:

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEWname [ ( column_name [, ...] ) ] AS query

CREATE VIEW MiVista AS

SELECT temp_baja, temp_alta, prcp, fecha, localizacion

FROM climas, ciudades

WHERE ciudad = nombre;



Base de Datos


Transacciones.Nos permiten ejecutar múltiples consultas y hacerque todas se ejecuten o que ninguna lo haga.

Iniciamos la transacción con el comando BEGIN,después ejecutamos alguna sentencia, y paraterminar con la transacción usamosROLLBACK.

Por lo tanto, los datos quedaran intactos alhacer ROLLBACK, lo que deshace todas lasoperaciones que escribimos.

Pero si deseamos que los cambios seanpermanentes utilizamos COMMIT.



Base de Datos


Funciones.

Se almacenan y ejecutan desde el proceso debase de datos y no desde la aplicación del

cliente.

Comúnmente llamadas "PROCEDIMIENTOSALMACENADOS" no solo en lenguaje SQL quees un lenguaje que carece de estructuras decontrol como IF, WHILE, FOR y otros, sino quepermite definir un lenguaje propio y extenderlo ala base de datos, es así como están disponiblespara Postgres los siguientes lenguajes : C, Perl,Phyton, PHP entre otros.



Base de Datos


Funciones.

Estos lenguajes deben ser instalados enla base de datos previamente antes deser utilizados.

Sin embargo, también existe un lenguajepropio de PostgreSQL, denominado"PL/pgsql", que posee estructuras decontrol y permite aprovechar toda lapotencia de SQL para crear nuestraspropias funciones.



Base de Datos


Funciones.VENTAJAS DEL USO DE FUNCIONES

Se pueden crear funciones para ser ejecutadas en algún evento de la base de datos. Al borrar, modificar o insertar datos por ejemplo.

Se pueden añadir estructuras de control al lenguaje SQL, podemos ahora definir ciclos de ejecución como FOR y WHILE que nos permitan recorrer variables de tipo array, tablas u otras estructuras.

Las funciones nos permiten realizar cálculos complejos, así ampliamos las funciones pre-definidas por el motor de base de datos y construir funciones a medida de nuestras necesidades o las del cliente/empresa.



Base de Datos


Funciones.VENTAJAS DEL USO DE FUNCIONES

Nos aporta una ejecución fiable para elservidor, es decir, podemos estar seguros delconjunto de instrucciones de la función definidase ejecutará, sin importar si ocurren problemasen el cliente, en una conexión o enlace, una vezsolicitada la función esta se ejecuta en modoseguro y protegido en el servidor.

Es un lenguaje fácil de usar, pues extiende deuna manera simplista pero a la vez potente ellenguaje SQL.



Base de Datos


Funciones.

DESCRIPCION DEL LENGUAJE.

ESTRUCTURA DE PL/pgSQL

El lenguaje PL/pgSQL no es sensible a las mayúsculas.

Todas las palabras clave e identificadores pueden usarse

en cualquier mezcla de mayúsculas y minúsculas, sin

embargo, de preferencia, el nombre de tablas y de

registros utilice según definió en la base de datos.

PL/pgSQL es un lenguaje orientado a bloques. la

estructura básica del lenguaje se define de la siguiente

manera :



Base de Datos


Funciones.DESCRIPCION DEL LENGUAJE.

ESTRUCTURA DE PL/pgSQL

CREATE [OR REPLACE] FUNCTION <nombre_de_funcion> ([lista

de parámetros])

RETURNS <tipo_de_retorno> AS '

DECLARE

<declaración de variables locales de la función>

BEGIN

<sentencias propias de la función>

END;

' LANGUAGE 'plpgsql';

NOTA: En algunos casos no se instala el lenguaje 'plpgsql„, para

instalarlo, solo ejecutamos:

CREATE LANGUAGE 'plpgsql';



Base de Datos


Funciones.DEFINIENDO LOS PARAMETROS DE LAS FUNCIONES

Una de las características de PL/pgSQL es que no es necesario

definir los nombres de variables en la función, sólo es necesario

definir si tipo, de esta manera tenemos que una función puede ser

definida en términos de parámetros de entrada y salida de la

siguiente forma :

FUNCTION suma ( real , real )

RETURNS real AS '

...resto de la función...

Cabe notar que podemos utilizar como parámetros todos los tipos

usados por PostgreSQL, es decir : INTEGER, DATE, TIME,

VARCHAR, CHAR, TIMESTAMP, REAL, TEXT y muchos otros, e

inclusive tipos definidos por el usuario por medio de la instrucción

CREATE TYPE.



Base de Datos


Funciones.DEFINICION DE VARIABLES LOCALES O DE FUNCION

Como explicamos anteriormente, las variables locales son definidas dentro de la sección DECLARE de la función, de forma muy similar a otros lenguajes podemos definir variables, por ejemplo :

DECLAREcontador integer = 0;cadena varchar;hoy date;bandera bool;suma real;

FUNCTION suma ( real , real )RETURNS real AS 'DECLAREnumero_1 alias for $1;numero_2 alias for $2;valor_total real;...resto de la funcion...

Podemos no definir los alias y usar los parámetros directamente, por ejemplo :

valor_total := $1 + $2;



Base de Datos


Funciones.

ASIGNACION DE LAS SENTENCIAS DE PL/pgSQL

Una vez definidos los parámetros de entrada/salida, y también nuestras variables

locales, podemos comenzar a trabajar en nuestra función en la sección BEGIN ....

END;

Las asignaciones de valores se realizan de la misma forma que en PASCAL, es decir,

utilizando := como operador de asignación y punto y coma al final de la línea, ejemplo :


RETURNS real AS '

DECLARE

numero_1 alias for $1;


valor_total real;

BEGIN

valor_total := numero_1 + numero_2;

numero_1 := numero_1 * numero_2;

...resto de la funcion...



Base de Datos


Funciones.

RETORNANDO EL UN VALOR COMO FUNCION

Finalmente una vez procesados nuestros valores

debemos retornar algún valor (pues ese es la esencia de

la función), este retorno de valores también se realiza

dentro de la sección BEGIN de la función, pero utilizando

la siguiente forma básica :


RETURN <variable>;




Base de Datos


Funciones.

RETORNANDO EL UN VALOR COMO FUNCION

Ejemplo completo de la función SUMA :


RETURNS real AS '

DECLARE



valor_total real;

BEGIN

valor_total := numero_1 + numero_2;

RETURN valor_total;

END;




Base de Datos


Funciones.

UTILIZANDO SENTENCIAS DE CONTROL

Podemos utilizar sentencias de control para escribir nuestros

programas y permitir que sigan un algoritmo no lineal, para ello,

contamos básicamente con las siguientes estructuras :

LOOP

EXIT

IF, ELSE

FOR

WHILE



Base de Datos


Funciones.

UTILIZANDO SENTENCIAS DE CONTROL

SENTENCIA DE CICLO LOOP :

Esta sentencia nos permite efectuar un ciclo, su estructura básica es :


LOOP

...sentencias a ejecutar en el ciclo...

END LOOP




Base de Datos


Funciones.SENTENCIA DE SALIDA EXIT :

Cuando usamos un ciclo LOOP, es necesario darle una salida para

que este mismo termine, la sentencia EXIT nos permite finalizar el

LOOP y continuar en la sentencia siguiente a la salida de éste,

generalmente, EXIT está acompañado con la sentencia IF, por

ejemplo

LOOP


contador := contador + 1;

IF contador >= 10 THEN

EXIT; -- salida del loop cuando sea mayor o igual que 10

END IF;

END LOOP




Base de Datos


Funciones.SENTENCIAS IF, ELSE :

Podemos efectuar operaciones de comparación con las sentencias

IF, ELSE, los operadores de comparación son los siguientes :

< menor que ...

> mayor que ...

<> distinto a ...

<= menor o igual que ...

>= mayor o igual que ...

= igual que ...

OR o ...

AND y ...



Base de Datos


Funciones.

SENTENCIAS IF, ELSE :

Ejemplo :


IF >= 10 or THEN

RETURN true;

ELSE

RETURN false;

END IF;




Base de Datos


Funciones.SENTENCIA DE CICLO FOR :

Existen dos tipos de ciclos FOR, el primero tiene que ver claramente con

los ciclos numéricos comunes, es decir, lo que comúnmente se usa para

efectuar repeticiones, y por otra parte, tenemos un ciclo FOR que nos

permite recorrer tablas y procesar sus datos, esta segunda está ligada a

sentencias SQL, veamos entonces como se utiliza la sentencia FOR

CASO 1 : SENTENCIA FOR DE PROCESO CICLICO NUMERICO

FOR <variable> IN <inicio>..<fin> LOOP

... SENTENCIAS A EJECUTAR DENTRO DEL CICLO FOR ...

END LOOP;

o bien puede usar el FOR de forma inversa

FOR <variable> REVERSE <fin>..<inicio> LOOP


END LOOP;



Base de Datos



CASO 1 : SENTENCIA FOR DE PROCESO CICLICO NUMERICO

Ejemplo :


FOR inc IN 1..10 LOOP

factorial := factorial * inc;

END LOOP;

RETURN factorial;

...resto de la función…



Base de Datos


Funciones.

SENTENCIA DE CICLO FOR :

CASO 2: SENTENCIA FOR APLICADA A PROCESOS DE

REGISTROS SQL

FOR <registro o fila> IN <sentencia SELECT SQL> LOOP


END LOOP;



Base de Datos



Ejemplo :


DECLARE

registro RECORD;

BEGIN

FOR registro IN SELECT * FROM productos LOOP

stock := registro.sock_actual + 100;

... otras sentencias ...

END LOOP;


Note que para utilizar esta sentencia FOR debemos declarar una variable

de tipo RECORD o registro. RECORD es una palabra reservada para

estos casos, y no posee una estructura definida pues no ha sido asignada

a ninguna tabla, este tipo de dato nos permite recorrer la tabla de

productos y obtener sus valores por medio de esta variable.



Base de Datos


Funciones.

SENTENCIA DE CICLO CONDICIONAL WHILE

La sentencia WHILE se ejecuta de forma muy similar a otros

lenguajes de programación, su estructura es la siguiente :

WHILE <condición> LOOP

... SENTENCIAS A EJECUTAR DENTRO DEL CICLO WHILE ...

END LOOP;



Base de Datos


Funciones.

SENTENCIA DE CICLO CONDICIONAL WHILE

Ejemplo :


WHILE inc <= 10 LOOP

factorial := factorial * inc;

END LOOP;

RETURN factorial;


Este código funciona igual al descrito en el ejemplo del ciclo FOR.



Base de Datos


Funciones.Ejemplo 1 : Buscar una Ciudad y retornar si existe o no

CREATE OR REPLACE FUNCTION buscar_ciudad (varchar)

RETURNS bool AS '

DECLARE

rut_buscar alias for $1;

registro ciudades%ROWTYPE;

/* Nótese que aquí definimos la variable registro del tipo FILA de ciudades

indicando la tabla, el símbolo % y luego la palabra reservada ROWTYPE */

BEGIN

SELECT INTO registro * FROM ciudades

WHERE ciudad = rut_buscar;

IF FOUND THEN

RETURN true;

END IF;

RETURN false;

END;


Como podemos apreciar, este código busca un cliente en la tabla de clientes, si

existe o se encuentra el cliente, la función devolverá un valor verdadero (true), de lo

contrario, ésta devolverá falso (false)



Base de Datos


Funciones.Ejemplo 2 : Buscar un producto y actualiza su precio según porcentaje

CREATE OR REPLACE FUNCTION actualizar_producto (varchar, real)

RETURNS bool AS '

DECLARE

producto ALIAS FOR $1;

porcentaje ALIAS FOR $2;

registro productos%ROWTYPE;

BEGIN

SELECT INTO registro * FROM productos

WHERE id_producto = producto;

IF FOUND THEN

UPDATE productos SET

precio_venta = precio_venta + (precio_venta * porcentaje)

WHERE id_categoria = categoria;

RETURN true;

END IF;

RETURN false;

END;

' LANGUAGE 'plpgsql„;

Este código busca el producto, obtiene los datos necesarios y actualiza el registro.



Base de Datos


Funciones.Ejemplo 3 : Incrementar valores según el porcentaje de IVA

CREATE OR REPLACE FUNCTION inc_iva (integer, real)

RETURNS integer AS '

DECLARE

valor ALIAS FOR $1;

iva ALIAS FOR $2;

total real;

BEGIN

total := valor + (valor * iva);

RETURN total;

RETURN;

END;


Esta función no actualiza realmente los datos de la tabla, solamente procesa los

parámetros, de tal manera que si los datos ingresados fueran los de una tabla

devolvería el valor mas IVA.



Base de Datos


Funciones.LLAMANDO A LAS FUNCIONES

Hasta ahora hemos descrito a grandes rasgos como construir fácilmente

funciones de usuario o procedimientos almacenados, pero todavía no los

hemos invocado, a continuación ejemplos de llamadas a los

procedimientos almacenados o funciones en PL/pgSQL :

Este ejemplo retornará true si la ciudad existe o false si no se encuentra en

la tabla de clientes (nota, no hay que indicar en que tabla pues eso esta

descrito en la función).

SELECT buscar_ciudad('Mexico');

Para ejecutar la función de SUMA:

SELECT SUMA(4,5);



Base de Datos


Disparadores (triggers).

Es una acción definida en una tabla de

nuestra base de datos y ejecutada

automáticamente por una función

programada por nosotros. Esta acción se

activará, según la definamos, cuando

realicemos un INSERT, un UPDATE ó un

DELETE en la susodicha tabla.



Base de Datos


Disparadores (triggers).Un disparador se puede definir de las siguientesmaneras:

Para que ocurra ANTES de cualquier INSERT,UPDATE ó DELETE

Para que ocurra DESPUES de cualquier INSERT,UPDATE ó DELETE

Para que se ejecute una sola vez por comando SQL(statement-level trigger)

Para que se ejecute por cada línea afectada por uncomando SQL (row-level trigger)



Base de Datos



Esta es la definición del comando SQL que se puede

utilizar para definir un disparador en una tabla.

CREATE TRIGGER nombre { BEFORE | AFTER }

{ INSERT | UPDATE | DELETE [ OR ... ] }

ON tabla [ FOR [ EACH ] { ROW | STATEMENT }]

EXECUTE PROCEDURE nombre de función (

argumentos )



Base de Datos



NOTA: Antes de definir el disparador tendremos que

definir el procedimiento almacenado que se ejecutará

cuando nuestro disparador se active.

El procedimiento almacenado usado por nuestro

disparador se puede programar en cualquiera de los

lenguajes de procedimientos disponibles, entre ellos,

el proporcionado por defecto cuando se instala

PostgreSQL, PL/pgSQL. Este lenguaje es el que

utilizaremos.



Base de Datos


Disparadores (triggers).Creamos una base de datos para utilizarla connuestros ejemplos.

CREATE TABLE numeros(

numero bigint NOT NULL,

cuadrado bigint,

cubo bigint,

raiz2 real,

raiz3 real,

PRIMARY KEY (numero)

);



Base de Datos


Disparadores (triggers).Después tenemos que crear una función en PL/pgSQLpara ser usada por nuestro disparador. Nuestra primera función es la más simple que se puede definir y lo único que hará será devolver el valor NULL:

CREATE OR REPLACE FUNCTION proteger_datos() RETURNS TRIGGER AS $proteger_datos$

DECLARE

BEGIN

-- Esta función es usada para proteger datos en una tabla

-- No se permitirá el borrado de filas si la usamos

-- en un disparador de tipo BEFORE / row-level

RETURN NULL;

END;

$proteger_datos$ LANGUAGE plpgsql;



Base de Datos



A continuación definimos en la tabla numeros un disparador del tipo

BEFORE / row-level para la operación DELETE.

CREATE TRIGGER proteger_datos BEFORE DELETE ON

numeros FOR EACH ROW

EXECUTE PROCEDURE proteger_datos();



Base de Datos


Disparadores (triggers).Ahora vamos a definir una nueva función un poco más complicada y un nuevo

disparador en nuestra tabla numeros:

CREATE OR REPLACE FUNCTION rellenar_datos() RETURNS TRIGGER AS

$rellenar_datos$

DECLARE

BEGIN

NEW.cuadrado := power(NEW.numero,2);

NEW.cubo := power(NEW.numero,3);

NEW.raiz2 := sqrt(NEW.numero);

NEW.raiz3 := cbrt(NEW.numero);

RETURN NEW;

END;

$rellenar_datos$ LANGUAGE plpgsql;

CREATE TRIGGER rellenar_datos BEFORE INSERT OR UPDATE

ON numeros FOR EACH ROW

EXECUTE PROCEDURE rellenar_datos();



Base de Datos


Disparadores (triggers).Ahora vamos a ver como los disparadores que hemos definido en la tabla

numeros funcionan:

SELECT * from numeros;

numero | cuadrado | cubo | raiz2 | raiz3

-----------+-------------+-------+-------+-------

INSERT INTO numeros (numero) VALUES (2);



------------+-------------+-------+-------------+---------

2 | 4 | 8 | 1.41421 | 1.25992

Cuando ejecutamos el INSERT (numero = 2), el disparador

rellenar_datos llama a la función rellenar_datos() una vez.



Base de Datos


Disparadores (triggers). El valor de la variable NEW al empezar a ejecutarse rellenar_datos() es

numero=2, cuadrado=NULL, cubo=NULL, raiz2=NULL, raiz3=NULL.

Nuestra tabla todavía no contiene ninguna fila.

A continuación calculamos el cuadrado, el cubo, la raíz cuadrada y la

raíz cúbica de 2 y asignamos estos valores a NEW.cuadrado,

NEW.cubo, NEW.raiz2 y NEW.raiz3.

El valor de la variable NEW antes de la sentencia RETURN NEW es

ahora numero=2, cuadrado=4, cubo=8, raiz2=1.41421, raiz3=1.25992.

Con la sentencia RETURN NEW, retornamos la fila (RECORD)

almacenada en la variable NEW, y salimos de la función

rellenar_datos(). El sistema almacena entonces el RECORD contenido

en NEW en la tabla numeros



Base de Datos


Disparadores (triggers).INSERT INTO numeros (numero) VALUES (3);



-----------+-------------+-------+-------------+---------

2 | 4 | 8 | 1.41421 | 1.25992

3 | 9 | 27 | 1.73205 | 1.44225

De la misma manera funciona el disparador proteger_datos cuando

ejecutamos una sentencia DELETE. Antes de borrar nada ejecutará la

función proteger_datos().

Esta función retorna el valor NULL y esto significa, según la regla que

hemos definido, que para la fila afectada no se ejecutará el comando

DELETE. Por eso y mientras este disparador este instalado será

imposible de borrar nada de la tabla numeros.



Base de Datos


Disparadores (triggers).Ahora vamos a ver como los disparadores que hemos definido en la

tabla numeros funcionan:

UPDATE numeros SET numero = 4 WHERE numero = 3;



-----------+-------------+-------+-------------+---------

2 | 4 | 8 | 1.41421 | 1.25992

4 | 16 | 64 | 2 | 1.5874



Base de Datos


Disparadores (triggers).Hemos realizado 2 INSERT y 1 UPDATE. Esto significa que por

cada uno de estos comandos el sistema ha ejecutado la función

rellenar_datos(), una vez por cada fila afectada y antes de

actualizar la tabla numeros.

Como podemos comprobar, nosotros solamente hemos

actualizado la columna numero, pero al listar el contenido de

nuestra tabla vemos como el resto de columnas (cuadrado,

cubo, raiz2 y raiz3) también contienen valores.

De esta actualización se ha encargado la función

rellenar_datos() llamada por nuestro disparador.



Base de Datos


Disparadores (triggers).Para eliminar/borrar un trigger, tenemos la siguiente sentencia:

DROP TRIGGER nombre ON tabla [ CASCADE | RESTRICT ]

CASCADE

Elimina automáticamente el borrado de objetos que dependen del trigger.

RESTRICT

Se niegan a eliminar el trigger si los objetos dependen de él. Este es el predeterminado.

DROP TRIGGER proteger_datos ON numeros;

DROP TRIGGER rellenar_datos ON numeros;



Base de Datos


Disparadores (triggers).A continuación crearemos un disparador único para las sentencias INSERT,UPDATE y DELETE. Para ello utilizaremos la variable TG_OP.

CREATE OR REPLACE FUNCTION proteger_y_rellenar_datos() RETURNS TRIGGER AS $proteger_y_rellenar_datos$

DECLARE

BEGIN

IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE' ) THEN

NEW.cuadrado := power(NEW.numero,2);

NEW.cubo := power(NEW.numero,3);

NEW.raiz2 := sqrt(NEW.numero);

NEW.raiz3 := cbrt(NEW.numero);

RETURN NEW;

ELSEIF (TG_OP = 'DELETE') THEN

RETURN NULL;

END IF;

END;

$proteger_y_rellenar_datos$ LANGUAGE plpgsql;



Base de Datos



CREATE TRIGGER proteger_y_rellenar_datos BEFORE INSERT

OR UPDATE OR DELETE

ON numeros FOR EACH ROW

EXECUTE PROCEDURE proteger_y_rellenar_datos();



Base de Datos



Ahora, todo seguirá funcionando de la misma manera que con los

dos disparadores del comienzo:





Base de Datos



Ahora vamos a definir un disparador del tipo statement-level quese ejecute después de nuestras sentencias INSERT, UPDATE yDELETE. La función ejecutada por este disparador grabarádatos de la ejecución en la tabla cambios.

Para demostrar cómo podemos utilizar esto vamos a definir unanueva tabla:

CREATE TABLE cambios(

timestamp_ TIMESTAMP WITH TIME ZONE default NOW(),

nombre_disparador text,

tipo_disparador text,

nivel_disparador text,

comando text

);



Base de Datos



La función la podemos definir así:

CREATE OR REPLACE FUNCTION grabar_operaciones() RETURNS TRIGGER AS $grabar_operaciones$

DECLARE

BEGIN

INSERT INTO cambios (

nombre_disparador,

tipo_disparador,

nivel_disparador,

comando)

VALUES (

TG_NAME,

TG_WHEN,

TG_LEVEL,

TG_OP

);

RETURN NULL;

END;

$grabar_operaciones$ LANGUAGE plpgsql;



Base de Datos



Y el disparador lo crearíamos de la siguiente forma:

CREATE TRIGGER grabar_operaciones AFTER INSERT OR

UPDATE OR DELETE

ON numeros FOR EACH STATEMENT

EXECUTE PROCEDURE grabar_operaciones();



Base de Datos


Disparadores (triggers).A continuación podemos ver como funcionaría:

Revisamos que la tabla este vacía:

SELECT * from cambios ;

Ingresamos un valor:


Verificamos que se haya insertado en la Bitácora:




Base de Datos

Introducción a PostgreSQL

Documents

Transcript of Introducción a PostgreSQL