Clase_5_PL_pgSQL_Parte_2

Vista previa del material en texto

PL/PGSQLPL/PGSQL
Extensión "procedural" de SQL
6.2
Pero primero triggersPero primero triggers
DROP TABLE producto cascade;
CREATE TABLE producto( id int PRIMARY KEY, vendido timestamp);
DROP FUNCTION modificacion() cascade;
CREATE FUNCTION modificacion() RETURNS TRIGGER AS '
BEGIN
 NEW.vendido := ''now'';
 RETURN NEW; 
END;
'LANGUAGE 'plpgsql'; 
DROP TRIGGER t_modificacion on producto;
CREATE TRIGGER t_modificacion BEFORE INSERT ON producto
FOR EACH ROW EXECUTE PROCEDURE modificacion();
INSERT into producto VALUES (1);
SELECT * FROM producto;
6.3
Trigger Layout
CREATE TRIGGER <trigger_name>
BEFORE
AFTER
INSERT
UPDATE
DELETE
OR
ON<table_name>
FOR EACH ROW
FOR EACH STATEMENT
EXECUTE PROCEDURE <function_name> ( );<arguments>
OLD NEW
6.4
Extensiones SQL-99Extensiones SQL-99
■ Entre las propuestas definidas en el estándar SQL-99 
están la extensión del SQL dotándolo de comandos 
para controlar el flujo:
★If, where, loop, etc
■ El standard SQL-99 esta bastante lejos de ser 
satisfecho por la mayoría de las bases de datos.
■ Oracle tiene una versión propia de estas extensiones 
"procedurales" llamada PL/SQL
■ PostgreSQL tiene su versión llamada PL/pgSQL
6.5
Por qué PL/pgSQLPor qué PL/pgSQL
■ Permite crear funciones que se ejecutan en el 
servidor (versus otras aproximaciones como JDBC 
que se ejecutan en el cliente con "overhead" de 
comunicaciones).
■ La propia base de datos se encarga de compilar y 
gestionar estas funciones con lo que suelen ser 
eficientes.
■ proporciona:
★ variables
★ bucles
★ evaluación condicional
6.6
Un paso previo a usar PL/pgSQLUn paso previo a usar PL/pgSQL
■ Cuando se crea una base de datos nueva hace falta "autorizar" 
el uso de pl/pgSQL (a menos que template1 ya este autorizada)
createdb mydatab
createlang plpgsql mydatab
■ En los laboratorios debería estar "autorizado" por defecto
■ Ojo: no se comprueba la sintaxis de las funciones hasta que no 
son ejecutadas. (Es difícil depurar el código)
6.7
PL/pgSQL: Estructura de las PL/pgSQL: Estructura de las funcionesfunciones
■ PL/pgSQL presenta una estructura en "Bloques".
■ Cada bloque se define usando
DECLARE
 --variables inicializada con NULL cada vez
 --que se entra en el bloque
 [...]
BEGIN
 --comandos;
 [...]
END;
■ No se pueden definir transacciones dentro de una función
6.8
PL/pgSQL estructuraPL/pgSQL estructura
CREATE FUNCTION nombre_función (argumentos) 
RETURNS type AS '
 DECLARE
 declaracion; --variables
 [...]
 BEGIN
 statement; --comandos
 [...]
 END;
' LANGUAGE 'plpgsql';
■ Una función puede constar de varios bloques y estos pueden 
estar anidados
6.9
PL/pgSQL: GeneralidadesPL/pgSQL: Generalidades
■ Los tipos de datos pasados a la función se dan en paréntesis 
(sin nombre de variable antes de postgres 8)
■ El cuerpo de la función se pasa a la base de datos como una 
cadena de caracteres (nótese, que el cuerpo empieza y acaba 
con comillas simples) (escape doble)
■ Tras la cadena el lenguaje usado para crear la función se define 
usando la orden "LANGUAJE" (otros lenguajes posibles son 
PL/PERL, PL/TCL, C, etc)
6.10
Ejemplo trivial sin pasar parámetrosEjemplo trivial sin pasar parámetros
■ ¿Qué hace esta función?
CREATE OR REPLACE FUNCTION una_funcion () RETURNS 
int4 AS 
' DECLARE 
an_integer int4; --variables
BEGIN 
an_integer := 10 * 10; --comandos
RETURN an_integer;
END; 
' LANGUAGE 'plpgsql'; 
____________________________
 select una_funcion();
 una_funcion 
------------
 100
