6. Funciones y disparadores

6. Funciones y disparadores Dataprix 26 Octubre, 2009 - 22:37

Como algunos de los gestores de bases de datos relacionales, comerciales líderes en el mercado, PostgreSQL puede incorporar múltiples lenguajes de programación a una base de datos en particular. Este hecho permite, por ejemplo:
 

PL/pgSQL
PL/pgSQL (procedual language/ postgreSQL) es una extensión del SQL que permite la creación de procedimientos y funciones al estilo de los lenguajes tradicionales de programación.

•    Almacenar procedimientos en la base de datos (stored procedure), que podrán lanzarse cuando convenga.

•    Definir operadores propios.

PostgreSQL ofrece por defecto soporte para su propio lenguaje procedural, el PL/pgSQL. Para instalarlo, basta con invocar el comando createlang desde el sistema operativo, no desde la línea de psql.

 

Mediante este comando, se ha          
instalado el lenguaje PL/pgSQL en    la base de datos demo.                       


$ createlang plpgsql
demo

PostgreSQL también soporta otros lenguajes directamente, como PL/Tcl, PL/ Perl y PL/Python.

 

6.1. Primer programa

6.1. Primer programa Dataprix 26 Octubre, 2009 - 22:48

Veamos el programa HolaMundo en PL/pgSQL:


demo=# create function HolaMundo() returns char
demo-# as ` begin return ´ "Hola Mundo PostgreSQL" ; end; `
demo-# language
`plpgsql´;
CREATE

La función tiene tres partes:

•    El encabezado que define el nombre de la función y el tipo de retorno.

•    El cuerpo de la función, que es una cadena de texto (por lo tanto, siempre va entre comillas dobles).

•    La especificación del lenguaje utilizado.

La función recien creada tiene las mismas características que las integradas.

 

Puede solicitarse mediante el comando select:


demo=# select HolaMundo();
holamundo
-----------------------
Hola Mundo PostgreSQL
(1 row)

Puede eliminarse mediante el comando drop function.


demo=# drop function HolaMundo();
DROP

6.2. Variables

6.2. Variables Dataprix 26 Octubre, 2009 - 22:57
La sentencia alias crea un nuevo       nombre para una variable.                  
La sentencia rename cambia el          
nombre de una variable.                     

Las funciones pueden recibir parámetros, sólo es necesario especificar los tipos de  datos. PostgreSQL asigna los nombres  a los  parámetros utilizando la secuencia $1, $2, $3...

En este ejemplo veremos todas las posibles maneras de declarar variables en una función.


create function mi_funcion(int,char) returns int
as `
declare -- declaración de variables locales
x int; -- x es de tipo entero
y int := 10; -- y tiene valor inicial de 10
z int not null; -- z no puede tomar valores nulos
a constant int := 20; -- a es constante
b alias for $1; -- El primer parámetro tiene dos nombres.
rename $1 to c; -- Cambia de nombre el segundo parámetro begin
x := y + 30;
end;
` language `plpgsql´;

 

6.3. Sentencias

6.3. Sentencias Dataprix 27 Octubre, 2009 - 10:02

La estructura básica de una función es el bloque, que incluye dos partes, la declaración de variables y la sección de sentencias:

declare
sección de variables
begin
sección de sentencias
end;

Sentencia Descripción
declare begin end Bloque
:= Asignación
select into Asignación desde un select
Sentencias sql Cualquier sentencia sql
perform Realiza una llamada a comando sql
execute Interpreta una cadena como comando sql
exit Termina la ejecución de un bloque
return Termina la ejecución de una función
if Ejecuta sentencias condicionalmente
loop Repite la ejecución de un conjunto de sentencias
while Repite un conjunto de sentencias mientras
for Repite un conjunto de sentencias utilizando una variable de control
raise Despliega un mensaje de error a advertencia

La sentencia de asignación utiliza el operador ‘:=‘ para almacenar los resultados de expresiones en variables. PostgreSQL proporciona otra sentencia para hacer asignaciones, select. Esta sentencia debe obtener como resultado un solo valor para que pueda ser almacenado en la variable:


select into x psugerido from productos where clave = 3;

