PL/PGSQL. Extensión "procedural" de SQL

(1)

PL/PGSQL

(2)

Pero 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;

(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> );

(4)

Extensiones 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

(5)

Por 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

(6)

Un 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}

(7)

PL/pgSQL: Estructura de las

funciones

_funciones

■ _{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;

(8)

PL/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}

(9)

PL/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)

(10)

Ejemplo 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)

(11)

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:

(12)

Tipos 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;

(13)

Ejemplo 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

(14)

Má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;

(15)

Ejemplo 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';

(16)

Ejercicio

■ _{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),

(17)

Control 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

(18)

Ejemplo 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}

(19)

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;

(20)

Excepciones

■ _{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_;

(21)

Excepciones: 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

(22)

SELECT 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}

(23)

SELECT 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;

(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

(25)

TABLAS

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),

(26)

DATOS

--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);

(27)

Ejemplo función en C

(28)

Ejemplo 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

(29)

Ejemplo 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}

(30)