(1 row)
6.11
Tipos de Variables-I Tipos de Variables-I 
■ Ejemplos de variables:
id_usuario INTEGER;
cantidad NUMERIC(5,2);
url VARCHAR;
-- Mas sobre los tipos siguientes más adelante
micampo mitabla.campo%TYPE; 
mitupla mitabla%ROWTYPE;
■ The general syntax of a variable declaration is:
name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } 
expression ];
6.12
Tipos de Variables IITipos de Variables II
■ Todos los tipos de variable definidos para SQL son válidos en 
PL/pgSQL
■ No es imprescindible conocer el tipo de variables de los atributos
★ Ejemplos usando %TYPE 
DECLARE … mivar payroll.salario%TYPE;
BEGIN … RETURN mivar*2;
★ %ROWTYPE reserva sitio para toda la tupla
★ Reteniendo la estructura de los datos
DECLARE … mivar payroll%ROWTYPE;
BEGIN … RETURN mivar.salario*2;
6.13
Ejemplo trivial pasando variablesEjemplo trivial pasando variables
CREATE OR REPLACE FUNCTION cal_longitud (text) 
RETURNS int4 AS 
' DECLARE 
intext ALIAS FOR $1; --primer parametro
 resultado int4; 
BEGIN 
resultado := (SELECT LENGTH(intext));
RETURN resultado;
END; 
' LANGUAGE 'plpgsql'; 
____________________________
SELECT cal_longitud('qwerty');
 cal_longitud 
--------------
 6
(1 row)
6.14
Más sobre VariablesMás sobre Variables
■ CREATE FUNCTION mifuncion(INTEGER, CHAR, …)
■ Se pueden pasar hasta 16 variables
★ $1, $2, …, $16
■ ALIAS permite renombrar variables
CREATE FUNCTION cal_longitud (text) RETURNS int4 
AS 
' DECLARE 
intext ALIAS FOR $1; --primer parametro
 resultado int4;
. . .
6.15
Ejemplo usando RowtypeEjemplo usando Rowtype
CREATE OR REPLACE FUNCTION trae_pelicula (integer) 
RETURNS text AS ' 
DECLARE 
pelicula_id ALIAS FOR $1;
encontrada_pelicula pelicula%ROWTYPE;
BEGIN
SELECT INTO encontrada_pelicula * FROM pelicula 
WHERE id = pelicula_id;
RETURN encontrada_pelicula.titulo || '' ('' || 
encontrada_pelicula.agno || '')'';
END; 
' LANGUAGE 'plpgsql'; 
■ Nota: Si SELECT INTO devuelve más de una tupla se ignoran 
todas menos la primera (la solución a esto más tarde)
6.16
EjercicioEjercicio
■ En la base de datos de películas crear dos funciones que nos 
sirvan para llenar la tabla reparto, dando el nombre de la 
pelicula y el nombre del actor 
CREATE TABLE PELICULA(
ID INTEGER, -- Identificador único
TITULO CHAR(70), -- Titulo de la película
AGNO DECIMAL(4), -- Año de estreno
PUNTUACION FLOAT, -- Puntuación media
VOTOS INTEGER, -- Numero de votos
PRIMARY KEY (ID));
--
CREATE TABLE ACTOR (
ID INTEGER, -- Identificador Único
NOMBRE CHAR(35), -- Nombre del actor/actriz
PRIMARY KEY (ID));
--
CREATE TABLE REPARTO(
PELICULA_ID INTEGER, -- referencia a la tabla PELICULA
ACTOR_ID INTEGER, -- referencia a la tabla ACTOR_ID
ORD INTEGER, -- Orden en el reparto
 -- La estrella es 1, ...
--
FOREIGN KEY (PELICULA_ID ) REFERENCES PELICULA(ID),
FOREIGN KEY (ACTOR_ID) REFERENCES ACTOR(ID),
PRIMARY KEY (PELICULA_ID, ACTOR_ID));
6.17
Control de FlujoControl de Flujo
■ Los programas no suelen ejecutarse de principio a fin sin 
exceptuar ninguna línea de código. PL/pgSQL contiene 
estructuras de control que permiten seleccionar las líneas de 
código que serán ejecutarse en tiempo real.
■ IF…THEN…ELSE…ELSE IF
★ ejecución condicional
■ LOOPS, WHILE LOOPS, FOR LOOPS
★ iteraciones
★ bucles
6.18
Ejemplo IF/ELSEEjemplo IF/ELSE
■ Programa que calcula la longitud de dos cadenas y devuelve la 
longitud mayor.
CREATE OR REPLACE FUNCTION cadena_mas_larga(text, 
text) RETURNS int4 AS '
DECLARE
 in_uno ALIAS FOR $1;
 in_dos ALIAS FOR $2;
 lon_uno int4;
 lon_dos int4;
 result int4;