La ejecución de comandos sql como create, drop, insert o update pueden hacerse sin ninguna sintaxis especial. La excepción es el comando select, que requiere  ejecutarse  con  el  comando  perform  a  fin  de  que  el  resultado  de  la consulta sea descartado.


perform select psugerido from productos;

 

La sentencia execute también ejecuta un comando sql pero a partir de una cadena de texto. Esta sentencia comporta el problema de que su sintaxis no se verifica hasta la ejecución. Se puede utilizar, por ejemplo, para procesar parámetros como comandos sql:


execute $1

El comando exit termina la ejecución de un bloque. Se utiliza principalmente para romper ciclos.

La bifurcación, o ejecución condicional, se realiza mediante la sentencia if:


if ( $1 > 0 ) then
resultado := `Positivo´;
else
resultado := `Negativo´;
end if;

 

También puede utilizarse if con más de dos ramas:


if ( $1 > 0 ) then
resultado := `Positivo´;
elsif ( $1 resultado := `Negativo´;
else
resultado := `Cero´;
end if;

Con referencia a los bucles, PL/pgSQL ofrece tres opciones:

•    El bucle loop es infinito, por lo que tiene una estructura muy simple. Por lo general se utiliza con alguna sentencia if para terminarlo:

   cont := 0;
   loop
   if ( cont = 10 )
   then exit;
   end if;
   -- alguna acción
  cont := cont + 1;
  end loop;

 

•    El bucle while incluye la condición al inicio del mismo, por lo que el control de su terminación es más claro:

   cont := 0;
   while cont != 10 loop
   -- alguna acción
  cont := cont + 1;
  end loop;

•    El bucle for permite realizar un número de iteraciones controladas por la variable del ciclo:

   for cont in 1 .. 10 loop
  -- alguna acción
   end loop;

La sentencia raise permite enviar mensajes de tres niveles de severidad:

•    debug. El mensaje se escribe en la bitácora del sistema (logs).
•    notice. El mensaje se escribe en la bitácora y en el cliente psql.
•    exception . El mensaje se escribe en la bitácora y aborta la transacción.

 

El mensaje puede incluir valores de variables mediante el carácter ‘ %’:

•    raise debug ‘funcion(): ejecutada con éxito;
•    raise notice ‘El valor % se tomo por omisión’, variable;
•    raise excepción ‘El valor % está fuera del rango permitido’, variable;

 

 

6.4. Disparadores

6.4. Disparadores Dataprix 27 Octubre, 2009 - 10:26

Las funciones deben llamarse explícitamente para su ejecución o para incluirlas en consultas. Sin embargo, se puede definir que algunas funciones se ejecuten automáticamente cuando cierto evento tenga lugar en cierta tabla. Estas funciones se conocen como disparadores o triggers y se ejecutan mediante los comandos insert, delete y uptade.

Agregamos la tabla historial que almacena los productos descontinuados cuando se eliminan de la tabla productos.

create table historial (fecha date, parte varchar(20), tipo varchar(20), especificacion varchar(20), precio float(6));

Para poder utilizar una función como disparador, no debe recibir argumentos y debe retornar el tipo especial trigger:

La variable old está predefinida por  
PostgreSQL y se refiere al registro    con sus antiguos valores. Para          referirse a los nuevos valores,          
se dispone de la variable new.          

create function respaldar_borrados() returns trigger as
 begin insert into historial values ( now(), old.parte, old.tipo, old.especificacion, old.psugerido );
 return null;
end;

La función está lista para ser utilizada como disparador, sólo es necesario definirlo y asociarlo a la tabla y al evento deseado:
create trigger archivar before delete on productos
for each row execute procedure respaldar_borrados();

 

Acabamos de crear un disparador de nombre archivar que se activará cuando se ejecute el comando delete en la tabla productos. El usuario no necesita saber que se debe hacer una copia de seguridad de los registros borrados, se hace automáticamente.

Al crear el disparador, hemos especificado “before delete” al indicar la operación. PostgreSQL nos permite lanzar el disparador antes o después (before, after) que se efectúen las operaciones. Este matiz es importante, ya que, si este mismo disparador lo ejecutamos después de la operación, no veremos ninguna fila en la tabla. Es posible definir el mismo disparador para varias operaciones:

create trigger archivar before delete or update on productos
for each row execute procedure respaldar_borrados();