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
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
Ahora probaremos un poco la terminal
interactiva llamada psql: para probarla
teclee las siguientes consultas:
SELECT version();
SELECT current_date;
SELECT 2 + 2;
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
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
);
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
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;
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
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');
Facultad de Ingeniería
Enrique Felipe Anastacio
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;
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
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
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
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;
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
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;
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
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';
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
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);
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
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);
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
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:
CREATE TABLE ciudades (
ciudad varchar(80) primary key,
localizacion point
);
CREATE TABLE climas (
ciudad varchar(80) references ciudades(ciudad),
temp_baja int,
temp_alta int,
prcp real,
fecha date);
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
Llave Primaria (PK) y Foránea (FK)Para crear las PK‟s y FK‟s a tablas yaexistentes, usamos la siguiente sentencia:
CREATE TABLE ciudades (
ciudad varchar(80) primary key,
localizacion point
);
ALTER TABLE ciudades
ADD CONSTRAINT ciudad_pkPRIMARY KEY (ciudad);
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
Llave Primaria (PK) y Foránea (FK)
CREATE TABLE climas (
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;
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
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
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
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
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
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
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
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.
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL
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;
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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.
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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.
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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.
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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.
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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.
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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 :
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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';
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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.
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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;
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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 :
FUNCTION suma ( real , real )
RETURNS real AS '
DECLARE
numero_1 alias for $1;
numero_2 alias for $2;
valor_total real;
BEGIN
valor_total := numero_1 + numero_2;
numero_1 := numero_1 * numero_2;
...resto de la funcion...
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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 :
...resto de la función...
RETURN <variable>;
...resto de la función...
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
Funciones.
RETORNANDO EL UN VALOR COMO FUNCION
Ejemplo completo de la función SUMA :
FUNCTION suma ( real , real )
RETURNS real AS '
DECLARE
numero_1 alias for $1;
numero_2 alias for $2;
valor_total real;
BEGIN
valor_total := numero_1 + numero_2;
RETURN valor_total;
END;
' LANGUAGE 'plpgsql';
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
Funciones.
UTILIZANDO SENTENCIAS DE CONTROL
SENTENCIA DE CICLO LOOP :
Esta sentencia nos permite efectuar un ciclo, su estructura básica es :
...resto de la función...
LOOP
...sentencias a ejecutar en el ciclo...
END LOOP
...resto de la función...
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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
...resto de la función...
contador := contador + 1;
IF contador >= 10 THEN
EXIT; -- salida del loop cuando sea mayor o igual que 10
END IF;
END LOOP
...resto de la función...
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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 ...
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
Funciones.
SENTENCIAS IF, ELSE :
Ejemplo :
...resto de la función...
IF >= 10 or THEN
RETURN true;
ELSE
RETURN false;
END IF;
...resto de la función...
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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
... SENTENCIAS A EJECUTAR DENTRO DEL CICLO FOR ...
END LOOP;
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
Funciones.SENTENCIA DE CICLO FOR :
CASO 1 : SENTENCIA FOR DE PROCESO CICLICO NUMERICO
Ejemplo :
...resto de la función...
FOR inc IN 1..10 LOOP
factorial := factorial * inc;
END LOOP;
RETURN factorial;
...resto de la función…
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
Funciones.
SENTENCIA DE CICLO FOR :
CASO 2: SENTENCIA FOR APLICADA A PROCESOS DE
REGISTROS SQL
FOR <registro o fila> IN <sentencia SELECT SQL> LOOP
... SENTENCIAS A EJECUTAR DENTRO DEL CICLO FOR ...
END LOOP;
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
Funciones.SENTENCIA DE CICLO FOR :
Ejemplo :
...resto de la función...
DECLARE
registro RECORD;
BEGIN
FOR registro IN SELECT * FROM productos LOOP
stock := registro.sock_actual + 100;
... otras sentencias ...
END LOOP;
...resto de la función...
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.
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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;
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
Funciones.
SENTENCIA DE CICLO CONDICIONAL WHILE
Ejemplo :
...resto de la función...
WHILE inc <= 10 LOOP
factorial := factorial * inc;
END LOOP;
RETURN factorial;
...resto de la función...
Este código funciona igual al descrito en el ejemplo del ciclo FOR.
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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;
' LANGUAGE 'plpgsql';
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)
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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.
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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;
' LANGUAGE 'plpgsql';
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.
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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);
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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.
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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)
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
Disparadores (triggers).
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 )
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
Disparadores (triggers).
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.
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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)
);
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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;
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
Disparadores (triggers).
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();
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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();
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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);
SELECT * from numeros;
numero | cuadrado | cubo | raiz2 | raiz3
------------+-------------+-------+-------------+---------
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.
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
Disparadores (triggers).INSERT INTO numeros (numero) VALUES (3);
SELECT * from numeros;
numero | cuadrado | cubo | raiz2 | raiz3
-----------+-------------+-------+-------------+---------
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.
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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;
SELECT * from numeros;
numero | cuadrado | cubo | raiz2 | raiz3
-----------+-------------+-------+-------------+---------
2 | 4 | 8 | 1.41421 | 1.25992
4 | 16 | 64 | 2 | 1.5874
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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.
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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;
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
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;
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
Disparadores (triggers).
CREATE TRIGGER proteger_y_rellenar_datos BEFORE INSERT
OR UPDATE OR DELETE
ON numeros FOR EACH ROW
EXECUTE PROCEDURE proteger_y_rellenar_datos();
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
Disparadores (triggers).
Ahora, todo seguirá funcionando de la misma manera que con los
dos disparadores del comienzo:
INSERT INTO numeros (numero) VALUES (5);
INSERT INTO numeros (numero) VALUES (6);
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
Disparadores (triggers).
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
);
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
Disparadores (triggers).
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;
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
Disparadores (triggers).
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();
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos
Introducción a PostgreSQL.
Disparadores (triggers).A continuación podemos ver como funcionaría:
Revisamos que la tabla este vacía:
SELECT * from cambios ;
Ingresamos un valor:
INSERT INTO numeros (numero) VALUES (100);
Verificamos que se haya insertado en la Bitácora:
SELECT * from numeros;
Facultad de Ingeniería
Enrique Felipe Anastacio
Base de Datos