BEGIN
 lon_uno := (SELECT LENGTH(in_uno));
 lon_dos := (SELECT LENGTH(in_dos));
 IF lon_uno > lon_dos THEN RETURN lon_uno;
 ELSE RETURN lon_dos;
 END IF;
END;
'LANGUAGE 'plpgsql';
■ NOTA 1: se pueden hacer condiciones mas 
complicadas usando OR y AND
■ NOTA 2: Como PL/pgSQL se agrupa en bloques no 
hacen falta paréntesis en torno a IF
6.19
Ejemplo bucle WHILE Ejemplo bucle WHILE (FOR)(FOR)
■ Función que cuenta cuantas veces aparece un carácter en una cadena
CREATE OR REPLACE FUNCTION cuentac(text,text) RETURNS 
INT4 AS '
DECLARE
intext ALIAS FOR $1; inchar ALIAS FOR $2;
lon int4; resultado int4; 
i int4; tmp char;
BEGIN
lon := length(intext)+1; i:=1;
resultado:=0;
WHILE i <= lon LOOP
tmp := substr(intext,i,1);IF tmp = inchar THEN
resultado := resultado +1;
END IF;
i:=i+1;
END LOOP;
RETURN resultado;
END
' LANGUAGE 'plpgsql';
-- SELECT cuentac('qwertytq','q');
6.20
ExcepcionesExcepciones
■ RAISE se usa para imprimir mensajes y, en el caso de 
excepcion, abortar la transacción
■ RAISE { NOTICE | EXCEPTION}
■ RAISE NOTICE
★ RAISE NOTICE ' No hagas eso!' ';
★ RAISE NOTICE ' 'El señor' ' || id || ' 'no está en casa' ';
★ RAISE NOTICE ' 'el señor % no está en casa' ' , id;
6.21
Excepciones: EjemploExcepciones: Ejemplo
■ Calcular la suma de los enteros de n a m (usar la formula (p+1)*p/2
CREATE OR REPLACE FUNCTION suma(int4, int4) RETURNS int4 AS '
DECLARE
inicio ALIAS FOR $1; fin ALIAS FOR $2;
 resultado int;
BEGIN
IF (inicio <1) THEN
 RAISE EXCEPTION ''inicio debe ser mayor que 1'';
ELSE
IF(inicio <= fin) THEN
 resultado := (fin+1)*fin/2 -
 (inicio-1)*inicio/2;
 ELSE
RAISE EXCEPTION ''El valor inicial % debe ser menor que el 
final %'', inicio, fin;
 END IF;
 END IF;
RETURN resultado;
END
' LANGUAGE 'plpgsql';
SELECT suma(1,3);
$$
6.22
SELECT y BuclesSELECT y Bucles
CREATE OR REPLACE FUNCTION trae_pelicula (integer) 
RETURNS text AS ' 
DECLARE 
pelicula_id ALIAS FOR $1;
encontrada_pelicula pelicula%ROWTYPE;
BEGIN
SELECT INTO encontrada_pelicula * FROM pelicula 
WHERE id = pelicula_id;
RETURN encontrada_pelicula.titulo || '' ('' || 
encontrada_pelicula.agno || '')'';
■ Nota: Si SELECT INTO devuelve más de una tupla se ignoran 
todas menos la primera (la solución a esto más tarde)
6.23
SELECT y BuclesSELECT y Bucles
■ Cuantas tuplas empiezan con una letra determinada
CREATE OR REPLACE FUNCTION cuenta_letra (text) RETURNS int4 AS 
' 
DECLARE 
caracter ALIAS FOR $1; temporal record;
tmp_caracter text; resultado int4;
BEGIN
resultado:=0;
FOR temporal IN SELECT titulo FROM pelicula LOOP
tmp_caracter :=substr(temporal.titulo,1,1);
IF tmp_caracter = caracter THEN
resultado := resultado +1;
END IF;
END LOOP;
RETURN resultado;
END;
'LANGUAGE 'plpgsql';
SELECT cuenta_letra('A');
6.24
Examen Parcial Nov
PERSONA
OBJETO
PUJA
Subastado por
Persona
Puja
Puja
Objeto
• Rechazar pujas no admisibles
• 10% menos que puja anterior
• Un día más tarde
• Actualizar campos redundantes
• vendido
6.25
TABLASTABLAS
CREATE TABLE persona(
 id SERIAL, --identificador unico
 PRIMARY KEY(id)
);
CREATE TABLE objeto(
 id SERIAL, --identificador unico
 persona_id INT,
 fecha_salida TIMESTAMP DEFAULT now(),
 vendido int DEFAULT 0, -- 1 vendido, 0 no vendido
 precio_salida NUMERIC(10,2),
 PRIMARY KEY(id),
 FOREIGN KEY (persona_id) REFERENCES persona(id)
);
CREATE TABLE puja(
 objeto_id INT,
 persona_id INT,
 fecha TIMESTAMP,
 cuantia NUMERIC(10,2),
 FOREIGN KEY (objeto_id) REFERENCES objeto(id),
 FOREIGN KEY (persona_id) REFERENCES persona(id),
 PRIMARY KEY(persona_id,objeto_id,fecha)
);
6.26
DATOSDATOS
--poblar la tabla
--
--persona (id)
--
INSERT INTO persona VALUES (1);--
INSERT INTO persona VALUES (2);--
INSERT INTO persona VALUES (3);--
INSERT INTO persona VALUES (4);--
INSERT INTO persona VALUES (5);--
--
--objeto(id, persona_id,fecha_salida,vendido,precio_salida)
--
INSERT INTO objeto VALUES (1,1,now(),DEFAULT,23);--
INSERT INTO objeto VALUES (2,1,now()+'-1.1 day',DEFAULT,23);
INSERT INTO objeto VALUES (3,3,now()+'-3.14 day',DEFAULT,23);
INSERT INTO objeto VALUES (4,3,now()+'-2.9 day',DEFAULT,23);
INSERT INTO objeto VALUES (5,3,now()+'-5.9 day',DEFAULT,23);
INSERT INTO objeto VALUES (6,4,now()+'-5.9 day',DEFAULT,23);
INSERT INTO objeto VALUES (7,4,now()+'-0.9 day',DEFAULT,23);
INSERT INTO objeto VALUES (8,5,now()+'-0.8 day',DEFAULT,23);
INSERT INTO objeto VALUES (9,5,now()+'-1.34 day',DEFAULT,23);
INSERT INTO objeto VALUES (10,5,now()+'-5.9 day',DEFAULT,23);
--
--puja (objeto_id,persona_id,fecha)
--
INSERT INTO puja VALUES (1,1,now()+'1 hour',10.34);
INSERT INTO puja VALUES (1,1,now()+'2 hour',10.34);
INSERT INTO puja VALUES (1,1,now()+'3 hour',10.34);
INSERT INTO puja VALUES (1,1,now()+'4 hour',10.34);
INSERT INTO puja VALUES (1,1,now()+'5 hour',10.34);
INSERT INTO puja VALUES (1,1,now()+'10 hour',10.34);
INSERT INTO puja VALUES (10,1,now()+'1 hour',10.34);
INSERT INTO puja VALUES (9,1,now()+'1 hour',10.34);
 
Ejemplo función en CEjemplo función en C
6.28
Ejemplo de Función en CEjemplo de Función en C
■ Código
 
#include "postgres.h"
#include <string.h>
/* by value */
 
int add_one(int arg)
{
 return arg + 1;
}
■ Compilación
gcc -fpic -c foo.c -I /usr/include/pgsql/server/
gcc -shared -o foo.so foo.o
6.29
Ejemplo de Función en C-IIEjemplo de Función en C-II
■ Instalacion:
★ como istrador de la base (postges):
CREATE FUNCTION add_one(integer) RETURNS integer
 AS '/tmp/foo.so', 'add_one'
 LANGUAGE C STRICT;
_______________________________
■ add_one.so es la librería dinámica con la función
■ Más información en: 
http://developer.postgresql.org/docs/postgres/xfunc-c.html
■ Recordar que C no soporta todos los tipos usados 
en SQL (y viceversa)
http://developer.postgresql.org/docs/postgres/xfunc-c.html
 
Se acabóSe acabó
	Slide 1
	Slide 2
	Slide 3
	Slide 4
	Slide 5
	Slide 6
	Slide 7
	Slide 8
	Slide 9
	Slide 10
	Slide 11
	Slide 12
	Slide 13
	Slide 14
	Slide 15
	Slide 16
	Slide 17
	Slide 18
	Slide 19
	Slide 20
	Slide 21
	Slide 22
	Slide 23
	Slide 24
	Slide 25
	Slide 26
	Slide 27
	Slide 28
	Slide 29
	Slide 30