PlPgSql

PL/PGSQL
Extensión "procedural" de SQL

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_modificacionBEFORE INSERT ON producto
FOR EACH ROW EXECUTE PROCEDURE modificacion();
INSERT into producto VALUES (1);
SELECT * FROM producto;

6.2

Trigger Layout
INSERT

BEFORE
CREATE TRIGGER



UPDATE
AFTER

DELETE

OLD NEW

OR

FOR EACH ROW


ON

FOR EACH STATEMENT

EXECUTE PROCEDURE



6.3

(



);

Extensiones SQL-99
■ Entre las propuestas definidas en elestá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.4

Por qué PL/pgSQL
■ Permite crearfunciones 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.5

Un paso previo a usar PL/pgSQL
■ Cuando se crea una base dedatos 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.6

PL/pgSQL: Estructura de las funciones
■ PL/pgSQL presenta unaestructura 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.7

PL/pgSQL estructura
CREATE FUNCTION nombre_función (argumentos)
RETURNS type AS '
DECLARE
declaracion;

--variables

[...]
BEGINstatement;

--comandos

[...]
END;
' LANGUAGE 'plpgsql';
■ Una función puede constar de varios bloques y estos pueden

estar anidados
6.8

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 yacaba
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.9

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

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 generalsyntax of a variable declaration is:

name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := }
expression ];

6.11

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 …
BEGIN

…

mivar payroll.salario%TYPE;
RETURN mivar*2;

★...