Anthony R.

Sotolongo Len
Yudisney Vazquez Ortz
ISBN : 978-1-312-99489-8, Edicin 1
La Habana, 2015

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Gua para el desarrollo de lgica de negocio del lado del servidor

CONTENIDOS
PREFACIO __________________________________________________________________VII
INTRODUCCIN A LA PROGRAMACIN DEL LADO DEL SERVIDOR EN
POSTGRESQL ________________________________________________________________12
1.1 Introduccin a las Funciones Definidas por el Usuario _____________________________12
1.2 Ventajas de utilizar la programacin del lado del servidor de bases de datos ____________15
1.3 Usos de la lgica de negocio del lado del servidor _________________________________16
1.4 Modelo de datos para el trabajo en el libro _______________________________________19
1.5 Resumen _________________________________________________________________19
PROGRAMACIN DE FUNCIONES EN SQL______________________________________21
2.1 Introduccin a las funciones SQL ______________________________________________21
2.2 Extensin de PostgreSQL con funciones SQL ____________________________________21
2.3 Sintaxis para la definicin de una funcin SQL ___________________________________21
2.4 Parmetros de funciones SQL _________________________________________________25
2.5 Retorno de valores__________________________________________________________28
2.6 Resumen _________________________________________________________________34
2.7 Para hacer con SQL _________________________________________________________35
PROGRAMACIN DE FUNCIONES EN PL/PGSQL________________________________37
3.1 Introduccin a las funciones PL/pgSQL _________________________________________37
3.2 Estructura de PL/pgSQL _____________________________________________________37
3.3 Trabajo con variables en PL/pgSQL ____________________________________________40
3.4 Sentencias en PL/pgSQL_____________________________________________________44
3.5 Estructuras de control _______________________________________________________47
3.6 Retorno de valores__________________________________________________________54
3.7 Mensajes _________________________________________________________________59
3.8 Disparadores ______________________________________________________________62
3.9 Resumen _________________________________________________________________77
3.10 Para hacer con PL/pgSQL ___________________________________________________77
PROGRAMACIN DE FUNCIONES EN LENGUAJES PROCEDURALES DE
DESCONFIANZA DE POSTGRESQL_____________________________________________79
4.1 Introduccin a los lenguajes de desconfianza _____________________________________79
4.2 Lenguaje procedural PL/Python _______________________________________________80
I

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Gua para el desarrollo de lgica de negocio del lado del servidor

4.2.1 Escribir funciones en PL/Python ___________________________________________80

4.2.2 Parmetros de una funcin en PL/Python ____________________________________81
4.2.3 Homologacin de tipos de datos PL/Python __________________________________83
4.2.4 Retorno de valores de una funcin en PL/Python ______________________________84
4.2.5 Ejecutando consultas en la funcin PL/Python ________________________________87
4.2.6 Mezclando ____________________________________________________________89
4.2.7 Realizando disparadores con PL/Python _____________________________________90
4.3 Lenguaje procedural PL/R____________________________________________________92
4.3.1 Escribir funciones en PL/R________________________________________________92
4.3.2 Pasando parmetros a una funcin PL/R _____________________________________93
4.3.3 Homologacin de tipos de datos PL/R _______________________________________94
4.3.4 Retornando valores de una funcin en PL/R __________________________________95
4.3.5

Ejecutando consultas en la funcin PL/R _________________________________98

4.3.6 Mezclando ____________________________________________________________99

4.3.7 Realizando disparadores con PL/R_________________________________________100
4.4 Para hacer con PL/Python y PL/R _____________________________________________101
4.5 Resumen ________________________________________________________________102
RESUMEN ___________________________________________________________________104
BIBLIOGRAFA ______________________________________________________________105

II

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Gua para el desarrollo de lgica de negocio del lado del servidor

NDICE DE EJEMPLOS
Ejemplo 1: Funcin SQL que elimina los estudiantes de quinto ao ________________________13
Ejemplo 2: Funcin en PL/pgSQL que suma 2 valores __________________________________13
Ejemplo 3: Invocacin de funciones en PostgreSQL ____________________________________14
Ejemplo 4: Invocacin de la suma definida en el ejemplo 2_______________________________14
Ejemplo 5: Funcin que actualiza el gnero de una cancin ______________________________16
Ejemplo 6: Empleo de PL/R para generar un grfico de pastel ____________________________17
Ejemplo 7: Funcin que importa el resultado de una consulta a un fichero CSV_______________18
Ejemplo 8: Funcin que elimina clientes de 18 aos o menos _____________________________22
Ejemplo 9: Reemplazo de funcin que elimina clientes menores de 20 en lugar de 18 aos ______24
Ejemplo 10: Reemplazo de la funcin eliminar_clientesmenores() existente por otra que elimina los
clientes menores de 20 aos y retorna la cantidad de clientes que quedan registrados en la base de
datos__________________________________________________________________________24
Ejemplo 11: Empleo de parmetros usando sus nombres en una sentencia INSERT____________26
Ejemplo 12: Empleo de parmetros empleando su numeracin en una sentencia INSERT _______26
Ejemplo 13: Empleo de parmetros con iguales nombres que columnas en tabla empleada en la
funcin________________________________________________________________________26
Ejemplo 14: Empleo de parmetros de tipo compuesto __________________________________27
Ejemplo 15: Empleo de parmetros de salida __________________________________________27
Ejemplo 16: Funcin que dado el identificador de la orden devuelve su monto total ___________29
Ejemplo 17: Funcin que a determinado producto le incrementa el precio en un 5% y lo muestra _29
Ejemplo 18: Funcin que incrementa, en un 5%, y muestra el precio de un producto determinado
pasndosele como parmetro un producto ____________________________________________30
Ejemplo 19: Empleo de la funcin aumentar_precio para aumentar el precio del producto
ACADEMY ADAPTATION ______________________________________________________31
Ejemplo 20: Empleo de la funcin mostrar_cliente para devolver todos los datos de un cliente
pasado por parmetro ____________________________________________________________31
Ejemplo 21: Empleo de la funcin mostrar_cliente para devolver el nombre del cliente con id 31_31
Ejemplo 22: Empleo de la funcin listar_productos para devolver el listado de productos existente
______________________________________________________________________________32
Ejemplo 23: Empleo de la funcin mostrar_productos para devolver nombre y precio de los
productos existentes empleando parmetros de salida ___________________________________33

III

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Gua para el desarrollo de lgica de negocio del lado del servidor

Ejemplo 24: Funcin mostrar_productos para devolver nombre y precio de los existentes empleando
RETURNS TABLE ______________________________________________________________34
Ejemplo 25: Funcin que retorna la suma 2 de nmeros enteros ___________________________38
Ejemplo 26: Empleo de variables en bloques anidados __________________________________39
Ejemplo 27: Creacin de un alias para el parmetro de la funcin duplicar_impuesto en el comando
CREATE FUNCTION y en la seccin DECLARE _____________________________________43
Ejemplo 28: Captura de una fila resultado de consultas SELECT, INSERT, UPDATE o DELETE 45
Ejemplo 29: Empleo del comando EXECUTE en consultas constantes y dinmicas____________47
Ejemplo 30: Empleo de la estructura condicional IF-THEN implementada en PL/pgSQL _______49
Ejemplo 31: Empleo de la estructura condicional IF-THEN-ELSE implementada en PL/pgSQL__49
Ejemplo 32: Empleo de la estructura condicional IF-THEN-ELSIF implementada en PL/pgSQL _50
Ejemplo 33: Empleo de la estructura condicional CASE implementada en PL/pgSQL__________50
Ejemplo 34: Empleo de la estructura condicional CASE buscado implementada en PL/pgSQL___50
Ejemplo 35: Empleo de las estructuras iterativas implementadas por PL/pgSQL usando LOOP y
WHILE _______________________________________________________________________53
Ejemplo 36: Empleo de la clusula RETURN para devolver valores y culminar la ejecucin de una
funcin________________________________________________________________________55
Ejemplo 37: Empleo de RETURN NEXT|QUERY para devolver un conjunto de valores resultante
de una consulta _________________________________________________________________58
Ejemplo 38: Mensajes utilizando las opciones RAISE: EXCEPTION, LOG y WARNING ______59
Ejemplo 39: Bloque de excepciones _________________________________________________61
Ejemplo 40: Implementacin de una funcin disparadora ________________________________63
Ejemplo 41: Disparador que invoca la funcin disparadora del ejemplo 40___________________64
Ejemplo 42: Disparador que invoca la funcin disparadora cuando se realiza una insercin,
actualizacin o eliminacin sobre la tabla categories ____________________________________64
Ejemplo 43: Ejecucin de una consulta que activa el disparador creado sobre la tabla categories _65
Ejemplo 44: Definicin de la funcin disparadora y su disparador asociado que se dispara cuando se
realiza alguna modificacin sobre la tabla categories ____________________________________66
Ejemplo 45: Ejecucin de trigger_modificacion en lugar de trigger_uno debido a que no se actualiza
ninguna tupla sobre la tabla categories con la consulta ejecutada __________________________67
Ejemplo 46: Disparador que permite modificar un nuevo registro antes de insertarlo en la base de
datos__________________________________________________________________________67
Ejemplo 47: Disparador que no permite eliminar de una tabla _____________________________69
Ejemplo 48: Forma de realizar auditoras sobre la tabla customers _________________________70

IV

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Gua para el desarrollo de lgica de negocio del lado del servidor

Ejemplo 49: Funcin disparadora y disparador por columnas para controlar las actualizaciones
sobre la columna price de la tabla products ___________________________________________72
Ejemplo 50: Definicin de un disparador condicional para chequear que se actualice el precio ___73
Ejemplo 51: Disparador condicional para chequear que actor comienza con maysculas ________73
Ejemplo 52: Funcin disparadora y disparadores necesarios para actualizar una vista __________74
Ejemplo 53: Uso de disparadores sobre eventos para registrar actividades de creacin y eliminacin
sobre tablas de la base de datos _____________________________________________________76
Ejemplo 54: Hola Mundo con PL/Python _____________________________________________80
Ejemplo 55: Paso de parmetros en funciones PL/Python ________________________________81
Ejemplo 56: Empleo de parmetros de salida __________________________________________81
Ejemplo 57: Paso de parmetros como tipo de dato compuesto ____________________________82
Ejemplo 58: Arreglos pasados por parmetros _________________________________________83
Ejemplo 59: Retornando una tupla en Python__________________________________________84
Ejemplo 60: Devolviendo valores de tipo compuesto como una tupla _______________________85
Ejemplo 61: Devolviendo valores de tipo compuesto como un diccionario ___________________85
Ejemplo 62: Devolviendo conjunto de valores como una lista o tupla _______________________86
Ejemplo 63: Devolviendo conjunto de valores como un generador _________________________87
Ejemplo 64: Devolviendo valores de la ejecucin de una consulta desde PL/Python con
plpy.execute____________________________________________________________________88
Ejemplo 65: Ejecucin con [Link] de una consulta preparada con [Link] __________88
Ejemplo 66: Guardar en un XML el resultado de una consulta ____________________________89
Ejemplo 67: Disparador en PL/Python _______________________________________________91
Ejemplo 68: Funcin en PL/R que suma 2 nmeros pasados por parmetros _________________92
Ejemplo 69: Funcin en PL/R que suma 2 nmeros pasados por parmetros, sin nombrarlos ____93
Ejemplo 70: Clculo de la desviacin estndar desde PL/R _______________________________93
Ejemplo 71: Pasando un tipo de dato compuesto como parmetro__________________________94
Ejemplo 72: Retorno de valores con arreglos desde PL/R ________________________________95
Ejemplo 73: Retorno de valores con tipos de datos compuestos desde PL/R __________________96
Ejemplo 74: Retorno de conjuntos __________________________________________________96
Ejemplo 75: Retorno del resultado de una consulta desde PL/R usando [Link] ____________98
Ejemplo 76: Funcin que genera una grfica de barras con el resultado de una consulta en PL/R _99
Ejemplo 77: Utilizando disparadores en PL/R ________________________________________101

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Gua para el desarrollo de lgica de negocio del lado del servidor

VI

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Gua para el desarrollo de lgica de negocio del lado del servidor

PREFACIO
Libro dirigido a estudiantes (de carreras afines con la Informtica) y profesionales que trabajen con
tecnologas de bases de datos, especficamente con el motor de bases de datos PostgreSQL.
Surge en respuesta a la necesidad de contar con bibliografa o materiales orientados a la docencia,
produccin e investigacin, con ejemplos variados, propuestas de ejercicios para aplicar los
conocimientos adquiridos y en idioma espaol, que de forma prctica posibilite incorporar tcnicas
de programacin haciendo uso del SQL (del ingls Structured Query Language) y de lenguajes
procedurales para programar del lado del servidor de bases de datos. Elementos que en los libros
existentes no son tratados en su conjunto, haciendo de la propuesta una opcin a ser considerada.
Programar del lado del servidor de bases de datos brinda un grupo importante de opciones que
pueden ser aprovechadas para ganar en rendimiento y potencia. Por lo que el propsito de este libro
es que el lector pueda aplicar las opciones que brindan SQL y los lenguajes procedurales para el
desarrollo de funciones, potenciando sus funcionalidades en la programacin del lado del servidor
de bases de datos.
Para facilitar la adquisicin de los conocimientos tratados, el libro brinda una serie de ejemplos
basados en Dell Store 2 (base de datos de prueba para PostgreSQL), proponiendo al final de los
captulos 2, 3 y 4 ejercicios en los que se deben aplicar los elementos abordados en ellos para su
solucin.

Qu cubre el libro?
Se encuentra dividido en cuatro captulos:
-

Captulo 1. Introduccin a la programacin del lado del servidor en PostgreSQL: se

realiza una introduccin a la programacin del lado del servidor, sus ventajas y cmo
PostgreSQL permite dicha programacin.

Captulo 2. Programacin de funciones en SQL: aborda cmo programar funciones en

SQL; explicndose mediante ejemplos la sintaxis bsica para su creacin, empleo de
parmetros y el retorno de valores.

Captulo 3. Programacin de funciones en PL/pgSQL: aborda cmo programar

funciones en PL/pgSQL; explicndose su estructura, cmo trabajar con variables y
sentencias, las estructuras condicionales y de control que implementa y cmo hacer uso de
los disparadores.

VII

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Gua para el desarrollo de lgica de negocio del lado del servidor

Captulo 4. Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL: se realiza una breve introduccin a los lenguajes procedurales de
desconfianza que soporta PostgreSQL, abordndose en detalles PL/Python y PL/R; de los
que se explica su compatibilidad con el gestor, la sintaxis bsica para escribir funciones en
ellos, el empleo de parmetros, la homologacin de los tipos de datos de cada uno con
PostgreSQL y, cmo realizar con ellos el retorno de valores, la ejecucin de consultas y la
definicin de disparadores.

Qu necesita para trabajar con este libro?

Para que este libro sea til y puedan irse probando los ejemplos ilustrados, el lector debe:
-

Tener conocimientos bsicos de SQL, Python y R.

Tener acceso a un servidor de bases de datos PostgreSQL 9.3 (versin en la que fueron
ejecutadas todas las sentencias contenidas en el libro), de preferencia con privilegios
administrativos.

Los

ejecutables

del

gestor

pueden

ser

descargados

desde

[Link]
-

Contar con un cliente de administracin, todos los ejemplos fueron ejecutados en el psql
pero puede emplearse pgAdmin o cualquier otro.

Contar con la base de datos Dell Store 2, sobre la que estn basados los ejemplos de los
captulos 2, 3 y 4; puede ser accedida desde las direcciones [Link] y
[Link]

Cmo dosificar los contenidos del libro?

La estructuracin de los captulos responde a la manera de abordar la programacin del lado del
servidor de bases de datos mediante el desarrollo de funciones aumentando el grado de
complejidad, tanto en el uso de las potencialidades que ofrecen los lenguajes, como de los lenguajes
en s.
Se sugiere leer el captulo introductorio para comprender las ventajas que ofrece la programacin
del lado del servidor y cmo PostgreSQL la permite.
Aquel lector con dominio de SQL puede prescindir del estudio del captulo 2 (aun cuando puede ser
necesario revisarlo para conocer la sintaxis de creacin de las funciones) y comenzar a estudiar el
captulo 3 y las potencialidades que ofrece PL/pgSQL para programar del lado del servidor de bases
de datos. Y luego, tendr las habilidades suficientes para comprender y poder hacer uso de los
lenguajes procedurales de desconfianza PL/Python y PL/R.

VIII

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Gua para el desarrollo de lgica de negocio del lado del servidor

Convenciones
Para proveer una mayor legibilidad, a lo largo del libro se utilizan varios estilos de texto segn la
informacin que transmiten:
-

Cdigo: el cdigo de las sintaxis bsicas, funciones y consultas utilizadas se escribirn en

Droid Sans a 10 puntos, resaltndose con negritas y en maysculas, cuando por sintaxis no
sea incorrecto las palabras reservadas del lenguaje; el siguiente es un ejemplo de bloque de
cdigo:
CREATE FUNCTION eliminar_clientesmenores() RETURNS void AS
$$
DELETE FROM customers WHERE age <= 18;
$$ LANGUAGE sql;

Los ejemplos, generalmente cdigo de consultas o funciones implementadas, se enumeran y

se enmarcan de la forma:

Ejemplo 1: Funcin SQL que elimina los estudiantes de quinto ao

CREATE OR REPLACE FUNCTION eliminar_estudiantes() RETURNS void AS

$$
DELETE FROM estudiante WHERE anno = 5;
$$ LANGUAGE sql;

Notas: acotaciones sobre lo que se est discutiendo se escribirn en Roboto, cursivas a 10

puntos y enmarcadas, de la forma:
Para analizar en detalles el acotado a emplear en el cuerpo de una funcin puede remitirse a las
Secciones String Constants y Dollar-quoted String Constants de la Documentacin Oficial de
PostgreSQL

Errores
De encontrarse cualquier error se le agradecera que lo comunicara a los autores.

IX

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Gua para el desarrollo de lgica de negocio del lado del servidor

AUTORES
Anthony R. Sotolongo Len (asotolongo@[Link]). Profesor de la Universidad de las Ciencias
Informticas y miembro de la Comunidad Cubana de PostgreSQL. Obtuvo el grado de Mster en
Informtica Aplicada en el ao 2010 en la Universidad. Ha impartido en el pregrado durante 8 aos
asignaturas relacionadas con las tecnologas de bases de datos como Sistemas de Bases de Datos I,
Sistemas de Bases de Datos II y Optimizacin de bases de datos. Imparte hace 3 ediciones las
asignaturas Introductorio a PostgreSQL, Programacin en PostgreSQL y Rplica de datos en
PostgreSQL del Diplomado en tecnologas de bases de datos PostgreSQL, del cual es Coordinador.
Organiza y coordina eventos relacionados con el gestor en Cuba y ha impartido varias charlas sobre
PostgreSQL.

Yudisney Vazquez Ortz (yvazquezo@[Link], yvazquezo@[Link]). Profesora de la

Universidad de las Ciencias Informticas y miembro de la Comunidad Cubana de PostgreSQL.
Obtuvo el grado de Mster en Gestin de Proyectos Informticos en el ao 2011 en la Universidad.
Ha impartido en el pregrado durante 7 aos asignaturas relacionadas con las tecnologas de bases de
datos como Sistemas de Bases de Datos I, Sistemas de Bases de Datos II y Optimizacin de bases
de datos. Imparte hace 2 ediciones las asignaturas Seguridad en PostgreSQL y Programacin en
PostgreSQL del Diplomado en tecnologas de bases de datos PostgreSQL, del cual es miembro de
su Comit Acadmico. Organiza y coordina eventos relacionados con el gestor en Cuba.

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Gua para el desarrollo de lgica de negocio del lado del servidor

COLABORADORES
Ing. Daymel Bonne Sols
Ing. Marcos Luis Ortiz Valmaseda
Ing. Adalennis Buchilln Sors

XI

1.

INTRODUCCIN A LA PROGRAMACIN DEL LADO DEL SERVIDOR

EN POSTGRESQL
1.1 Introduccin a las Funciones Definidas por el Usuario
Las bases de datos relacionales son el estndar de almacenamiento de datos de las aplicaciones
informticas, las operaciones con dichas bases de datos suelen ser comnmente mediante sentencias
SQL, lenguaje por defecto para interactuar con las mismas.
Ver los gestores de bases de datos relacionales puramente para almacenar datos es restringir sus
potencialidades de trabajo, puesto que brindan algo ms que un lugar para acumular datos, ejemplo
de ello son los procedimientos almacenados y los disparadores (triggers), parte de un grupo
importante de funcionalidades que se pueden potenciar programando del lado del servidor de bases
de datos.
El manejo de los datos solamente con lenguaje de definicin y manipulacin de datos (DDL y
DML) tiene limitaciones, pues no permiten operaciones como controles de flujo o utilizacin de
variables para retener un dato determinado, impidiendo realizar operaciones de lgica de negocio
del lado del servidor de bases de datos, con las consecuentes ventajas que esto puede acarrear.
PostgreSQL como gestor de bases de datos relacional brinda las caractersticas de programacin del
lado del servidor desde sus inicios, que a partir de la versin 7.2 se mejoraron considerablemente y
se le agregaron otros lenguajes para la programacin (llamados lenguajes de desconfianza), adems
del conocido Estndar SQL. Paulatinamente se han ido agregando mejoras a la programacin del
lado del servidor y hoy es una verdadera potencialidad para el desarrollo de aplicaciones que
utilizan PostgreSQL como gestor de bases de datos.
PostgreSQL permite el desarrollo de la programacin de lgica de negocio del lado del servidor
mediante Funciones Definidas por el Usuario (FDU por sus siglas en ingls), tambin llamadas en
otros gestores como procedimiento almacenados. Estas funciones se pueden entender como el
conjunto agrupado de operaciones que se ejecutan del lado del servidor y que pueden derivar en
acciones sobre los datos; pueden utilizar otras caractersticas del gestor como los tipos de datos y
operadores personalizados, reglas, vistas, etc. Las Funciones Definidas por el Usuario pueden

clasificarse en cuatro tipos segn la seccin User-dened Functions de la Documentacin Oficial:

12

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Introduccin a la programacin del lado del servidor en PostgreSQL

Funcin

Funcin en lenguaje procedural: ejecuta operaciones empleando lenguajes de tipo

en

SQL:

ejecuta

puramente

operaciones

SQL.

procedural como PL/pgSQL o PL/Python.

-

Funcin interna: ejecuta operaciones en lenguaje C, estn enlazadas directamente al

servidor PostgreSQL lo que implica que estn predefinidas dentro del gestor.

Funcin en lenguaje C: las operaciones estn escritas en el lenguaje C o compatible (como

C++) y pueden ser cargadas a PostgreSQL dinmicamente bajo demanda.

Este libro se centrar en los dos primeros tipos de funciones mencionadas debido a que es la forma
ms comn de programar del lado del servidor en PostgreSQL.
Es vlido aclarar que todo lo que ocurre dentro de una Funcin Definida por el Usuario en
PostgreSQL lo hace de forma transaccional, es decir, se ejecuta todo o nada; de ocurrir algn fallo
el propio PostgreSQL realiza un RollBack deshaciendo las operaciones realizadas.
Los siguientes ejemplos ilustran acciones que se pueden realizar al emplear funciones definidas por
el usuario.
Ejemplo 1: Funcin SQL que elimina los estudiantes de quinto ao

CREATE OR REPLACE FUNCTION eliminar_estudiantes() RETURNS void AS

$$
DELETE FROM estudiante WHERE anno = 5;
$$ LANGUAGE sql;
Ejemplo 2: Funcin en PL/pgSQL que suma 2 valores

CREATE OR REPLACE FUNCTION sumar(valor1 int, valor2 int) RETURNS int AS

$$
BEGIN
RETURN $1 + $2;
END;
$$ LANGUAGE plpgsql;

Note que entre ambos tipos de funciones hay ciertas diferencias, que se analizarn con mayor nivel
de detalle en los captulos 2 y 3 respectivamente.

13

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Introduccin a la programacin del lado del servidor en PostgreSQL

Ejemplos de funciones internas y funciones escritas en C pueden encontrarse en la Documentacin

Oficial de PostgreSQL a las secciones Internal Functions y C-Language Functions

Para invocar una funcin se debe ejecutar la sentencia:

SELECT nombre_funcin( [parmetro_funcin1,] )

Si dentro de una funcin se realiza el llamado a otra funcin basta con colocar su nombre sin
necesidad de utilizar la sentencia SELECT.
Los siguientes ejemplos ilustran cmo realizar invocaciones de funciones.
Ejemplo 3: Invocacin de funciones en PostgreSQL

postgres=# SELECT version();

Version
PostgreSQL 9.3.1, compiled by Visual C++ build 1600, 64-bit
(1 fila)
version() es una funcin interna de PostgreSQL. Puede encontrar otras funciones internas en la
Documentacin Oficial de PostgreSQL en la seccin Functions and Operators
Ejemplo 4: Invocacin de la suma definida en el ejemplo 2

postgres=# SELECT sumar(1,2);

sumar
3
(1 fila)

1.2 Ventajas de utilizar la programacin del lado del servidor de bases de datos
Tener el cdigo de la lgica de negocio en el servidor de bases de datos puede ir en contra de
algunos modelos de desarrollo de aplicaciones, como por ejemplo el modelo Tres Capas, donde se
le asigna a cada capa una de las siguientes actividades:
-

Capa de datos: base de datos.

Capa de negocio: capa intermedia, lgica de negocio, operaciones, etc.

Capa de presentacin: presentacin al usuario.

14

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Introduccin a la programacin del lado del servidor en PostgreSQL

El punto de vista anterior se cumple si se logra que las capas coincidan con un lugar fsico en el
desarrollo del sistema, es decir capa de datos con el gestor de bases de datos, capa de negocio con el
cdigo de las aplicaciones y capa de presentacin con la interfaz que se le presenta al usuario.
Ahora, si se ven las capas como lgicas en el desarrollo del sistema, puede describirse como la capa
de datos al gestor de bases de datos, como capa de negocio el cdigo del negocio (que puede estar
en el gestor de bases de datos), y como capa de presentacin a la interfaz para el usuario.
Vindolo desde este ltimo punto de vista, la programacin del lado del servidor brinda excelentes
posibilidades de desarrollo y, por tanto, varias ventajas como las que se describen en el libro
PostgreSQL Server Programming de los autores Hannu Krosing, Jim Mlodgenski y Kirk Roybal,
las cuales se resumen en:
-

Rendimiento: si la lgica de negocio se realiza de lado del servidor se evita que los datos
viajen de la base de datos a la aplicacin, evitando la latencia por esta operacin.

Fcil mantenimiento: si la lgica de negocio cambia por algn motivo los cambios se
realizan en la base de datos central y pueden ser realizados fcilmente mediante el Lenguaje
de Definicin de Datos (DDL) para actualizar las funciones implicadas en los cambios.

Simple modo de garantizar la seguridad en la lgica de negocio: a las funciones se les

pueden definir permisos de acceso a travs del Lenguaje de Control de Datos (DCL),
adems de evitar que los datos viajen por la red.

Otra ventaja es que evita la necesidad de reescribir cdigo de negocio, imagine que se tiene una
base de datos de la que consumen datos varias aplicaciones escritas en diferentes lenguajes como
Pascal, Java, PHP o Python y, que se tenga que realizar una operacin de insercin y actualizacin
de datos; si dicha operacin est en el lado del servidor de bases de datos solo sera ordenar su
ejecucin desde las distintas aplicaciones.
1.3 Usos de la lgica de negocio del lado del servidor
Transacciones que incluyan varias operaciones
Es muy comn ejecutar varias sentencias relacionadas que realizan operaciones sobre los datos,
sobre todo para evitar trfico en la red. Por ejemplo, si se desea actualizar el gnero de una cancin
con un valor dado, de la cual se conoce su nombre y, adems, se debe devolver el autor de dicha
cancin, se pudiera implementar una funcin como la mostrada en el ejemplo 5.

15

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Introduccin a la programacin del lado del servidor en PostgreSQL

Ejemplo 5: Funcin que actualiza el gnero de una cancin

CREATE FUNCTION actualizar_genero(nomb_c varchar, genero_c varchar) RETURNS

varchar AS
$$
DECLARE
id integer;
autor varchar;
BEGIN
UPDATE cancion SET genero = $2 WHERE nombre_cancion = $1;
SELECT idcancion INTO id FROM cancion WHERE nombre_cancion = $1;
SELECT autor INTO autor FROM cancion WHERE idcancion = id;
RETURN autor;
END;
$$ LANGUAGE plpgsql;

Note que se realizan varias operaciones para obtener el resultado deseado, que de realizarse a nivel
de aplicacin implicara un mayor trfico de datos entre esta y el servidor, incidiendo en el
rendimiento de la misma.
Auditoras de datos
La auditora de datos es la operacin de chequear las operaciones realizadas sobre los datos con el
objetivo de detectar anomalas u operaciones no deseadas o incorrectas. Uno de los usos que ms se
le da a la lgica de negocio del lado del servidor es para realizar estas auditoras. Para efectuar una
operacin de este tipo, donde se lleve el registro de los datos modificados o eliminados, se pueden
utilizar disparadores, ejemplos de ello son los siguientes mdulos desarrollados para esta tarea:
-

Pgaudit: disponible en [Link]

Audit-triggers: disponible [Link]

Dichos ejemplos son un conjunto de funciones y disparadores que posibilitan detectar cambios
realizados sobre los datos y, son configurables para trabajar sobre las tablas deseadas.

16

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Introduccin a la programacin del lado del servidor en PostgreSQL

Consumo de caractersticas de otros lenguajes de programacin

Existen operaciones que los lenguajes nativos brindados por el gestor no realizan o su posibilidad
de hacer alguna actividad es compleja. Una de las potencialidades que ms se puede explotar con la
programacin del lado del servidor es el consumo o ejecucin de funciones de otros lenguajes. Por
ejemplo, si se necesitara generar un grfico de pastel pudiera emplearse el lenguaje R (especializado
en actividades estadsticas) como muestra la funcin del ejemplo siguiente.
Ejemplo 6: Empleo de PL/R para generar un grfico de pastel

CREATE FUNCTION generar_pastel(nombre text, vector integer[], texto text)

RETURNS integer AS
$$
png(paste(nombre,"png",sep="."))
pie(vector,header=TRUE,col=rainbow(length(vector)),main=texto,labels=vector)
[Link]()
$$
LANGUAGE plr;
-- Invocacin de la funcin
postgres=# SELECT generar_pastel('migraficapie',array[3,6,7,9],'Ejemplo de Pie');
generar_pastel
(1 fila)

Una vez invocada la funcin se muestra el grfico mostrado en la figura siguiente.

Figura 1: Grfica generada con PL/R

17

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Introduccin a la programacin del lado del servidor en PostgreSQL

Importacin y exportacin de datos

Desde PostgreSQL se puede realizar exportacin o importacin de datos, por ejemplo, si se necesita
exportar el resultado de una consulta a un formato CSV, se puede desarrollar una funcin con
PL/pgSQL que realice dicha actividad como muestra el ejemplo siguiente.
Ejemplo 7: Funcin que importa el resultado de una consulta a un fichero CSV

CREATE FUNCTION salvar() RETURNS boolean AS

$$
BEGIN
COPY (SELECT * FROM empleado ) TO '/tmp/[Link]' WITH CSV;
RETURN true;
END;
$$ LANGUAGE plpgsql;

Sin lugar a dudas, programar del lado del servidor de bases de datos empleando Funciones
Definidas por el Usuario brinda un grupo importante de opciones que pueden ser aprovechadas para
ganar en rendimiento y potencia. De ah que el propsito de este libro sea analizar con mayor nivel
de detalle la forma de crear funciones utilizando SQL y lenguajes procedurales para potenciar sus
funcionalidades.
1.4 Modelo de datos para el trabajo en el libro
El modelo de datos mostrado en la figura 2 se corresponde con la base de datos Dell Store 2,
disponible en el proyecto Coleccin de bases de datos de ejemplos para PostgreSQL y que puede
ser accedida desde [Link] y [Link]
1.5 Resumen
La programacin del lado del servidor de bases de datos es una opcin para el desarrollo de los
sistemas informticos. Su atractivo radica en que ofrece un grupo de ventajas entre las que destacan
el ganar en rendimiento, seguridad y facilidad de manteniendo, as como el evitar reescritura de
cdigo.
PostgreSQL como sistema de gestin de bases de datos relacional permite la programacin del lado
del servidor, principalmente mediante Funciones Definidas por el Usuario.

18

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Introduccin a la programacin del lado del servidor en PostgreSQL

En el captulo se mostraron algunos ejemplos bsicos del uso de las Funciones Definidas por el
Usuario que, a medida que se avance en la lectura de este libro podrn comprenderse mejor, y el
lector podr utilizarlas para potenciar las funcionalidades de este gestor.

19

Figura 2: Modelo de datos de la base de datos Dell Store 2

20

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

2.

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

PROGRAMACIN DE FUNCIONES EN SQL

2.1 Introduccin a las funciones SQL

En el captulo se realiza una breve introduccin a cmo programar funciones en SQL; explicndose
mediante ejemplos sencillos la sintaxis bsica para la creacin de funciones, el empleo de
parmetros y los tipos existentes para el retorno de valores, de forma que se pueda posteriormente
enfrentar el trabajo con lenguajes procedurales de mayores potencialidades para la extensin del
gestor.
Para estudiar en detalle el lenguaje SQL puede consultar libros como Understanding the New SQL,
A Guide to the SQL Standard y la propia Documentacin de PostgreSQL.
2.2 Extensin de PostgreSQL con funciones SQL
Las funciones son parte de la extensibilidad que provee PostgreSQL a los usuarios; siendo el cdigo
escrito en SQL uno de los ms sencillos de aadir al gestor.
Las funciones SQL ejecutan una lista arbitraria de sentencias SQL separadas por punto y coma (;)
que retornan el resultado de la ltima consulta en dicha lista; por lo que cualquier coleccin de
comandos SQL puede ser empaquetada y definida como una funcin.
Adems de consultas SELECT se pueden incluir consultas de modificacin de datos (INSERT,
UPDATE, DELETE), as como cualquier otro comando SQL, excepto aquellos de control de
transacciones (COMMIT, SAVEPOINT) y algunos de utilidad (VACUUM).
2.3 Sintaxis para la definicin de una funcin SQL
Para la definicin de una funcin SQL se emplea el comando CREATE FUNCTION de la forma:
CREATE [OR REPLACE] FUNCTION nombre ([parmetro1] [,...]) [RETURNS tipo_retorno |
RETURNS TABLE (nombre_columna tipo_columna [,...])] AS
$$

Cuerpo de la funcin

21

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en SQL

$$ LANGUAGE sql;
Para analizar la sintaxis ampliada de definicin de una funcin puede remitirse a la Documentacin
Oficial de PostgreSQL en el captulo Reference, epgrafe SQL Commands

El empleo del comando CREATE FUNCTION requiere tener en cuenta los siguientes elementos:
-

Para definir una nueva funcin el usuario debe tener privilegio de uso (USAGE) sobre el
lenguaje, SQL en este caso.

Si el esquema es incluido, entonces la funcin es creada en el esquema especificado, de otra

forma, es creada en el esquema actual.

El nombre de la nueva funcin no debe coincidir con ninguna existente con los mismos
parmetros y en el mismo esquema, en ese caso la existente se reemplaza por la nueva
siempre que sea incluida la clusula OR REPLACE durante la creacin de la funcin.

Funciones con parmetros diferentes pueden compartir el mismo nombre.

Por ejemplo, la funcin mostrada en el ejemplo 8 elimina aquellos clientes que tienen 18 aos o
menos, note que no se necesita que la funcin retorne ningn valor, por tanto, el tipo de retorno
definido es VOID; para mayor detalles de los tipos de retorno vea el epgrafe 2.5 Retorno de
valores.
Ejemplo 8: Funcin que elimina clientes de 18 aos o menos

CREATE FUNCTION eliminar_clientesmenores() RETURNS void AS

$$
DELETE FROM customers WHERE age <= 18;
$$ LANGUAGE sql;
-- Invocacin de la funcin
dell=# SELECT eliminar_clientesmenores();
eliminar_clientesmenores
(1 fila)

CREATE FUNCTION requiere que el cuerpo de la funcin sea escrito como una cadena constante.
Note que en la sintaxis detallada en la Documentacin Oficial de PostgreSQL para la definicin de
la funcin se emplean comillas simples (' '), pero es ms conveniente usar el dlar ($$) o cualquier
otro tipo de acotado, ya que de usarse las primeras, si estas o las barras invertidas (\) son requeridas
en el cuerpo de la funcin deben entrecomillarse tambin, haciendo engorroso el cdigo.
22

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en SQL

Para analizar en detalles el acotado a emplear en el cuerpo de una funcin puede remitirse a las
Secciones String Constants y Dollar-quoted String Constants de la Documentacin Oficial de
PostgreSQL. En este libro se emplear el $$ para acotar el cuerpo de las funciones

Para reemplazar la definicin actual de una funcin existente se especifica la clusula OR

REPLACE, con la que:
-

Se reemplaza la definicin de una funcin existente con el mismo nombre, parmetros y en

el mismo esquema especificado.

No es posible cambiar el nombre o los tipos de los parmetros de la definicin de la

funcin, si se intenta, lo que realmente se est haciendo es crear una funcin distinta.

No es posible cambiar el tipo de retorno de la funcin existente; para hacerlo se debe

eliminar y volver a crear la funcin.

No se cambian el propietario y los permisos de la funcin.

Si se elimina y se vuelve a crear la funcin, esta nueva funcin no es el mismo objeto que la
vieja, por tanto, tendrn que eliminarse las reglas, vistas y disparadores existentes que
hacan referencia a la antigua funcin.

Por ejemplo, la funcin mostrada en el ejemplo 9 reemplaza la funcin anterior

eliminar_clientesmenores() pues tiene el mismo nombre y parmetros (en este caso ninguno),
especificando ahora que se borrarn aquellos clientes menores de 20 en lugar de 18 aos.
Note que de no especificarse la clusula OR REPLACE, al intentarse definir la funcin,
PostgreSQL arroja un error especificando que ya existe una funcin con el mismo nombre y
parmetros.
Ejemplo 9: Reemplazo de funcin que elimina clientes menores de 20 en lugar de 18 aos

CREATE OR REPLACE FUNCTION eliminar_clientesmenores() RETURNS void AS

$$
DELETE FROM customers WHERE age < 20;
$$ LANGUAGE sql;

Pero, si adems de eliminar los clientes menores de 20 aos se quisiera mostrar la cantidad de
clientes restantes, como a la funcin existente no se le puede cambiar el tipo de retorno, se debe
eliminar e implementar una nueva con las especificaciones requeridas, como se muestra en el
ejemplo 10.

23

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en SQL

Ejemplo 10: Reemplazo de la funcin eliminar_clientesmenores() existente por otra que elimina los clientes menores de
20 aos y retorna la cantidad de clientes que quedan registrados en la base de datos

-- Eliminar la funcin anterior

DROP FUNCTION eliminar_clientesmenores();
-- Definir la nueva funcin
CREATE FUNCTION eliminar_clientesmenores() RETURNS bigint AS
$$
DELETE FROM customers WHERE age < 20;
SELECT count(*) FROM customers;
$$ LANGUAGE sql;
-- Invocacin de la funcin
dell=# SELECT eliminar_clientesmenores();
eliminar_clientesmenores
19480
(1 fila)

Note que la diferencia de esta funcin con la implementada en el ejemplo 9 radica en que se agrega
una sentencia SELECT (para retornar la cantidad de clientes restantes) despus de la eliminacin,
con el consecuente cambio del tipo de dato de retorno de la funcin.
2.4 Parmetros de funciones SQL
Los parmetros de una funcin especifican aquellos valores que el usuario define sean empleados
para su procesamiento posterior en el cuerpo de la funcin con el fin de obtener el resultado
esperado. Se definen dentro de los parntesis colocados detrs del nombre de la funcin con la
forma:
[modo_parmetro] [nombre_parmetro] tipo_dato_parmetro

El modo del parmetro puede ser de 4 tipos:

-

IN: modo por defecto, especifica que el parmetro es de entrada, o sea, forma parte de la
lista de parmetros con que se invoca a la funcin y que son necesarios para el
procesamiento definido en la funcin.

24

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en SQL

OUT: parmetro de salida, forma parte del resultado de la funcin y no se incluye en la

invocacin de la funcin.

INOUT: parmetro de entrada/salida, puede ser empleado indistintamente para que forme
parte de la lista de parmetros de entrada y que sea parte luego del resultado.

VARIADIC: parmetro de entrada con un tratamiento especial, que permite definir un

arreglo para especificar que la funcin acepta un conjunto variable de parmetros, los que
lgicamente deben ser del mismo tipo.
Para mayor detalle en el empleo de parmetros de tipo VARIADIC puede remitirse a la
Documentacin Oficial en la seccin SQL Functions with Variable Numbers of Arguments del
captulo Extending SQL

Los parmetros pueden ser referenciados en el cuerpo de la funcin usando sus nombres (a partir de
la versin 9.2 de PostgreSQL) o nmeros. Para ello se debe tener en cuenta que:
-

Para usar el nombre, se debe previamente haber especificado este en la declaracin de los
parmetros cuando se defini la funcin.

Si el parmetro es nombrado igual que alguna columna empleada en el cuerpo de la

funcin, puede causar problemas de precedencia. No obstante, se recomienda con el fin de
evitar esta ambigedad (1) emplear parmetros con nombres distintos a las columnas de las
tablas que puedan utilizarse en la funcin, (2) definir un alias distinto para la columna de la
tabla que entra en conflicto con el parmetro o, (3) calificar los parmetros con el nombre
de la funcin.

Los parmetros pueden ser referenciados con nmeros de la forma $nmero, refirindose $1
al primer parmetro definido, $2 al segundo, y as sucesivamente; lo cual funciona se haya,
o no, nombrado el parmetro.

Si el parmetro es de tipo compuesto se puede emplear la notacin [Link] para

acceder a sus atributos.

Los ejemplos 11, 12, 13, 14 y 15 ilustran los elementos mencionados.

Ejemplo 11: Empleo de parmetros usando sus nombres en una sentencia INSERT

CREATE FUNCTION insertar_categoria(categoria integer, nombre varchar) RETURNS

void AS
$$
INSERT INTO categories VALUES (categoria, nombre);

25

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en SQL

$$ LANGUAGE sql;

Note que en la sentencia de insercin se hace referencia a los parmetros mediante sus nombres,
especificados en la definicin de los parmetros.
Ejemplo 12: Empleo de parmetros empleando su numeracin en una sentencia INSERT

CREATE FUNCTION insertar_categoria(integer, varchar) RETURNS void AS

$$
INSERT INTO categories VALUES ($1, $2);
$$ LANGUAGE sql;

Note que puede perfectamente omitirse el nombre de los parmetros y solamente especificar el tipo
de dato, en cuyo caso se hace referencia a ellos mediante el signo $ y el nmero que ocupan en el
listado de parmetros definidos.
Ejemplo 13: Empleo de parmetros con iguales nombres que columnas en tabla empleada en la funcin

CREATE FUNCTION insertar_categoria(category integer, categoryname varchar)

RETURNS void AS
$$
INSERT

INTO

categories

VALUES

(insertar_categoria.category,

insertar_categoria.categoryname);
$$ LANGUAGE sql;

Observe en el ejemplo 13 que se definen los parmetros category y categoryname con los mismos
nombres que los atributos de la tabla categories, y que para evitar la ambigedad se califican los
parmetros con el nombre de la funcin.
Ejemplo 14: Empleo de parmetros de tipo compuesto

CREATE FUNCTION insertar_producto(categoria categories, id integer, titulo varchar,

actor varchar, precio float, especial integer, id_comun integer) RETURNS void AS
$$
INSERT INTO products VALUES ([Link], id, titulo, actor, precio,
especial, id_comun);
$$ LANGUAGE sql;

26

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en SQL

En el ejemplo 14 aprecie que se define el parmetro categoria que es del tipo compuesto categories
(PostgreSQL crea para cada tabla un tipo compuesto asociado), y que para utilizarlo en la insercin
de un nuevo producto se califica el parmetro accediendo al elemento category.
Ejemplo 15: Empleo de parmetros de salida

CREATE FUNCTION mostrar_nombrecompleto(id integer, OUT first varchar, OUT last

varchar) AS
$$
SELECT firstname, lastname FROM customers WHERE customerid = id;
$$ LANGUAGE sql;
-- Invocacin de la funcin
dell=# SELECT mostrar_nombrecompleto(20);
mostrar_nombrecompleto
(IAYPUX,YELMUQZEHW)
(1 fila)
-- Invocacin de la funcin empleando otra forma
dell=# SELECT first, last FROM mostrar_nombrecompleto(20);
first

last

IAYPUX

YELMUQZEHW

(1 fila)

En el ejemplo 15 se puede apreciar el empleo de los parmetros de salida first y last para guardar el
nombre y apellidos respectivamente del cliente pasado por parmetro. De no haberse declarado se
debe especificar el tipo de retorno de la funcin haciendo uso de la clusula RETURNS.
2.5 Retorno de valores
El tipo de retorno de las funciones especifica el tipo de dato que debe devolver la funcin una vez
que se ejecute; que puede ser:
-

Uno de los tipos de datos definidos en el estndar SQL o por el usuario, por ejemplo,
VARCHAR para devolver la ciudad en que vive el cliente Brian Daniel Vzquez Lpez,
INTEGER para devolver la edad del cliente Llilian Pozo Ortiz, VOID para aquellas

27

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en SQL

funciones que no retornen un valor usable, un tipo de dato compuesto (ejemplo

PRODUCTS para devolver una tupla de la tabla products de la base de datos), RECORD
para retornar una fila resultante de un subconjunto de las columnas de una tabla o de
concatenaciones entre tablas, etc.
-

Un conjunto de tuplas: mediante la especificacin del tipo de retorno SETOF algn_tipo,

o de forma equivalente declarando el tipo de retorno TABLE (columnas), en cuyo caso
todas las tuplas de la ltima consulta son retornadas.

El valor nulo: en caso de que la consulta no retorne ninguna fila.

A menos que la funcin sea declarada con el tipo de retorno VOID, la ltima sentencia de su cuerpo
debe ser un SELECT, o un INSERT, UPDATE o DELETE que incluya la clusula RETURNING
con la que devuelvan lo que sea especificado en el tipo de retorno de la funcin.
Retorno de tipos de datos bsicos
Las funciones SQL ms simples no tienen parmetros o retornan un tipo de dato bsico, retorno que
se puede realizar (como se muestra en los ejemplos 16 y 17):
-

Utilizando una consulta SELECT como la ltima del bloque de sentencias SQL de la
funcin.

Empleando la clusula RETURNING como parte de las consultas INSERT, UPDATE o

DELETE.

Ejemplo 16: Funcin que dado el identificador de la orden devuelve su monto total

CREATE FUNCTION monto_total(id integer) RETURNS numeric AS

$$
SELECT totalamount FROM orders WHERE orderid = id;
$$ LANGUAGE sql;
-- El mismo ejemplo empleando enumeracin de parmetros
CREATE FUNCTION monto_total(integer) RETURNS numeric AS
$$
SELECT totalamount FROM orders WHERE orderid = $1;
$$ LANGUAGE sql;

28

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en SQL

Note en el ejemplo 16 que ambas funciones estn compuestas por una sola sentencia SELECT,
retornando un tipo de dato bsico.
Ejemplo 17: Funcin que a determinado producto le incrementa el precio en un 5% y lo muestra

CREATE FUNCTION incrementar_precio(prod integer) RETURNS numeric AS

$$
UPDATE products SET price = price + 0.05 * price WHERE prod_id = prod;
SELECT price FROM products WHERE prod_id = prod;
$$ LANGUAGE sql;
-- El mismo ejemplo empleando la clusula RETURNING en el UPDATE
CREATE FUNCTION incrementar_precio(prod integer) RETURNS numeric AS
$$
UPDATE products SET price = price + 0.05 * price WHERE prod_id = prod
RETURNING price;
$$ LANGUAGE sql;

La primera funcin del ejemplo 17 est conformada por dos sentencias, siendo la ltima el SELECT
necesario para el retorno de la funcin. En la segunda funcin, que da respuesta a la misma
necesidad, se garantiza el retorno de la funcin mediante la clusula RETURNING en el UPDATE.
Empleo y retorno de tipos de datos compuestos
Cuando se escriben funciones que utilicen tipos de datos compuestos no basta con emplear el
parmetro, sino que hay que especificar qu campo de dicho tipo de dato se utilizar.
Por ejemplo, si se deseara incrementar y devolver el precio de un producto determinado en un 5%,
pudiera implementarse una funcin como la mostrada en el ejemplo 18 que, a diferencia del
ejemplo anterior, recibe como parmetro un producto en lugar de su identificador.
Ejemplo 18: Funcin que incrementa, en un 5%, y muestra el precio de un producto determinado pasndosele como
parmetro un producto

CREATE FUNCTION aumentar_precio(prod products) RETURNS numeric AS

$$
UPDATE products SET price = price + 0.05 * price WHERE prod_id =

29

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en SQL

prod.prod_id
RETURNING price;
$$ LANGUAGE sql;

Note que en el ejemplo anterior se hace referencia al prod_id mediante la calificacin del atributo
con el producto pasado por parmetro.
Si se quisiera utilizar la funcin aumentar_precio del ejemplo anterior para subirle el precio a un
producto determinado, pudiera utilizarse la consulta mostrada en el ejemplo 19. Constate que dos de
las ventajas de pasar por parmetro un dato compuesto es que no se necesita conocer previamente
un atributo en particular y, se le puede aumentar el precio o lo que se desee hacer a ms de un
registro, siempre que en el WHERE de la consulta que realiza la llamada a la funcin se
especifiquen las condiciones necesarias para ello.
Ejemplo 19: Empleo de la funcin aumentar_precio para aumentar el precio del producto ACADEMY ADAPTATION

dell=# SELECT common_prod_id, aumentar_precio(products.*)

dell=# FROM products
dell=# WHERE title= 'ACADEMY ADAPTATION';
common_prod_id

aumentar_precio

7173

30.44

(1 fila)

Note aqu que el empleo del * en el SELECT se utiliza para seleccionar la tupla actual de la tabla
como un valor compuesto, aunque tambin puede ser referenciada usando solamente el nombre de
la tabla pero este uso es poco empleado por acarrear confusin.
Una funcin puede, adems, retornar un tipo de dato compuesto, por ejemplo, si se quisieran
obtener todos los datos de un cliente pasado por parmetro, pudiera implementarse una funcin
como la mostrada en el ejemplo 20.
Ejemplo 20: Empleo de la funcin mostrar_cliente para devolver todos los datos de un cliente pasado por parmetro

CREATE FUNCTION mostrar_cliente(id integer) RETURNS customers AS

$$
SELECT * FROM customers WHERE customerid = id;

30

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en SQL

$$ LANGUAGE sql;

Ms an, si se quisiera, siguiendo con el ejemplo anterior, solamente devolver el nombre de un

cliente pasado por parmetro, la llamada a la funcin quedara de la forma que se observa en el
ejemplo 21. Lo que se realiza accediendo al atributo firstname de la tabla customers sobre la que se
realiza la seleccin en la funcin mostrar_cliente.
Ejemplo 21: Empleo de la funcin mostrar_cliente para devolver el nombre del cliente con id 31

dell=# SELECT (mostrar_cliente(31)).firstname;

firstname
XSKFVE
(1 fila)

Retorno de conjunto de valores

Muchas veces se necesita que una funcin retorne un conjunto de valores, por ejemplo, aquellos
clientes menores de 30 aos, o los que viven en determinada ciudad, etc. Con lo visto hasta ahora
esto no puede hacerse pero su implementacin es posible mediante la especificacin de SETOF en
la clusula RETURNS cuando se define la funcin.
Cuando una funcin SQL es declarada para que retorne SETOF algn_tipo, al invocarse lo que se
hace es retornar cada fila de la consulta resultante como un elemento del conjunto de resultado.
Esta funcionalidad puede ser usada:
-

Al llamarse la funcin en la clusula FROM, siendo cada elemento del resultado una fila de
la tabla resultante de la consulta.

Al invocarse la funcin como parte de la lista del SELECT de una consulta (que devuelve el
resultado enumerando los valores de las columnas separados por coma); el problema de
utilizar esta forma surge cuando se ponen como parte de la lista del SELECT ms de una
funcin que retorna un conjunto de valores, lo que implica que el resultado puede no tener
mucho sentido, este es uno de los motivos por el que en la Documentacin Oficial se dice
que esta funcionalidad pudiera desaparecer en versiones posteriores del gestor, no obstante,
sigue siendo actualmente una funcionalidad vlida y ampliamente utilizada.

Por ejemplo, si se quisiera obtener un listado de los productos existentes pudiera implementarse una
funcin como la mostrada en el ejemplo 22.

31

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en SQL

Ejemplo 22: Empleo de la funcin listar_productos para devolver el listado de productos existente

CREATE FUNCTION listar_productos() RETURNS SETOF products AS

$$
SELECT * FROM products;
$$ LANGUAGE sql;
-- Invocacin de la funcin desde la clusula FROM
dell=# SELECT * FROM listar_productos();
prod_id

category

title

14

common_prod_id

ACADEMY ACADEMY

1976

ACADEMY ACE

6289

-- More --- Invocacin de la funcin desde el SELECT

dell=# SELECT listar_productos();
listar_productos
(1,14,ACADEMY ACADEMY,,1976)
(2,6,ACADEMY ACE,,6289)
-- More --

Pudiera, adems, implementarse la funcin empleando parmetros de salida, por ejemplo, si se

quisiera obtener el nombre y precio de los productos existentes la funcin del ejemplo 23 servira.
Ejemplo 23: Empleo de la funcin mostrar_productos para devolver nombre y precio de los productos existentes
empleando parmetros de salida

CREATE FUNCTION mostrar_productos(OUT nombre varchar, OUT precio numeric)

RETURNS SETOF record AS
$$
SELECT title, price FROM products;
$$ LANGUAGE sql;
--Invocacin de la funcin desde la clusula FROM
dell=# SELECT * FROM mostrar_productos();

32

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

nombre

Programacin de funciones en SQL

precio

ACADEMY ACADEMY

25.99

ACADEMY ACE

20.99

-- More --

Note en el ejemplo anterior que el tipo definido en SETOF es record, que indica que la funcin
debe retornar un conjunto de filas. Este tipo se emplea cuando el resultado:
-

No es la estructura completa de una tabla, por ejemplo cuando se quieren obtener solamente
un conjunto de los atributos existentes, como en el caso anterior.

Se obtiene de concatenaciones entre tablas, por ejemplo cuando se desea obtener los
productos existentes y el nombre de la categora a que pertenecen.

Retorno de tipo tabla

Otra de las formas para retornar un conjunto de valores es empleando la clusula RETURNS
TABLE cuando se define la funcin. El mismo posee la ventaja de que fue aadido recientemente al
estndar y por ende, puede ser ms portable que el uso de SETOF.
Por ejemplo, la funcin implementada en el ejemplo 23 con parmetros de salida pudiera
implementarse de la forma mostrada en el ejemplo 24 emplendose RETURNS TABLE.
Ejemplo 24: Funcin mostrar_productos para devolver nombre y precio de los existentes empleando RETURNS TABLE

CREATE OR REPLACE FUNCTION mostrar_productos() RETURNS TABLE(nombre

varchar, precio numeric) AS
$$
SELECT title, price FROM products;
$$ LANGUAGE sql;

Note que para emplear esta forma debe especificarse cada columna de la salida que se desee como
una columna de la tabla del resultado en la clusula RETURNS TABLE y que, entonces, el tipo de
dato de la funcin se sustituye por una tabla con las columnas y tipos de datos especificados.

33

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en SQL

2.6 Resumen
Las funciones SQL son la forma ms sencilla de agregar cdigo al ncleo de PostgreSQL para su
extensin. Una funcin SQL es un conjunto de sentencias SQL empaquetadas, con el objetivo de
realizar un grupo de acciones para obtener un resultado.
Para definir una funcin SQL se emplea el comando CREATE FUNCTION, en el que se especifica
el nombre de la funcin, los parmetros que recibir, el tipo de retorno de la funcin y el cuerpo de
la misma, en el que se listan las sentencias SQL que conformarn la funcin.
Los parmetros de las funciones, que pueden ser de entrada, salida, entrada/salida o variables,
especifican aquellos valores que el usuario define sean empleados para su procesamiento posterior
en el cuerpo de la funcin y; pueden ser accesibles mediante su nombre o de la forma $nmero,
siendo nmero la posicin que ocupa en el listado de parmetros comenzando por el 1.
Las funciones SQL pueden retornar uno de los tipos bsicos definidos en el estndar SQL o por el
usuario, el valor nulo o un conjunto de valores (utilizando SETOF o RETURNS TABLE).
2.7 Para hacer con SQL
1. Implemente funciones que permitan la insercin de datos en cada una de las tablas:
a. categories
b. products
c. customers
d. orderlines
e. orders
2. Desarrolle una funcin que dado el identificador de un producto devuelva toda la
informacin referente a l.
3. Cree una funcin que dado el identificador de un cliente devuelva su nombre y apellidos,
edad, gnero, correo electrnico, telfono, ciudad y pas.
a. Emplee parmetros de salida.
b. Qu tipo de dato pudiera emplearse para no tener que declarar tantos parmetros
de salida?
4. Obtenga una funcin que dado el identificador de un producto actualice su precio, pasado
tambin por parmetro, y muestre finalmente toda la informacin del producto.
34

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en SQL

5. Elabore una funcin que dado el identificador del producto devuelva toda la informacin
referente a l, as como el total de pedidos realizados de l.
6. Implemente una funcin que devuelva todos los clientes de sexo femenino y menores de 30
aos.
a. De qu formas puede definirse el tipo de retorno de esta funcin? Cul es la
diferencia entre ellas?
7. Desarrolle una funcin que dado el identificador de un cliente elimine las rdenes
realizadas por l anteriores al 24 de abril de 2004 y muestre luego, de las resultantes, su
identificador, fecha, monto neto y monto total.
8. Cree una funcin que inserte un nuevo producto suministrado por el usuario y, adems,
muestre nombre, precio y categora de los existentes en la base de datos.
9. Obtenga una funcin que muestre por categora el total de productos existentes.

35

3.

PROGRAMACIN DE FUNCIONES EN PL/PGSQL

3.1 Introduccin a las funciones PL/pgSQL

En el captulo se realiza una introduccin a cmo programar funciones en PL/pgSQL, lenguaje
procedural para PostgreSQL empleado para implementar funciones y disparadores, que ha sido
incluido por defecto en todas las liberaciones del gestor a partir de su versin 9.0.
PL/pgSQL es un lenguaje influenciado directamente de PL/SQL de Oracle, que al igual que las
funciones SQL, permite la agrupacin de consultas SQL, evitando la saturacin de la red entre el
cliente y el servidor de bases de datos. Brinda, adems, un grupo de ventajas adicionales entre las
que destacan que puede incluir estructuras iterativas y condicionales; heredar todos los tipos de
datos, funciones y operadores definidos por el usuario; mejorar el rendimiento de clculos
complejos y emplearse para definir funciones disparadoras (triggers). Todo esto le otorga mayor
potencialidad al combinar las ventajas de un lenguaje procedural y la facilidad de SQL.
3.2 Estructura de PL/pgSQL
Al ser PL/pgSQL un lenguaje estructurado por bloques, su definicin debe ser un bloque de la
forma:
[ <<etiqueta>> ]
[DECLARE

Declaraciones]
BEGIN

Sentencias
END [etiqueta];

Se debe tener en cuenta que el uso de BEGIN y END para agrupar sentencias en PL/pgSQL no es el
mismo que al iniciar o terminar una transaccin. Las funciones y los disparadores son ejecutados
siempre dentro de una transaccin establecida por una consulta externa.

36

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

Esta estructura en forma de bloque puede constatarse en la funcin en PL/pgSQL mostrada en el

ejemplo 25.
Ejemplo 25: Funcin que retorna la suma 2 de nmeros enteros

CREATE OR REPLACE FUNCTION sumar(int, int) RETURNS int AS

$$
BEGIN
RETURN $1 + $2;
END;
$$ LANGUAGE plpgsql;
-- Invocacin de la funcin
dell=# SELECT sumar(2, 3);
sumar
5
(1 fila)

Ntese que en este lenguaje procedural, al igual que en SQL, se mantienen las sentencias
terminadas con punto y coma (;) al final de cada lnea y para retornar el resultado se emplea la
palabra reservada RETURN (para ms detalles vea el epgrafe 3.6 Retorno de valores).
Las etiquetas son necesarias cuando se desea identificar el bloque para ser usado en una sentencia
EXIT o para calificar las variables declaradas en l; adems, si son especificadas despus del END
deben coincidir con las del inicio del bloque.
Los bloques pueden estar anidados, por lo que aquel que aparezca dentro de otro debe terminar su
END con punto y coma, no siendo requerido el del ltimo END.
Cada sentencia en el bloque de sentencias puede ser un sub-bloque, empleado para realizar
agrupaciones lgicas o para crear variables para un grupo de sentencias. Las variables de bloques
externos pueden ser accedidas en un sub-bloque calificndolas con la etiqueta del bloque al que
pertenecen. El ejemplo 26 muestra el tratamiento de variables en bloques anidados y el acceso a
variables externas mediante su calificacin con el nombre del bloque al que pertenecen.

37

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

Ejemplo 26: Empleo de variables en bloques anidados

CREATE FUNCTION incrementar_precio_porciento(id integer) RETURNS numeric AS

$$
<<principal>>
DECLARE
incremento numeric := (SELECT price FROM products WHERE prod_id = $1) *
0.3;
BEGIN
RAISE NOTICE 'El precio despus del incremento ser de % pesos', incremento;
-- Muestra el incremento en un 30%
<<excepcional>>
DECLARE
incremento numeric := (SELECT price FROM products WHERE prod_id
= $1) * 0.5;
BEGIN
RAISE NOTICE 'El precio despus del incremento excepcional ser de %
pesos', incremento; -- Muestra el incremento en un 50%
RAISE NOTICE 'El precio despus del incremento ser de % pesos',
[Link]; -- Muestra el incremento en un

30%

END;
RAISE NOTICE 'El precio despus del incremento ser de % pesos', incremento;
-- Muestra el incremento en un 30%
RETURN incremento;
END;
$$ LANGUAGE plpgsql;
--Invocacin de la funcin
dell=# SELECT * FROM incrementar_precio_porciento(1);
NOTICE: El precio despus del incremento ser de 7.797 pesos
NOTICE: El precio despus del incremento excepcional ser de 12.995 pesos

38

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

NOTICE: El precio despus del incremento ser de 7.797 pesos

incrementar_precio_porciento
7.797
(1 fila)

3.3 Trabajo con variables en PL/pgSQL

Las variables pueden ser de cualquier tipo de dato SQL o definido por el usuario y deben ser
declaradas en la seccin DECLARE.
La sintaxis general para la declaracin de una variable es la siguiente:
nombre [CONSTANT] tipo [NOT NULL] [{DEFAULT | :=} expresin]

Donde:
-

La clusula CONSTANT especifica que la variable es constante, por lo que el valor inicial
asignado a la variable es el valor con que se mantendr, de no ser especificada la variable es
inicializada con el valor nulo.

La clusula NOT NULL especifica que la variable no puede tener asignado un valor nulo
(generndose un error en tiempo de ejecucin en caso de que ocurra); todas las variables
declaradas como no nulas deben tener especificado un valor no nulo.

La clusula DEFAULT (o :=) asigna un valor a la variable.

Pueden declarase variables de la forma mostrada en los siguientes ejemplos:

impuesto CONSTANT numeric := 0.3;

-- variable de tipo numeric con un valor constante

-- de 0.3

incremento integer DEFAULT 31;

-- variable de tipo entero con valor 31

region varchar := 'Occidente';

-- variable de tipo varchar con el valor Occidente

-- asignado

fecha date NOT NULL:= '2007-11-24';

-- variable de tipo fecha no nula y con valor 2007-- 11-24

Copiado de tipos de datos

Para el trabajo con variables una de las utilidades de PL/pgSQL es el copiado de tipos de datos, til
para que: (1) no sea necesario conocer el tipo de dato de la estructura que se est referenciando y,
(2) en caso de cambiar dicho tipo de dato no sea necesario cambiar la definicin de la funcin.

39

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

Los tipos de datos de PostgreSQL pueden verse en la Documentacin, en la seccin Data Types

Esta funcionalidad se utiliza de la forma:

variable%TYPE

Permitiendo %TYPE capturar el tipo de dato de la variable o columna de la tabla asociada, como se
muestra en el ejemplo siguiente, en el que la variable declarada impuesto tomar el tipo de dato
del campo tax de la tabla orders.
impuesto [Link]%TYPE

Tipo de dato fila

PL/pgSQL soporta, adems, el tipo de dato fila (ROWTYPE), un tipo compuesto que puede
almacenar toda la fila de una consulta SELECT o FOR y del que sus campos pueden ser accesibles
calificndolos, como cualquier otro tipo de dato compuesto (ver epgrafe Empleo y retorno de tipos
de datos compuestos).
Para emplearlo se debe tener en cuenta que:
-

En esta estructura slo son accesibles las columnas definidas por el usuario (no los OID u
otras columnas del sistema).

Los campos heredan el tamao y precisin de los tipos de datos de los que son copiados.

Una variable fila puede ser declarada para que tenga el mismo tipo de las filas de una tabla o vista
mediante la forma:
nombre_tabla%ROWTYPE

Accin que tambin puede realizarse declarando la variable del tipo de la tabla de la que se quiere
almacenar la estructura de sus filas:
variable nombre_tabla
Cada tabla tiene asociado un tipo de dato compuesto con su mismo nombre

Ejemplos equivalentes empleando ambas formas son los siguientes:

cliente customers%ROWTYPE;
cliente customers;

40

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

Tipo de dato compuesto

Los tipos de datos definidos por el usuario son una de las funcionalidades que brinda PostgreSQL
dentro de su capacidad de extensibilidad, que les permite definir sus propios tipos de datos para
determinado resultado.
Esta funcionalidad tiene varias opciones de definicin de tipos de datos personalizados, dentro de
los que se encuentran, entre otros, los enumerativos y los compuestos; los ltimos son de gran
utilidad sobre todo en ocasiones en que se necesita devolver de una funcin un resultado compuesto
por elementos de varias tablas.
Dicho resultado consiste que la encapsulacin de una lista de nombres, con sus tipos de datos,
separados por coma. La sintaxis de definicin del mismo es de la forma:
CREATE TYPE nombre AS ( [ nombre_atributo tipo_dato [,... ] ] ),

Un ejemplo de su empleo pudiera ser:

CREATE TYPE nombre_completo AS (nombre varchar, apellidos varchar);

Tipo de dato RECORD

PL/pgSQL soporta el tipo de dato RECORD, similar a ROWTYPE pero sin estructura predefinida,
la que toma de la fila actual asignada durante la ejecucin del comando SELECT o FOR.
RECORD no es un tipo de dato verdadero sino un contenedor. Este tipo de dato no es el mismo
concepto que cuando se declara una funcin para que retorne un tipo RECORD; en ambos casos la
estructura de la fila actual es desconocida cuando la funcin est siendo escrita, pero para el retorno
de una funcin la estructura actual es determinada cuando la llamada es revisada por el analizador
sintctico, mientras que la de la variable puede ser cambiada en tiempo de ejecucin.
Puede ser utilizado para devolver un valor del que no se conoce tipo de dato, pero s se debe
conocer su estructura cuando se quiere acceder a un valor dentro de l, por ejemplo para acceder a
un valor de una variable de tipo RECORD se debe conocer previamente el nombre del atributo para
poder calificarlo y acceder al mismo.
Parmetros de funciones
En PL/pgSQL al igual que en SQL (ver epgrafe 2.4 Parmetros de funciones SQL), se hace
referencia a los parmetros de las funciones mediante la numeracin $1, $2, $n o mediante un alias,
que puede crearse de 2 formas:
-

Nombrar el parmetro en el comando CREATE FUNCTION (forma preferida).

41

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

En la seccin de declaraciones (nica forma disponible antes de la versin 8.0 de

PostgreSQL).

El ejemplo 27 muestra estas 2 formas de hacer referencia a los parmetros de las funciones en
PL/pgSQL.
Ejemplo 27: Creacin de un alias para el parmetro de la funcin duplicar_impuesto en el comando CREATE
FUNCTION y en la seccin DECLARE

-- Funcin que define el alias de un parmetro en su definicin

CREATE FUNCTION duplicar_impuesto(id integer) RETURNS numeric AS
$$
BEGIN
RETURN (SELECT tax FROM orders WHERE orderid = id) * 2;
END;
$$ LANGUAGE plpgsql;
-- Forma de especificar el alias de un parmetro en la seccin DECLARE
CREATE FUNCTION duplicar_impuesto(integer) RETURNS numeric AS
$$
DECLARE
id ALIAS FOR $1;
BEGIN
RETURN (SELECT tax FROM orders WHERE orderid = id) * 2;
END;
$$ LANGUAGE plpgsql;

Es vlido aclarar que el comando ALIAS se emplea no slo para definir el alias de un parmetro,
sino que puede ser utilizado para definir el alias de cualquier variable.
3.4 Sentencias en PL/pgSQL
Una de las partes ms significativas de una funcin en PL/pgSQL es la especificacin de las
sentencias, que permitirn detallar las acciones necesarias para obtener el resultado deseado con la

42

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

ejecucin de la funcin. En este epgrafe se mostrarn algunas de las sentencias ms comunes

utilizadas en PL/pgSQL.
Asignacin de valores a variables
La asignacin de valores a variables en PL/pgSQL se realiza en la seccin DECLARE de la forma:
variable := expresin;

Donde:
-

variable (opcionalmente calificada con el nombre de un bloque): puede ser una variable
simple, un campo de una fila o record o algn elemento de un arreglo.

expresin: debe ser devolver un nico valor.

Ejemplos de asignaciones son los siguientes:

pais := 'Cuba';
impuesto := price*0.2;
nombre := (SELECT firstname FROM customers WHERE customerid = $1);

Ejecucin de consultas que arrojan resultados con una nica fila

Para almacenar el resultado de un comando SQL que retorne una fila puede utilizarse una variable
de tipo RECORD, ROWTYPE o una lista de variables escalares, lo que puede hacerse aadiendo la
clusula INTO al comando (SELECT, INSERT, UPDATE o DELETE con clusula RETURNING
y comandos de utilidad que retornan filas, como EXPLAIN), de la forma:
SELECT expresin INTO [STRICT] variable(s) FROM;
INSERT RETURNING expresin INTO [STRICT] variable(s);
UPDATE RETURNING expresin INTO [STRICT] variable(s);
DELETE RETURNING expresin INTO [STRICT] variable(s);

Donde de emplearse ms de una variable deben estar separadas por coma. Si una fila o lista de
variables es usada, las columnas resultantes de la consulta deben coincidir exactamente con la
misma estructura de las variables y sus tipos de datos o se generar un error en tiempo de ejecucin.
Si STRICT no es especificado, la variable almacenar la primera fila retornada por la consulta (no
bien definida a menos que se emplee ORDER BY) o nulo si no arroja resultados, siendo descartadas
el resto de las filas. De ser especificado, si la consulta devuelve ms de una fila se genera un error
en tiempo de ejecucin. No obstante, en el caso de las consultas INSERT, UPDATE o DELETE,
43

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

aun cuando no sea especificado el STRICT se genera el error si el resultado tiene ms de una fila,
ya que como estas no tienen la opcin ORDER BY no se puede determinar cul de las filas del
resultado se debera devolver.
Los ejemplos siguientes muestran el empleo de estos comandos para capturar una fila del resultado.
Ejemplo 28: Captura de una fila resultado de consultas SELECT, INSERT, UPDATE o DELETE

-- Guardar el resultado del SELECT en la variable fecha

SELECT orderdate INTO fecha FROM orders WHERE orderid = 1;
-- Guardar el resultado de firstname del SELECT en la variable nombre
SELECT firstname INTO nombre FROM customers ORDER BY firstname DESC;
-- Guardar el resultado del UPDATE en la variable pd de tipo record
UPDATE products SET title = 'Habana Eva' WHERE prod_id = 1 RETURNING * INTO pd;

Ejecucin de comandos dinmicos

Existen escenarios donde se hace inevitable generar comandos dinmicos en las funciones
PL/pgSQL, o sea, comandos que involucren diferentes tablas o tipos de datos cada vez que sean
ejecutados. Para ello se utiliza la sentencia EXECUTE con la sintaxis:
EXECUTE cadena [ INTO [ STRICT ] variable(s) ] [ USING expresin [,...] ];

Donde:
-

cadena: expresin de tipo texto que contiene el comando a ser ejecutado.

variable(s): almacena el resultado de la consulta, puede ser de tipo RECORD, fila o lista de
variables simples separados por coma, con las especificaciones explicadas previamente para
el empleo de la clusula INTO (ver epgrafe Ejecucin de consultas que arrojan resultados
con una nica fila), que de no ser especificada son descartadas las filas resultantes.

expresin USING: suministra valores a ser insertados en el comando.

La sentencia ejecutada con este comando es planeada cada vez que es ejecutado el EXECUTE, por
lo que la cadena que la contiene puede ser creada dinmicamente dentro de la funcin.
Este comando es especialmente til cuando se necesita usar valores de parmetros en la cadena a ser
ejecutada que involucren tablas o tipos de datos dinmicos.
Para su empleo se deben tener en cuenta los siguientes elementos:

44

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

Los smbolos de los parmetros ($1, $2, $n) pueden ser usados solamente para valores de
datos, no para hacer referencia a tablas o columnas

Un EXECUTE con un comando constante (como en la primera sentencia del ejemplo 29) es
equivalente a escribir la consulta directamente en PL/pgSQL, la diferencia radica en que
EXECUTE replanifica el comando para cada ejecucin generando un plan especfico para
los valores de los parmetros empleados, mientras que PL/pgSQL crea un plan genrico y
lo reutiliza, por lo que en situaciones donde el mejor plan dependa de los valores de los
parmetros se recomienda el empleo de EXECUTE.

La ejecucin de consultas dinmicas requiere un manejo cuidadoso ya que pueden contener

caracteres de acotado que de no tratarse adecuadamente pueden generar errores en tiempo
de ejecucin. Para ello se pueden emplear las siguientes funciones:

quote_ident: empleada en expresiones que contienen identificadores de tablas o

columnas.

quote_literal: empleada en expresiones que contienen cadenas literales.

quote_nullable: funciona igual que literal, pero es empleada cuando pueden haber
parmetros nulos, retornando una cadena nula y no derivando en un error de
EXECUTE al convertir todo el comando dinmico en nulo.

Las consultas dinmicas pueden ser escritas, adems, de forma segura, mediante el empleo
de la funcin format, que resulta ser una manera eficiente, ya que los parmetros no son
convertidos a texto.

Los siguientes ejemplos demuestran los elementos previamente analizados.

Ejemplo 29: Empleo del comando EXECUTE en consultas constantes y dinmicas

-- Empleo del comando EXECUTE en consultas constantes

EXECUTE 'SELECT * FROM customers WHERE customerid = $1';
-- Consulta dinmica que recibe por parmetro el nombre de la tabla a eliminar
EXECUTE 'DROP TABLE IF EXISTS ' || $1 || ' CASCADE';
-- Consulta dinmica que actualiza un campo de la tabla products, recibiendo por
-- parmetros la columna a actualizar, el nuevo valor y su identificador
EXECUTE 'UPDATE products SET ' || quote_ident($1) || ' = ' || quote_nullable($2) || '
WHERE prod_id = ' || quote_literal($3);

45

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

Note que para la ejecucin de consultas dinmicas debe dejarse un espacio en blanco entre las
cadenas a unir de la consulta preparada, de no hacerse se genera un error ya que al convertir toda la
cadena lo hace sin espacios entre los elementos concatenados.
3.5 Estructuras de control
PL/pgSQL incorpora estructuras de control (condicionales e iterativas) para imprimirle mayor
flexibilidad y poder al lenguaje mediante variadas opciones para la manipulacin de los datos.
Estructuras condicionales
PL/pgSQL implementa 5 formas de los condicionales IF y CASE que permiten la ejecucin de
comandos basados en ciertas condiciones como se muestra en los ejemplos del 30 al 34.
Tabla 1: Estructuras condicionales implementadas por PL/pgSQL

Tipo
IF-THEN

Sintaxis
IF expresin_booleana THEN
sentencias;

Uso
Forma ms simple del IF, las sentencias
son ejecutadas si la condicin es verdadera.

END IF;
IF-THEN-

IF expresin_booleana THEN

ELSE

sentencias;

Aade al tipo anterior la clusula ELSE

para especificar sentencias alternativas a
ejecutar cuando no se cumpla la condicin

ELSE

definida.
sentencias;

END IF;
IF-THEN-

IF expresin_booleana THEN

ELSIF

sentencias;

Empleada cuando hay ms de 2

alternativas a evaluar, evala cada
condicin IF hasta que encuentre una

[ELSIF expresin_booleana THEN

sentencias;

verdadera, en ese caso ejecuta las

sentencias asociadas y no evala ningn IF
restante; en caso de que no sea evaluada de

verdadera ninguna condicin y exista la

[ELSE

clusula ELSE, las sentencias asociadas a

sentencias;]

esta son ejecutadas.

END IF;

46

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

CASE

Programacin de funciones en PL/pgSQL

CASE expresin_bsqueda
WHEN expresin [,expresin [...]] THEN

Forma ms simple del CASE que permite

la ejecucin de sentencias basadas en
condicionales de operadores de igualdad.

sentencias;
[WHEN expresin [,expresin [.. ]] THEN
sentencias;...]
[ELSE

END CASE;

buscado

vez y posteriormente comparada con cada

expresin en la clusula WHEN, de
coincidir son ejecutadas las sentencias
asociadas a dicha clusula ignorando el

sentencias;]

CASE

La expresin de bsqueda es evaluada una

CASE

resto de las clusulas WHEN o ELSE

existentes; de no coincidir es ejecutada la
clusula ELSE si existe.
Permite la ejecucin de condicionales

WHEN expresin_booleana THEN

basadas en una expresin booleana. Cada

expresin de la clusula WHEN es

sentencias;
[WHEN expresin_booleana THEN
sentencias;

evaluada en cada turno hasta que una es

verdadera, ejecutndose las sentencias
asociadas e ignorndose el resto de las
clusulas en la estructura; al igual que en la

...]

anterior, de no haber una expresin

[ELSE

evaluada de verdadera se ejecuta la

sentencias;]

clusula ELSE de existir.

END CASE;
Ejemplo 30: Empleo de la estructura condicional IF-THEN implementada en PL/pgSQL

-- Insertar un nuevo producto si tiene un precio superior a los 25 pesos

IF price > 25.0 THEN
INSERT INTO products VALUES ($1, $2, $3, $4, $5, $6, $7);
END IF;
Ejemplo 31: Empleo de la estructura condicional IF-THEN-ELSE implementada en PL/pgSQL

-- Insertar un nuevo producto si tiene un precio superior a los 25 pesos si menor es

-- mostrar una notificacin especificando que el producto es muy barato
IF price > 25.0 THEN
INSERT INTO products VALUES ($1, $2, $3, $4, $5, $6, $7);

47

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

ELSE
RAISE NOTICE 'El producto es muy barato';
END IF;
Ejemplo 32: Empleo de la estructura condicional IF-THEN-ELSIF implementada en PL/pgSQL

-- Insertar un nuevo producto si tiene un precio entre los 25 y los 50 pesos, si es menor
-- muestra una notificacin indicando que el producto es muy barato, en caso contrario
-- que es muy caro
IF price BETWEEN 25.0 AND 50.0 THEN
INSERT INTO products VALUES ($1, $2, $3, $4, $5, $6, $7);
ELSIF price < 25.00 THEN
RAISE NOTICE 'El producto es muy barato';
ELSE
RAISE NOTICE 'El producto es muy caro';
END IF;
Ejemplo 33: Empleo de la estructura condicional CASE implementada en PL/pgSQL

-- Evaluar si el producto es de categora infantil (2, 3) u otra

CASE category
WHEN 2, 3 THEN
RAISE NOTICE 'El producto es de categora Infantil;
ELSE
RAISE NOTICE 'El producto no es de categora Infantil';
END CASE;
Ejemplo 34: Empleo de la estructura condicional CASE buscado implementada en PL/pgSQL

-- Evaluar si el producto es de categora infantil (2, 3), drama (5, 7, 8) u otra

CASE
WHEN (category = 2 OR category = 3) THEN
RAISE NOTICE 'El producto es de categora Infantil';

48

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

WHEN (category = 5 OR category = 7 OR category = 8) THEN

RAISE NOTICE 'El producto es de categora Drama';
ELSE
RAISE NOTICE 'El producto tiene otra categora';
END CASE;

Como evidencian los ejemplos anteriores, pueden utilizarse operadores lgicos como AND y OR en
las sentencias condicionales. De modo general, este tipo de sentencias son muy tiles sobre todo
para el control de flujo, donde puede ejecutarse una accin u otra en funcin de las condiciones que
se cumplan.
El uso de IF o CASE depende de las preferencias del programador pues con ambas puede lograrse
lo mismo. En ocasiones una otorga ms legibilidad al cdigo lo que puede facilitar el
mantenimiento del mismo; por ejemplo, cuando son muchas condiciones a chequear en los valores
de las variables el CASE puede ser ms legible, pero si son pocas las condiciones a chequear el IF
suele ser la opcin ms empleada.
Estructuras iterativas
PL/pgSQL implementa varias formas iterativas y de control utilizadas en el ejemplo 35.
Tabla 2 Estructuras iterativas implementadas en PL/pgSQL

Tipo
LOOP
simple

Sintaxis
[<<etiqueta>>]

Uso
Define un ciclo incondicional que es
repetido indefinidamente hasta que

LOOP

encuentra una sentencia EXIT o

sentencias;
END LOOP [etiqueta];

RETURN. La etiqueta puede ser usada

para sentencias EXIT o CONTINUE en
LOOPs anidados.

EXIT

EXIT [etiqueta] [WHEN expresin_booleana];

Termina la ejecucin de un LOOP o un

bloque; en caso de especificarse la
etiqueta termina el ciclo o bloque
etiquetado, siendo la siguiente
sentencia la especificada despus del
END asociado a la estructura

49

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

terminada; de no hacerse termina el

LOOP ms cercano siendo la siguiente
sentencia el END LOOP. De
especificarse la clusula WHEN el
EXIT ocurre de cumplirse la condicin.
CONTINUE

CONTINUE [etiqueta] [WHEN

Contina la ejecucin del LOOP

expresin_booleana];

especificado (mediante la etiqueta o el

propio LOOP en ejecucin); de
especificarse la clusula WHEN la
prxima iteracin del LOOP inicia slo
si la condicin es verdadera, en caso
contrario el control pasa a la sentencia
despus del CONTINUE.

WHILE

[<<etiqueta>>]

Repite una secuencia de sentencias

mientras la condicin evaluada sea

WHILE expresin_booleana LOOP

verdadera, la expresin booleana es

sentencias;

FOR

chequeada antes de cada entrada en el

END LOOP [etiqueta];

ciclo LOOP.

[<<etiqueta>>]

Crea un LOOP que itera sobre un rango

FOR

nombre

IN

[REVERSE]

expresin [BY expresin ] LOOP

expresin..

de valores enteros. La variable nombre

es automticamente definida de tipo
entero y existe solamente dentro del

sentencias;
END LOOP [etiqueta];

ciclo; las 2 expresiones delimitan los

lmites inferior y superior del rango; la
clusula BY especifica el incremento
en cada iteracin (por defecto 1); la
clusula REVERSE especifica que en
lugar de incrementarse el valor a iterar
se decrementa; las expresiones son
evaluadas en cada entrada al ciclo
LOOP.

FOR
recorriendo

[<<etiqueta>>]
FOR variable IN consulta LOOP

resultado de
una consulta

Permite iterar por el resultado de una

consulta, y manipular los datos de cada
tupla. La variable es de tipo RECORD,

sentencias;
END LOOP [etiqueta];

fila o un listado de variables escalares

separadas por coma.

50

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

Ejemplo 35: Empleo de las estructuras iterativas implementadas por PL/pgSQL usando LOOP y WHILE

-- Cambie el precio de un producto pasado por parmetro, duplicndolo el total de veces

-- pasado como segundo parmetro, retornar el precio final haciendo uso del LOOP
CREATE FUNCTION cambiar_precio(id integer, cant integer) RETURNS numeric AS
$$
DECLARE
contador integer := 0;
precio numeric;
BEGIN
LOOP
UPDATE products SET price = price * 2 WHERE prod_id = id RETURNING
price INTO precio;
contador := contador + 1;
IF contador = cant THEN
EXIT;
END IF;
END LOOP;
RETURN precio;
END;
$$ LANGUAGE plpgsql;
dell=# SELECT cambiar_precio(131, 2);
cambiar_precio
39.96
(1 fila)
-- Cambie el precio de un producto pasado por parmetro, duplicndolo el total de veces
-- pasado como segundo parmetro, retornar el precio final haciendo uso del WHILE
CREATE FUNCTION cambiar_precio(id integer, cant integer) RETURNS numeric AS
$$

51

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

DECLARE
contador integer := 0;
precio numeric;
BEGIN
WHILE contador < cant LOOP
UPDATE products SET price = price * 2 WHERE prod_id = id RETURNING
price INTO precio;
contador := contador + 1;
END LOOP;
RETURN precio;
END;
$$ LANGUAGE plpgsql;

El uso del FOR puede analizarse en el Ejemplo 37.

Como se muestra en los ejemplos anteriores, existen varias estructuras iterativas para el control de
los ciclos. La eleccin de un caso u otro depende del escenario y de las preferencias del
programador; en ocasiones una otorga mayor legibilidad y elegancia al cdigo, lo cual contribuye al
entendimiento y mantenimiento del mismo.
3.6 Retorno de valores
PL/pgSQL implementa 2 comandos que permiten devolver datos de una funcin: RETURN y
RETURN NEXT|QUERY.
La clusula RETURN es empleada cuando la funcin no devuelve un conjunto de datos. Tiene la
forma:
RETURN expresin;

Para su empleo se debe tener en cuenta que esta clusula:

-

Devuelve el valor de evaluar la expresin terminando la ejecucin de la funcin.

De haberse definido una funcin con parmetros de salida no se hace necesario especificar
ninguna expresin en ella.

52

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

En funciones que retornen el tipo de dato VOID puede emplearse sin especificar ninguna
expresin para terminar la funcin tempranamente.

No debe dejar de especificarse en una funcin (excepto en los casos mencionados

anteriormente), ya que genera un error en tiempo de ejecucin.

Lo que se devuelve debe tener el mismo tipo de datos que el declarado en la clusula
RETURNS en el encabezado de la funcin.

El ejemplo 36 muestra su empleo en el cuerpo de una funcin.

Ejemplo 36: Empleo de la clusula RETURN para devolver valores y culminar la ejecucin de una funcin

-- Funcin que dado el identificador de un producto devuelve su ttulo, empleo de la

-- clusula RETURN para devolver un dato escalar
CREATE FUNCTION devolver_producto(integer) RETURNS varchar AS
$$
DECLARE
prod varchar;
BEGIN
SELECT title INTO prod FROM products WHERE prod_id = $1;
RETURN prod;
END;
$$ LANGUAGE plpgsql;
-- La misma funcin anterior pero empleando parmetros de salida, note que en este
caso -- la clusula RETURN no necesita tener una expresin asociada
CREATE FUNCTION devolver_producto(integer, OUT varchar) RETURNS varchar AS
$$
BEGIN
SELECT title INTO $2 FROM products WHERE prod_id = $1;
RETURN;
END;
$$ LANGUAGE plpgsql;
-- Funcin que devuelve dado el identificador de un producto todos sus datos, empleo de

53

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

-- la clusula RETURN para devolver un dato compuesto, en este ejemplo hay que
-- garantizar que el resultado devuelva una sola tupla, en caso contrario la funcin
-- devuelve la primera del resultado
CREATE FUNCTION datos_producto(integer) RETURNS products AS
$$
DECLARE
prod products;
BEGIN
SELECT * INTO prod FROM products WHERE prod_id = $1;
RETURN prod;
END;
$$ LANGUAGE plpgsql;
-- Funcin que devuelve dado el identificador de un producto su nombre, empleo de la
-- clusula RETURN para devolver de un dato compuesto un elemento
CREATE FUNCTION datos_producto_titulo(integer) RETURNS varchar AS
$$
DECLARE
prod RECORD;
BEGIN
SELECT * INTO prod FROM products WHERE prod_id = $1;
RETURN [Link];
END;
$$ LANGUAGE plpgsql;
-- La misma funcin anterior pero utilizando un tipo de dato compuesto que contiene
-- solamente el nombre y el precio del producto
CREATE TYPE mini_prod AS (nombre varchar, precio numeric);
CREATE FUNCTION datos_producto_mini_prod(integer) RETURNS mini_prod AS
$$

54

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

DECLARE
prod mini_prod;
BEGIN
SELECT * INTO prod FROM products WHERE prod_id = $1;
RETURN mini_prod;
END;
$$ LANGUAGE plpgsql;

La clusula RETURN NEXT o RETURN QUERY es empleada cuando la funcin devuelve un

conjunto de datos. Tiene la forma:
RETURN NEXT expresin;
RETURN QUERY consulta;

Para su empleo se debe tener en cuenta que estas clusulas (ver su uso en el ejemplo 37):
-

Son empleadas cuando la funcin es declarada para que devuelva SETOF algn_tipo.

RETURN NEXT se emplea con tipos de datos compuestos, RECORD o fila.

RETURN QUERY aade el resultado de ejecutar una consulta al conjunto resultante de la

funcin.

Ambos pueden ser usados en una misma funcin, concatenndose ambos resultados.

No culminan la ejecucin de la funcin, simplemente aaden cero o ms filas al resultado

de la funcin, puede emplearse un RETURN sin argumento para salir de la funcin.

De declararse parmetros de salida se puede especificar el RETURN NEXT sin una

expresin, en cuyo caso la funcin debe declararse para que retorne SETOF record.

Ejemplo 37: Empleo de RETURN NEXT|QUERY para devolver un conjunto de valores resultante de una consulta

-- Funcin que devuelve todos los productos registrados en la base de datos que su
-- identificador sea mayor que el pasado por parmetro, note el empleo de un FOR para
-- iterar por el resultado de la consulta
CREATE FUNCTION datos_producto_setof(integer) RETURNS SETOF products AS
$$
DECLARE
resultado products;

55

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

BEGIN
FOR resultado IN SELECT * FROM products where prod_id > $1 LOOP
RETURN NEXT resultado;
END LOOP;
RETURN; -- Opcional
END;
$$ LANGUAGE plpgsql;
-- La misma funcin pero empleando el RETURN QUERY
CREATE FUNCTION datos_producto_setof(integer) RETURNS SETOF products AS
$$
BEGIN
RETURN QUERY SELECT * FROM products where prod_id > $1;
RETURN; -- Opcional
END;
$$ LANGUAGE plpgsql;
-- La misma funcin pero empleando RETURN QUERY y devolviendo RECORD. Para
-- ejecutarla se debe especificar qu estructura debe tener el resultado de la forma
-- SELECT * FROM datos_producto_record(1000) AS (prod_id int, category integer, title
-- character varying(50), actor character varying(50), price numeric(12,2), special smallint,
-- common_prod_id integer)
CREATE FUNCTION datos_producto_record(integer) RETURNS SETOF record AS
$$
BEGIN
RETURN QUERY SELECT * FROM products where prod_id > $1;
RETURN; -- Opcional
END;
$$ LANGUAGE plpgsql;

3.7 Mensajes

56

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

En ejemplos analizados previamente se ha hecho uso de mensajes utilizando la clusula RAISE con
la opcin NOTICE. Adems de esta opcin, la clusula RAISE permite las opciones DEBUG,
LOG, INFO, WARNING y EXCEPTION, esta ltima utilizada por defecto:
-

NOTICE: es utilizado para hacer notificaciones.

WARNING: es utilizado para hacer advertencias.

LOG: es utilizado para dejar constancia en los logs de PostgreSQL del mensaje o error.

EXCEPTION: es utilizado para lanzar una excepcin, cancelndose todas las operaciones
realizadas previamente en la funcin.
Para mayor detalles sobre las opciones de RAISE puede analizar en la Documentacin Oficial la
seccin Errors and Messages

El ejemplo 38 muestra casos donde es utilizado RAISE con varios de los niveles de mensajes que
permite.
Ejemplo 38: Mensajes utilizando las opciones RAISE: EXCEPTION, LOG y WARNING

-- Funcin que devuelve dado el identificador de un producto su ttulo, empleo de la

-- clusula RETURN para devolver un dato escalar, uso de FOUND y EXCEPTION para
-- determinar si lo encontr
CREATE FUNCTION producto(integer) RETURNS varchar AS
$$
DECLARE
prod varchar;
BEGIN
SELECT title INTO prod FROM products WHERE prod_id = $1;
IF not found THEN
RAISE EXCEPTION 'Producto % no encontrado', $1;
END IF;
RETURN prod;
END;
$$ LANGUAGE plpgsql;
-- Invocacin de la funcin

57

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

dell=# SELECT producto(1000000);

ERROR: Producto 1000000 no encontrado
-- Empleo de la clusula RETURN para devolver un dato escalar, uso de FOUND, LOG y
-- WARNING para determinar si lo encontr y registrar el error en el log de PostgreSQL
CREATE OR REPLACE FUNCTION producto(integer) RETURNS varchar AS
$$
DECLARE
prod varchar;
BEGIN
SELECT title INTO prod FROM products WHERE prod_id = $1;
IF not found THEN
RAISE LOG 'Producto % no encontrado', $1;
RAISE WARNING 'Producto % no encontrado, es posible que no se haya
insertado an', $1;
END IF;
RETURN prod;
END;
$$ LANGUAGE plpgsql;
-- Invocacin de la funcin
dell=# SELECT producto(1000000);
WARNING: Producto 1000000 no encontrado, es posible que no se haya insertado an
producto
(1 fila)

Ntese que se ha utilizado la variable especial FOUND, que es de tipo booleano, que por defecto en
PL/pgSQL es FALSE y se activa luego de:
-

Una asignacin de un SELECT INTO que haya arrojado un resultado efectivo almacenado
en la variable.

58

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

Las operaciones UPDATE, INSERT y DELETE que hayan realizado alguna accin efectiva
sobre la base de datos.

Las operaciones RETURN QUERY y RETURN QUERY EXECUTE que hayan retornado
algn valor.
Existen otros casos donde se activa la variable FOUND, puede ver la seccin Obtaining the Result
Status de la Documentacin Oficial

Tambin puede utilizarse un bloque de excepciones para hacer tratamiento de las mismas como se
muestra en el ejemplo 39.
Ejemplo 39: Bloque de excepciones

-- Funcin que valida si una consulta pasada por parmetro se puede ejecutar en la base
-- de datos
CREATE OR REPLACE FUNCTION valida_consulta(consulta varchar) RETURNS void AS
$$
BEGIN
EXECUTE $1;
EXCEPTION
WHEN syntax_error THEN
RAISE EXCEPTION 'Consulta con problemas de sintaxis';
WHEN undefined_column OR undefined_table THEN
RAISE EXCEPTION 'Columna o tabla no vlida';
END;
$$ LANGUAGE plpgsql;
-- Invocacin de la funcin
dell=# SELECT * FROM valida_consulta('select * from catego');
ERROR: Columna o tabla no vlida

3.8 Disparadores
Para ejecutar alguna actividad o accin en la base de datos es necesario que se invoque una funcin
que contendr el cdigo que se desea ejecutar, como se ha visto hasta ahora, pero cmo se resuelve
un problema en que haya que ejecutar una accin en la base de datos cuando ocurra algn evento
59

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

especfico en las tablas de la misma? Para dar solucin a este problema existen los
desencadenadores o disparadores, conocidos mayormente como triggers (del ingls).
Un disparador es una accin que se ejecuta automticamente cuando se cumple una condicin
establecida al realizar una operacin sobre la base de datos. Por ejemplo, puede ser la realizacin de
una actividad determinada antes de insertar un registro nuevo en la tabla categories.
Algunas ventajas de la utilizacin de disparadores son las siguientes:
-

Se ejecutan automticamente cuando ocurre determinada actividad en la base de datos.

Permiten la implementacin de requisitos complejos mientras se manejan los datos.

Pueden exigir restricciones ms complejas que las definidas con restricciones CHECK.

Son un buen modo de realizar auditoras en las bases de datos.

Disparadores en PostgreSQL
PostgreSQL permite la implementacin de disparadores mediante la definicin de una funcin
disparadora, y luego el respectivo disparador que llame a dicha funcin; esto posibilita a varios
disparadores utilizar la misma funcin disparadora sin necesidad de reescribir cdigo.
La definicin de la funcin disparadora es similar a la de una funcin normal de PL/pgSQL, con la
peculiaridad de que no se especifican parmetros y que debe retornar un tipo de dato especial
llamado TRIGGER. Dentro de la funcin se puede escribir cdigo en PL/pgSQL y se debe
garantizar que se retorne algn valor ya sea NULL, RECORD o una fila con la misma estructura de
la tabla que lo invoca.
El ejemplo 40 muestra una funcin disparadora simple que notifica que ha sido invocado un
disparador.
Ejemplo 40: Implementacin de una funcin disparadora

-- Funcin disparadora que notifica que ha sido invocado un disparador

CREATE OR REPLACE FUNCTION funcion_disparadora() RETURNS trigger AS
$$
BEGIN
RAISE NOTICE 'Un disparador ha sido invocado';
RETURN null;
END;

60

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

$$ LANGUAGE plpgsql;

Para que esta funcin se ejecute debe definirse un disparador que la invoque cuando ocurra
determinado evento, sobre determinado objeto de la base de datos y en determinado momento.
La sintaxis bsica para la definicin de un disparador se muestra a continuacin, la sintaxis
ampliada se puede ver en la Documentacin Oficial en la seccin SQL Commands.
CREATE TRIGGER nombre
{ BEFORE | AFTER | INSTEAD OF } { evento [OR] }
ON tabla
[FOR EACH { ROW | STATEMENT } ]
[WHEN ( condicin ) ]
EXECUTE PROCEDURE funcin_disparadora()

Donde evento puede ser INSERT, UPDATE, DELETE o TRUNCATE.

Un ejemplo de disparador para la invocacin de la funcin definida anteriormente puede ser el
mostrado en el ejemplo 41.
Ejemplo 41: Disparador que invoca la funcin disparadora del ejemplo 40

CREATE TRIGGER trigger_uno

AFTER INSERT
ON categories
FOR EACH ROW
EXECUTE PROCEDURE funcion_disparadora();

El ejemplo 41 especifica que la funcin disparadora previamente definida se ejecutar despus de

haberse insertado un registro en la tabla categories.
En el ejemplo 42 se define un disparador que invocar la funcin disparadora cuando ocurra ms de
un evento, a diferencia del ejemplo anterior que slo la invocaba cuando se realizaba una insercin.
Ejemplo 42: Disparador que invoca la funcin disparadora cuando se realiza una insercin, actualizacin o eliminacin
sobre la tabla categories

CREATE TRIGGER trigger_dos

AFTER INSERT or UPDATE or DELETE
61

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

ON categories
FOR EACH ROW
EXECUTE PROCEDURE funcion_disparadora();

Para definir cul de los eventos invoc la funcin disparadora, PostgreSQL habilita una serie de
variables especiales que contienen informacin sobre el disparador como cundo ocurri, qu
evento, etc.:
-

TG_OP: variable de tipo cadena que indica qu tipo de evento est ocurriendo (INSERT,
UPDATE, DELETE, TRUNCATE), siempre en mayscula.

TG_WHEN: variable de tipo cadena que indica el momento en que se invocar el

disparador (BEFORE, AFTER, INSTEAD OF), siempre en mayscula.

TG_RELNAME o TG_TABLE_NAME: variable de tipo cadena que almacena el nombre

de la tabla sobre la cual se est trabajando cuando se invoc el disparador.

NEW: variable de tipo RECORD que almacena los nuevos valores de la fila que se est
insertando o modificando.

OLD: variable de tipo RECORD que almacena los valores antiguos de la fila que se est
modificando o eliminando.
Existen otras variables especiales que se pueden analizar en detalle en la seccin Triggers on data
changes de la Documentacin Oficial

FOR EACH en la definicin del disparador

Una vez definida la funcin disparadora y el disparador, este estar observando la tabla para la que
fue creado y una vez que ocurra el evento especificado se invocar la funcin. El ejemplo 43
muestra una consulta que ejecuta el disparador definido sobre la tabla categories en el ejemplo
anterior.
Ejemplo 43: Ejecucin de una consulta que activa el disparador creado sobre la tabla categories

-- Ejecucin de una consulta de actualizacin sobre la tabla categories

dell=# UPDATE categories SET categoryname=upper(categoryname) WHERE category >=
15;
NOTICE: Un disparador ha sido invocado
NOTICE: Un disparador ha sido invocado
UPDATE 2

62

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

En el ejemplo anterior puede verse cmo se ejecuta la funcin disparadora en dos ocasiones ya que
fueron lanzadas dos notificaciones, esto indica que fueron afectadas con la consulta dos filas, y est
dado porque en la definicin del disparador se emple el FOR EACH ROW, que significa que el
disparador se ejecuta por cada fila afectada. Sin embargo, si se define en su lugar FOR EACH
STATEMENT, la funcin disparadora se ejecutara en una sola ocasin pues se le indica que se
ejecutar por cada sentencia SQL ejecutada.
Si se define el disparador como se muestra en el ejemplo 44 y se ejecuta la misma sentencia de
actualizacin, se mostrara otro mensaje indicando la ejecucin una sola vez.
Ejemplo 44: Definicin de la funcin disparadora y su disparador asociado que se dispara cuando se realiza alguna
modificacin sobre la tabla categories

-- Funcin disparadora que notifica que ha sido invocado un disparador para

modificacin
CREATE OR REPLACE FUNCTION trigger_para_modificacion() RETURNS trigger AS
$$
BEGIN
RAISE NOTICE 'Un disparador se ha invocado para modificacin';
RETURN null;
END;
$$ LANGUAGE plpgsql;
-- Disparador que invoca la funcin anterior una vez realizada una actualizacin o
-- eliminacin sobre la tabla categories
CREATE TRIGGER trigger_modificacion
AFTER UPDATE or DELETE
ON categories
FOR EACH STATEMENT
EXECUTE PROCEDURE trigger_para_modificacion();
-- Invocacin del disparador una vez ejecutada una actualizacin sobre la tabla
categories
dell=# UPDATE categories SET categoryname=upper(categoryname) WHERE category >=

63

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

15;
NOTICE: Un disparador se ha invocado para modificacin
UPDATE 2

Es vlido destacar que la sentencia FOR EACH STATEMENT se ejecuta siempre que la consulta
SQL cumpla con alguno de los eventos sobre la tabla para la cual fue definida, aun cuando no afecte
ninguna fila; a diferencia de FOR EACH ROW que solo se ejecuta cuando se afecte al menos una
fila. El ejemplo 45 demuestra lo anterior.
Ejemplo 45: Ejecucin de trigger_modificacion en lugar de trigger_uno debido a que no se actualiza ninguna tupla sobre
la tabla categories con la consulta ejecutada

-- Invocacin de trigger_modificacion una vez ejecutada una actualizacin sobre la tabla

-- categories
dell=# UPDATE categories SET categoryname=upper(categoryname) WHERE category >=
15;
NOTICE: Un disparador se ha invocado para modificacin
UPDATE 0

Note que en este ejemplo el disparador que se dispara es trigger_modificacion en lugar de

trigger_uno porque el primero utiliza la sentencia FOR EACH STATEMENT en lugar de FOR
EACH ROW.
El evento TRUNCATE solo puede ser utilizado con la sentencia FOR EACH STATEMENT

El retorno de la funcin disparadora

Otro aspecto a tener en cuenta para el trabajo con disparadores en PostgreSQL es el retorno de la
funcin disparadora. Si el disparador se define con la opcin:
-

BEFORE: y la funcin retorna NULL, las tuplas indicadas en la sentencia SQL no se vern
afectadas. Esto posibilita que, incluso, se pueda modificar el valor del nuevo registro en
determinada tabla antes de insertarlo o, simplemente, si no cumple con determinada
condicin la decisin sea no insertarlo.

AFTER: la funcin puede retornar NULL, NEW o cualquier otro valor que cumpla con los
requisitos de devolucin de la funcin debido a que ya el valor est insertado en la tabla.

64

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

El ejemplo 46 muestra cmo un disparador permite modificar el valor de un nuevo registro antes de
insertarse. En este caso si se inserta un nuevo cliente y el nombre no comienza con mayscula se
modifica y se inserta el mismo con esta condicin cumplida.
Ejemplo 46: Disparador que permite modificar un nuevo registro antes de insertarlo en la base de datos

-- Funcin disparadora que pone en mayscula la primera letra del nombre

CREATE OR REPLACE FUNCTION mayuscula_nombre() RETURNS trigger AS
$$
DECLARE
resultado text;
BEGIN
-- Validando que el nombre comience con mayscula
IF

ascii(substring([Link]

from

for

1)

>=

65

AND

ascii(substring([Link] from 1 for 1)) <= 90 THEN

RAISE NOTICE 'Formato correcto';
RETURN NEW;
ELSE
-- Si no comienza con mayscula se arregla antes de insertarlo
resultado:= upper(substring([Link] from 1 for
1)) || substring([Link] from 2 for length([Link]));
RAISE NOTICE 'Formato incorrecto. % es el correcto', resultado;
-- Se asigna el nuevo valor con la correccin a la variable especial NEW
[Link] := resultado;
-- Se retorna el nuevo valor
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
-- Definicin del disparador

65

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

CREATE TRIGGER trigger_modificar_insercion

BEFORE INSERT
ON customers
FOR EACH ROW
EXECUTE PROCEDURE mayuscula_nombre();
--Ejecucin de una insercin sobre la tabla customers con la primera inicial del nombre
en -- mayscula
dell=# INSERT INTO customers(firstname, lastname, address1, address2, city, state,
country, region, creditcardtype, creditcard, creditcardexpiration, username, password)
VALUES ('Anthony', 'Sotolongo', 'dir1', 'dir2', 'Cienfuegos', 'Cienfuegos', 'Cuba', 2, 1, 'una
tarjeta crdito', 'algo', 'asotolongo', 'mipasss');
NOTICE: Formato correcto
UPDATE 0
INSERT 0 1
--Ejecucin de una insercin sobre la tabla customers sin la primera inicial del nombre
en

-- mayscula

dell=# INSERT INTO customers(firstname, lastname, address1, address2, city, state,

country, region, creditcardtype, creditcard, creditcardexpiration, username, password)
VALUES ('luis', 'Sotolongo', 'dir', 'dir2', 'Cienfuegos', 'Cienfuegos', 'Cuba', 2, 1, 'una tarjeta
crdito', 'algo', 'lsotolongo', 'mipasss');
NOTICE: Formato incorrecto. Luis es el correcto
INSERT 0 1

Este comportamiento puede ser utilizado para evitar que se eliminen datos de alguna tabla
especfica. Por ejemplo, de ser necesario garantizar que no se eliminen datos de la tabla orders,
pudiera desarrollarse un disparador en el que se defina que antes de eliminar un registro de esa tabla
se ejecute determinada funcin disparadora que retorne NULL. El ejemplo 47 muestra una
implementacin para ello.
Ejemplo 47: Disparador que no permite eliminar de una tabla

-- Funcin disparadora que impide eliminar registros de una tabla

CREATE OR REPLACE FUNCTION asegurar_datos() RETURNS trigger AS
66

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

$$
BEGIN
RAISE NOTICE 'No se pueden eliminar datos de esta tabla';
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- Definicin del disparador
CREATE TRIGGER trigger_asegurar_datos
BEFORE DELETE
ON orders
FOR EACH ROW
EXECUTE PROCEDURE asegurar_datos()
--Ejecucin de una consulta de eliminacin sobre la tabla orders
dell=# DELETE FROM orders WHERE orderid=11000;
NOTICE: No se pueden eliminar datos de esta tabla
DELETE 0

Haciendo auditora con disparadores

Uno de los empleos ms significativos de los disparadores es para el registro de cambios en la base
de datos, algo as como un log de cambios de determinada tabla. Ello es posible mediante el uso de
las variables especiales NEW y OLD, que permiten acceder a los nuevos y antiguos valores
respectivamente. El ejemplo 48 muestra cmo se almacenan en una tabla llamada registro los
cambios de modificacin o eliminacin realizados sobre la tabla customers, donde, adems, se
almacenar la operacin y la fecha en que fue realizada.
Ejemplo 48: Forma de realizar auditoras sobre la tabla customers

-- Tabla registro donde se almacenarn las operaciones realizadas sobre customers

CREATE TABLE registro (operacion text, fecha timestamp, antiguo text, nuevo text);
-- Nota: pueden utilizarse tipos de datos con mejores descripciones para el antiguo y
nuevo valor como JSON y HSTORE

67

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

--Funcin disparadora para registrar las operaciones

CREATE OR REPLACE FUNCTION registro_trigger() RETURNS trigger AS
$$
BEGIN
IF TG_OP = 'UPDATE' THEN
RAISE NOTICE 'Operacin UPDATE';
INSERT INTO registro VALUES (TG_OP, now(), OLD::text, NEW::text);
END IF;
IF TG_OP = 'DELETE' THEN
RAISE NOTICE 'Operacin DELETE';
INSERT INTO registro VALUES (TG_OP, now(),OLD::text,'');
END IF;
RETURN null;
END;
$$ LANGUAGE plpgsql;
--Disparador
CREATE TRIGGER trigger_registro
AFTER DELETE or UPDATE
ON customers
FOR EACH ROW
EXECUTE PROCEDURE registro_trigger();
-- Ejemplo de operacin DELETE
dell=# DELETE FROM customers WHERE customerid = 10000;
NOTICE: Operacin DELETE
DELETE 1
-- Ejemplo de operacin UPDATE
dell=# UPDATE customers SET firstname = lower(firstname) WHERE customerid =

68

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

12000;
NOTICE: Operacin UPDATE
UPDATE 1

Disparadores por columnas y condicionales

Desde la versin 9.0 de PostgreSQL se pueden definir disparadores por columnas sobre sentencias
UPDATE y definir alguna condicin para que se ejecute el disparador. Esto puede acarrear ventajas
de rendimiento al no ser necesario programar en la funcin disparadora lgica de negocio para que
se realice determinada accin; significa, que si se establecen estas condiciones en la definicin del
disparador no es necesaria hacer la llamada a la funcin de no cumplirse con dichas condiciones.
Los disparadores por columnas se ejecutan cuando una o varias columnas determinadas por el
usuario se actualizan. La sintaxis para definir un disparador de este tipo es la siguiente:
CREATE TRIGGER nombre
{ BEFORE | AFTER | INSTEAD OF } { UPDATE OF columna }
ON tabla
[ FOR EACH { ROW | STATEMENT } ]
EXECUTE PROCEDURE nombre_funcin();

Un ejemplo de cmo utilizarlo puede ser que cuando se modifique el valor del precio de algn
producto de la tabla products se almacene en la tabla registro; el ejemplo 49 muestra una
implementacin para ello.
Ejemplo 49: Funcin disparadora y disparador por columnas para controlar las actualizaciones sobre la columna price
de la tabla products

-- Funcin disparadora para registrar las actualizaciones sobre la columna precio

CREATE OR REPLACE FUNCTION registrar_update() RETURNS trigger AS
$$
BEGIN
RAISE NOTICE 'Operacin UPDATE sobre columna price';
INSERT INTO registro VALUES (TG_OP, now(), OLD::text, NEW::text);
RETURN null;

69

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

END;
$$ LANGUAGE plpgsql;
-- Definicin del disparador por columna
CREATE TRIGGER trigger_registro_update
AFTER UPDATE OF price
ON products
FOR EACH ROW
EXECUTE PROCEDURE registrar_update()

Los disparadores condicionales permiten especificar condiciones en la definicin del disparador,

pudiendo escribirse cualquier expresin con resultado booleano haciendo uso de los operadores
lgicos (AND, OR, etc.) exceptuando subconsultas.
El ejemplo anterior se pudiera reimplementar haciendo uso de la clusula WHEN, como muestra el
ejemplo 50.
Ejemplo 50: Definicin de un disparador condicional para chequear que se actualice el precio

CREATE TRIGGER trigger_registro_update

AFTER UPDATE
ON products
FOR EACH ROW
WHEN ([Link] IS DISTINCT FROM [Link])
EXECUTE PROCEDURE registrar_update()

Note que en este caso el disparador se activar cuando se realice una actualizacin sobre la tabla
products que, adems, cumpla la condicin de que se haya actualizado el atributo price.
Otro ejemplo de su uso pudiera ser que verificara que los nuevos valores del atributo actor
comiencen con mayscula; para que se ejecute la funcin disparadora se debe definir un disparador
del siguiente modo.
Ejemplo 51: Disparador condicional para chequear que actor comienza con maysculas

CREATE TRIGGER trigger_registrar_update

70

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

AFTER UPDATE
ON products
FOR EACH ROW
WHEN

(ascii(substring([Link]

FROM

FOR

1)

>=

65

AND

ascii(substring([Link] FROM 1 FOR 1)) <= 90)

EXECUTE PROCEDURE registrar_update()

Disparadores sobre vistas

Desde la versin 9.1 de PostgreSQL se permite realizar disparadores sobre las vistas siempre y
cuando el disparador se defina haciendo uso de:
-

INSTEAD OF y FOR EACH ROW

BEFORE | AFTER y FOR EACH STATEMENT

La vista en s no se puede manipular con DDL (INSERT, DELETE y UPDATE) pero esta versin
de PostgreSQL ya lo posibilita siempre y cuando se realice la actividad de DDL de forma manual
en las respectivas tablas involucradas. Es decir, en la funcin disparadora se debe escribir el cdigo
que realice dicha actividad en las tablas relacionadas en la vista.
El ejemplo 52 describe cmo hacer un disparador sobre la vista inventario_de_productos para que
sea actualizada.
Ejemplo 52: Funcin disparadora y disparadores necesarios para actualizar una vista

-- Definicin de la vista inventario_de_productos

CREATE VIEW inventario_de_productos AS (
SELECT

inventory.quan_in_stock,

[Link],

[Link],

inventory.prod_id
FROM inventory, products
WHERE products.prod_id=inventory.prod_id);
-- Definicin de la funcin disparadora
CREATE OR REPLACE FUNCTION actualizar_vista() RETURNS trigger AS
$$
BEGIN

71

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

RAISE NOTICE 'Disparador sobre la vista inventario_de_productos';

IF ([Link] <> [Link] OR NEW.quan_in_stock <> OLD.quan_in_stock) THEN
RAISE NOTICE 'Actualizando las ventas y el stock en inventory';
UPDATE

inventory

SET

sales

[Link],

quan_in_stock

NEW.quan_in_stock WHERE prod_id = NEW.prod_id;

END IF;
IF [Link] <> [Link] THEN
RAISE NOTICE 'Actualizando el nombre del producto: % por %',
[Link],

[Link];

UPDATE products SET title = [Link] WHERE prod_id = NEW.prod_id;

END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Definicin del disparador
CREATE TRIGGER trigger_actualizar_vista
INSTEAD OF UPDATE
ON inventario_de_productos
FOR EACH ROW EXECUTE PROCEDURE actualizar_vista();
-- Realizando UPDATE sobre la vista
dell=# UPDATE inventario_de_productos SET title = upper('Braveheart') WHERE
prod_id = 12;
NOTICE: Disparador sobre la vista inventario_de_productos
NOTICE: Actualizando el nombre del producto: ACADEMY ALASKA por BRAVEHEART
UPDATE 1

Disparadores sobre eventos

72

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

Los disparadores sobre los eventos DDL son agregados a PostgreSQL desde la versin 9.3, los
mismos permiten ejecutar alguna rutina de funcin cuando se ejecuta algn comando DDL. La
sintaxis para ello es la siguiente:
CREATE EVENT TRIGGER nombre
ON evento()
[WHEN variable_filtro IN (valor_filtro [, ... ]) [ AND ... ] ]
EXECUTE PROCEDURE nombre_funcin();

Donde:
-

Evento: ddl_command_start, ddl_command_end y sql_drop (detalles en Overview of Event

Trigger Behavior de la Documentacin Oficial).

WHEN: filtro para que se ejecute o no la funcin disparadora, variable_filtro debe ser TAG
y valor_filtro debe ser cualquiera de los comando DDL listados en la seccin Event Trigger
Firing Matrix de la Documentacin, por ejemplo CREATE TABLE, DROP TABLE, etc.

Existen para el trabajo con estos disparadores las variables especiales siguientes:
-

TG_EVENT: variable de tipo texto con el evento.

TG_TAG: variable de tipo texto con el comando que se ejecut (CREATE TABLE,
ALTER TABLE, etc.).

Un ejemplo de la utilizacin de disparadores sobre eventos puede ser registrar cundo se realizaron
en la base de datos algunas actividades de creacin o eliminacin de una tabla, como muestra el
ejemplo 53.
Ejemplo 53: Uso de disparadores sobre eventos para registrar actividades de creacin y eliminacin sobre tablas de la
base de datos

-- Definicin de la tabla registro_evento

CREATE TABLE registro_evento (
evento text,
fecha timestamp);
-- Definicin de la funcin disparadora
CREATE OR REPLACE FUNCTION registrar_evento() RETURNS event_trigger AS
$$

73

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

BEGIN
RAISE NOTICE 'Evento: % , Horario: %', TG_TAG, now();
INSERT INTO registro_evento VALUES (TG_TAG, now());
END;
$$ LANGUAGE plpgsql;
-- Definicin del disparador
CREATE EVENT TRIGGER trigger_evento
ON ddl_command_start
WHEN TAG IN ('CREATE TABLE', 'DROP TABLE')
EXECUTE PROCEDURE registrar_evento();
-- Creando una tabla
dell=# CREATE TABLE usuarios (nombre text, usuario text, pass text);
NOTICE: Evento: CREATE TABLE , Horario: 2014-01-08 18:02:49.941-0

3.9 Resumen
Las funciones en PL/pgSQL son la forma ms utilizada de escribir lgica de negocio del lado del
servidor pues este lenguaje da la posibilidad de emplear varios componentes de la programacin en
ellas, como las variables, estructuras de control y condicionales, paso de mensajes, disparadores,
etc. Adems, permite la ejecucin de sentencias SQL dentro de ellas, pudindose manipular los
datos de la base de datos.
Para crear una funcin PL/pgSQL se emplea el comando CREATE FUNCTION, en el que se define
el nombre de la funcin, los parmetros que recibir y el tipo de retorno de la funcin; su cuerpo
est estructurado por bloques de declaracin (DECLARE, opcional), y de sentencias (acotado por
las palabras reservadas BEGIN y END).
Las funciones en PL/pgSQL pueden retornar uno de los tipos bsicos definidos en el estndar SQL
o por el usuario, el valor nulo o un conjunto de valores (utilizando SETOF o RETURNS TABLE).
3.10 Para hacer con PL/pgSQL
1. Implemente una funcin que permita la insercin datos en la tabla categories, la misma
debe mostrar un mensaje de error si la categora que se desea insertar ya existe.

74

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en PL/pgSQL

2. Desarrollo una funcin que dado el identificador de un cliente devuelva nombre, apellidos,
direccin, ciudad y pas; de no existir el cliente debe mostrar una notificacin
especificndolo.
3. Elabore una funcin que dada una fecha devuelva todos los datos de rdenes que se
realizaron antes de ella, de no haber, muestre un mensaje especificndolo.
4. Obtenga una funcin que devuelva el nombre de los productos que en el inventario ya no
queden en el almacn (inventory.quan_in_stock - [Link]=0), si no hay productos en
esta situacin emita un mensaje indicndolo.
5. Cree una funcin que dada una categora de un producto calcule el promedio del precio si la
categora es Games o Music, de lo contrario calcule el valor mnimo del precio.
6. Implemente una funcin que permita actualizar dinmicamente una tabla pasada por
parmetro, el atributo que se desea modificar, su nuevo valor y la condicin que deben
cumplir las tuplas a actualizar.
7. Elabore una funcin que permita mostrar todos los datos de una tabla pasada por parmetro
dinmicamente.
8. Dado el identificador de una categora, se sume un monto pasado por parmetro a los
precios existentes de los productos pertenecientes a dicha categora. Debe, adems, retornar
todos los datos actualizados de los productos de dicha categora.
9. Realice un mecanismo que registre en los logs de PostgreSQL los nuevos productos que se
inserten de ser el precio superior a las 200 unidades.
10. Implemente un mecanismo que registre en una tabla llamada eliminados [create table
eliminados (productos text)] los productos eliminados.
11. Dada una vista que muestre la informacin de la consulta siguiente:
SELECT [Link], [Link]
FROM [Link], [Link]
WHERE [Link] = [Link];

a. Realice un mecanismo que si se modifica un ttulo de un producto de esa vista el

mismo se propague a la tabla correspondiente.

75

4.

PROGRAMACIN DE FUNCIONES EN LENGUAJES PROCEDURALES

DE DESCONFIANZA DE POSTGRESQL

4.1 Introduccin a los lenguajes de desconfianza

En captulos previos se ha mostrado cmo programar funciones en SQL y PL/pgSQL, potentes
lenguajes que permiten realizar todas la operaciones con los datos almacenados en la bases de datos,
y que se recomienda utilizarlos siempre que lo que se necesite hacer pueda solventarse con ellos,
sobre todo por ser lenguajes de confianza. En ocasiones estos lenguajes no son suficientes para
ejecutar alguna actividad, como lo puede ser consumir algn recurso del sistema operativo o
hardware, escribir directamente en los discos del servidor, entre otros.
PostgreSQL para estas actividades permite que se puedan desarrollar rutinas de cdigo en otros
lenguajes, como por ejemplo en C (para consultar sobre el tema puede remitirse en la
Documentacin Oficial a la seccin C-Language Function). En este captulo se detallarn sobre
lenguajes procedurales de desconfianza, tiles para hacer dichas actividades, que aunque su apellido
sea desconfianza, no quiere decir que no se deban utilizar, sino que se debe tener cuidado y tomar
medidas para su uso. La primera medida la toma el gestor de bases de datos y es que solo pueden
ser creados por administradores, al igual que las funciones en dichos lenguajes.
Existen varios lenguajes procedurales como son PL/Perl, PL/TCL, PL/PHP, PL/Java, PL/Python,
PL/R, entre otros. Los cuales permiten escribir cdigo de funciones para PostgreSQL en sus
respectivos lenguajes, algo as como escribir un cdigo en Python dentro de una funcin.
Comnmente se escribe una u como sufijo del lenguaje para su identificacin del ingls untrusted
(desconfianza), es el caso de PL/Perlu o PL/Pythonu En este libro se particularizar en los lenguajes
procedurales

de

desconfianza

PL/Python

PL/R.

4.2 Lenguaje procedural PL/Python

PL/Python fue introducido en la versin 7.2 de PostgreSQL por Andrew Bosma en el ao 2002 y ha
sido mejorado paulatinamente en cada versin del gestor. El mismo posibilita escribir funciones en
lenguaje Python para el gestor de bases de datos PostgreSQL.
Python es un lenguaje simple y fcil de aprender, posee mltiples bibliotecas para hacer variadas
actividades, ya sea en sistemas de gestin comercial, interacciones con el sistema operativo, etc.

76

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

Para la instalacin de PL/Python a partir de 9.1 en adelante que se cre en PostgreSQL el

mecanismo de extensiones, este lenguaje se puede instalar como una extensin con el comando
create extension plpythonu. Tambin se puede instalar desde la lnea de comandos con createlang
plpythonu nombre_basededatos, para versiones previas.
Para el empleo correcto de PL/Python en Debian/Ubuntu el sistema debe tener instalado el paquete
postgresql-plpython-9.1 o superior

4.2.1 Escribir funciones en PL/Python

Una funcin en PL/Python se crea de igual forma que el resto de las funciones en PostgreSQL,
haciendo uso del comando CREATE FUNCTION. El cuerpo de la funcin es cdigo en Python. El
retorno de la funcin se realiza con la clusula clsica de Python para retornar valores de una
funcin return, tambin puede ser empleado yield y, sino se provee una salida se devuelve none que
se traduce a PostgreSQL como null.
El ejemplo siguiente muestra es una funcin para devolver la cadena Hola Mundo.
Ejemplo 54: Hola Mundo con PL/Python

CREATE FUNCTION holamundo() RETURNS text AS

$$
return "Hola Mundo"
$$
LANGUAGE plpythonu;
-- Invocacin de la funcin
dell=# SELECT holamundo();
holamundo
Hola Mundo
(1 fila)

Note en el ejemplo anterior que al igual que las funciones en SQL y PL/pgSQL, a la funcin se le
debe especificar el lenguaje en que est siendo creada, en el caso de Python plpythonu.

77

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

4.2.2 Parmetros de una funcin en PL/Python

Los parmetros de una funcin PL/Python se pasan normalmente como en cualquier otro lenguaje
procedural y, una vez dentro de la funcin deben ser llamados por su nombre. El ejemplo 55
muestra esto.
Ejemplo 55: Paso de parmetros en funciones PL/Python

CREATE FUNCTION hola(nombre text) RETURNS text AS

$$
return 'Hola %s' % nombre
$$
LANGUAGE plpythonu;
-- Invocacin de la funcin
dell=# SELECT hola('Anthony');
hola
Hola Anthony
(1 fila)

Al igual que en PL/pgSQL pueden definirse parmetros de salida. El ejemplo 56 muestra su

empleo.
Ejemplo 56: Empleo de parmetros de salida

CREATE OR REPLACE FUNCTION saludo(INOUT nombre text, OUT mayuscula text) AS

$$
mayscula = 'HOLA %s' % [Link]()
return ('Hola '+ nombre, mayuscula)
$$
LANGUAGE plpythonu;
-- Invocacin de la funcin
dell=# SELECT saludo('Sandra');
saludo

78

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

Hola Sandra, HOLA SANDRA

(1 fila)

Pasando tipos compuestos como parmetros

Los tipos de datos compuestos de PostgreSQL tambin pueden pasarse como parmetro a
PL/Python y son automticamente convertidos en diccionarios dentro de Python. A continuacin un
ejemplo de cmo se utilizan.
Ejemplo 57: Paso de parmetros como tipo de dato compuesto

-- Creacin de un tipo de dato compuesto

CREATE TYPE mini_prod AS (nombre varchar, precio numeric);
-- Funcin que chequea que determinado product comience o no con A
CREATE OR REPLACE FUNCTION parametros_mi_tipo(tabla mini_prod) RETURNS
character varying AS
$$
if 'A' in tabla['nombre'][0]:
return 'Comienza con A'
else:
return 'No Comienza con A'
$$
LANGUAGE plpythonu;
-- Invocacin de la funcin
dell=# SELECT parametros_mi_tipo(ROW('Braveheart', 1.10));
parametros_mi_tipo
No comienza con A
(1 fila)

Pasando arreglos como parmetros

Tambin se pueden pasar como parmetros arreglos de PostgreSQL los cuales son convertidos a
listas o tuplas. El ejemplo 58 muestra cmo emplearlos.

79

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

Ejemplo 58: Arreglos pasados por parmetros

-- Funcin que devuelve el primer elemento de un arreglo pasado por parmetro

CREATE OR REPLACE FUNCTION arreglos(a character varying[]) RETURNS character
varying AS
$$
return a[0]
$$
LANGUAGE plpythonu;
-- Invocacin de la funcin
dell=# SELECT arreglos(array['Anthony','Sotolongo']);
arreglos
Anthony
(1 fila)

4.2.3 Homologacin de tipos de datos PL/Python

Segn la Documentacin Oficial de PostgreSQL la homologacin de tipos de datos de PostgreSQL
a PL/Python se realiza como muestra la tabla siguiente.
Tabla 3: Homologacin de tipos de datos PL/Python

PostgreSQL

Python 2

Python 3

text, char, varchar

str

str

boolean

bool

bool

real, numeric, double

float

float

smallint, int

int

int

bigint, oid

long

int

null

none

none

4.2.4 Retorno de valores de una funcin en PL/Python

En epgrafes anteriores se ha mostrado cmo pasar parmetros y cmo se utiliza la sentencia
RETURN en Python para retornar valores, en esta seccin se analizarn algunas particularidades del
retorno de valores en PL/Python.
80

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

Devolviendo arreglos
Devolver una lista o tupla en Python es similar a un arreglo en PostgreSQL; el ejemplo 59 lo
muestra.
Ejemplo 59: Retornando una tupla en Python

-- Funcin que retorna un arreglo

CREATE FUNCTION retorna_arreglo_texto() RETURNS text[] AS
$$
return ('Hola','Mundo')
$$ LANGUAGE plpythonu;
-- Invocacin de la funcin
dell=# SELECT retorna_arreglo_texto();
retorna_arreglo_texto
{Hola,Mundo}
(1 fila)

Devolviendo tipos compuestos

Devolver tipos compuestos en Python puede realizarse de tres modos, como:
-

Tupla o lista: que debe tener la misma cantidad y tipos de datos que el compuesto definido
por el usurario; el ejemplo 60 muestra el caso.

Diccionario: que debe tener la misma la cantidad y tipos de datos que el tipo compuesto
definido, adems, los nombres de las columnas deben coincidir; el ejemplo 61 muestra el
caso.

Objeto: donde la definicin de la clase debe tener los atributos similares al del tipo de datos
compuesto por el usuario.

Ejemplo 60: Devolviendo valores de tipo compuesto como una tupla

-- Funcin que retorna un dato compuesto como tupla

CREATE FUNCTION retorna_tipo_lista() RETURNS mini_prod AS
$$
nombre = 'Una novia para David'

81

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

precio = 2.80
return (nombre, precio) # como tupla
$$ LANGUAGE plpythonu;
-- Invocacin de la funcin
dell=# SELECT retorna_tipo_lista();
retorna_tipo_lista
(Una novia para David, 2.8)
(1 fila)

Note que en este y los siguientes ejemplos se emplea el tipo de dato compuesto mini_prod creado
en el ejemplo 57.
Tambin puede retornarse como con la forma RETURN [nombre, precio] para hacerlo una lista
Ejemplo 61: Devolviendo valores de tipo compuesto como un diccionario

-- Funcin que retorna un dato compuesto como diccionario

CREATE FUNCTION retorna_tipo_dic() RETURNS mini_prod AS
$$
nombre = 'Una novia para David'
precio = 2.80
return { "nombre": nombre, "precio": precio }
$$ LANGUAGE plpythonu;
-- Invocacin de la funcin
dell=# SELECT retorna_tipo_dic();
retorna_tipo_dic
(Una novia para David, 2.8)
(1 fila)

Retornando conjuntos de resultados

Para devolver conjuntos de datos puede utilizarse:
-

Una lista o tupla, ver ejemplo 62.

82

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

Un generador (yield), ver ejemplo 63.

Un iterador.

Ejemplo 62: Devolviendo conjunto de valores como una lista o tupla

-- Funcin que retorna un conjunto de datos como lista o tupla

CREATE FUNCTION mini_producto_conjunto() RETURNS SETOF mini_prod AS
$$
nombre = 'Hello Hemingway'
precio = 4.31
return ( [ nombre, precio ], [ nombre + nombre, precio + precio ] )
$$ LANGUAGE plpythonu;
-- Invocacin de la funcin
dell=# SELECT * FROM mini_producto_conjunto();
nombre | precio
Hello Hemingway | 4.31
Hello HemingwayHello Hemingway | 8.62
(2 filas)
Ejemplo 63: Devolviendo conjunto de valores como un generador

-- Funcin que retorna un conjunto de datos como un generador

CREATE FUNCTION mini_producto_conjunto_generador() RETURNS SETOF record AS
$$
lista=[('Clandestinos', 5.00), ('Memorias del subdesarrollo', 5.10), ('Suite Habana',
3.90)]
for producto in lista:
yield ( producto[0], producto[1] )
$$ LANGUAGE plpythonu;
-- Invocacin de la funcin
dell=# SELECT * FROM mini_producto_conjunto_generador() AS (a text, b numeric);

83

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

a|b
Clandestinos | 5.00
Memorias del subdesarrollo | 5.10
Suite Habana | 3.90
(3 filas)

4.2.5 Ejecutando consultas en la funcin PL/Python

PL/Python permite realizar consultas sobre la base de datos a travs de un mdulo llamado plpy,
importado por defecto. Tiene varias funciones importantes como son:
-

[Link](consulta [, max-rows]): permite ejecutar una consulta con una cantidad finita
de tuplas en el resultado, especificado en el segundo parmetro (opcional). Devuelve un
objeto similar a una lista de diccionarios, al que se puede acceder con la forma
resultado[0]['micolumna'], para acceder al primer registro y a la columna 'micolumna'. Ver
ejemplo 64 para su uso.

[Link](consulta [, argtypes]): permite preparar planes de ejecucin para determinadas

consultas para luego ser ejecutadas por la funcin execute(plan [, arguments [, max-rows]]).
Ver ejemplo 65 para su uso.

[Link](consulta): aadido a partir de la versin 9.2 del gestor.

Ejemplo 64: Devolviendo valores de la ejecucin de una consulta desde PL/Python con [Link]

-- Funcin que retorna los 10 primeros productos haciendo uso de [Link]

CREATE FUNCTION obtener_productos() RETURNS TABLE (id int, titulo text, precio
numeric) AS
$$
resultado = [Link]("SELECT * FROM products LIMIT 10")
for tupla in resultado:
yield (tupla['prod_id'], tupla['title'], tupla['price'])
$$
LANGUAGE plpythonu;
-- Invocacin de la funcin
dell=# SELECT * FROM obtener_productos();
84

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

id | titulo | precio
1 | ACADEMY ACADEMY| 25.99
2 | ACADEMY ACE | 20.99
3 | ACADEMY ADAPTATION | 28.99
4 | ACADEMY AFFAIR | 14.99
5 | ACADEMY AFRICAN | 11.99
6 | ACADEMY AGENT | 15.99
7 | ACADEMY AIRPLANE | 25.99
8 | ACADEMY AIRPORT | 16.99
9 | ACADEMY ALABAMA | 10.99
10 | ACADEMY ALADDIN | 9.99
(10 filas)
Ejemplo 65: Ejecucin con [Link] de una consulta preparada con [Link]

CREATE FUNCTION preparada() RETURNS text AS

$$
miplan = [Link]("SELECT * FROM products WHERE prod_id = $1", ["int"])
resultado = [Link](miplan, [4])
return resultado[0]['title']
$$
LANGUAGE plpythonu;
-- Invocacin de la funcin
dell=# SELECT * FROM preparada();
preparada
ACADEMY AFFAIR
(1 fila)
PL/Python cuenta con otras funciones que pueden ser tiles, como [Link](msg), [Link](msg),
[Link](msg), [Link](msg), [Link](msg), [Link](msg). Para ms informacin sobre

85

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

estas funciones puede dirigirse a la seccin Utility Functions del captulo PL/Python - Python
Procedural Language de la Documentacin Oficial

4.2.6 Mezclando
Como se ha mostrado anteriormente los lenguajes de desconfianza son utilizados para hacer rutinas
externas al servidor de bases de datos, a continuacin se muestra un ejemplo de cmo guardar en un
archivo XML el resultado de una consulta.
Ejemplo 66: Guardar en un XML el resultado de una consulta

CREATE OR REPLACE FUNCTION salva_tabla_xml() RETURNS character varying AS

$$
from [Link] import ElementTree
rv = [Link]("SELECT * FROM categories")
raiz = [Link]('consulta')
for valor in rv:
datos = [Link](raiz,'datos')
for key, value in [Link]():
element = [Link](datos, key)
[Link] = str(value)
contenido = [Link](raiz)
fichero = open('/tmp/[Link]','w')
[Link](contenido)
[Link]()
return contenido
$$
LANGUAGE plpythonu;

Note que para que esta funcin se ejecute satisfactoriamente la ruta especificada en fichero debe
existir.

86

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

4.2.7 Realizando disparadores con PL/Python

PL/Python permite escribir funciones disparadoras, para eso cuenta con un variable diccionario TD,
que posee varios pares llave/valor tiles para su trabajo; algunos se describen a continuacin:
-

TD["event"]: almacena como un texto el evento que se est ejecutando (INSERT,

UPDATE, DELETE o TRUNCATE).

TD["when"]: almacena como un texto el momento de ejecucin del trigger (BEFORE,

AFTER o INSTEAD OF).

TD["new"], ["old"]: almacenan el registro nuevo y viejo respectivamente, en dependencia

del evento que se ejecute.

TD["table_name"]: almacena el nombre de la tabla que dispara el trigger.

Se puede retornar NONE u OK para dar a conocer que la operacin con la fila se ejecut
correctamente o SKIP para abortar el evento.
El ejemplo 67 muestra un disparador que verifica al insertar si el ttulo es "HOLA MUNDO" y si es
as no lo permite insertar.
Ejemplo 67: Disparador en PL/Python

-- Funcin disparadora
CREATE FUNCTION trigger_plpython() RETURNS trigger AS
$$
[Link]('Comenzando el disparador')
if TD["event"] == "INSERT" and TD["new"]['title'] == "HOLA MUNDO":
[Link]('Se

abort

la

insercin

por

tener

el

ttulo:

'

TD["new"]['title'])
return "SKIP"
else:
[Link]('Se registr correctamente')
return "OK"
$$
LANGUAGE plpythonu;
-- Disparador

87

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

CREATE TRIGGER trigger_plpython

BEFORE INSERT OR UPDATE
ON products
FOR EACH ROW
EXECUTE PROCEDURE trigger_plpython();
-- Invocacin de la funcin
dell=#

INSERT

INTO

products(prod_id,category,title,actor,price,special,common_prod_id)
dell=# VALUES (20001, 13, 'HOLA MUNDO', 'Anthony Sotolongo', 12.0, 0, 1000);
NOTICE: Comenzando el disparador
CONTEXT: PL/Python function "trigger_plpython"
NOTICE: Se abort la insercin por tener el ttulo: HOLA MUNDO
CONTEXT: PL/Python function "trigger_plpython"

4.3 Lenguaje procedural PL/R

PL/R es un lenguaje procedural para PostgreSQL que permite escribir funciones en el lenguaje R
para su empleo dentro del gestor. Es desarrollado por Joseph E. Conway desde el 2003 y compatible
con PostgreSQL desde su versin 7.4. Soporta casi todas las funcionalidades de R desde el gestor.
Este lenguaje es orientado especficamente a realizar operaciones estadsticas y el cual es muy
potente en esta rama, cuenta con disimiles paquetes (conjunto de funcionalidades) para su trabajo y
se utiliza en varias reas de la informtica, medicina, bioinformtica, entre otras.
Para la instalacin de PL/R a partir de la versin 9.1 en adelante, en la que fue aadido en
PostgreSQL el mecanismo de extensiones, se puede emplear el comando create extension plr.
Tambin se puede instalar desde la lnea de comandos con createlang plr nombre_basedatos, para
versiones previas puede consultar el sitio oficial del lenguaje ([Link]
Se debe tener instalado en los sistemas Debian/Ubuntu el paquete postgresql-9.1-plr o superior

4.3.1 Escribir funciones en PL/R

Una funcin en PL/R se define igual que las dems funciones en PostgreSQL, con la sentencia
CREATE FUNCTION. El cuerpo de la funcin es cdigo R y tiene la particularidad de que en este

88

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

lenguaje las funciones deben nombrarse de forma diferente aunque sus atributos no sean los
mismos.
El retorno de la funcin se realiza con la clusula clsica de R para retornar valores de una funcin
return, pero en ocasiones no es necesario escribirla.
El ejemplo 68 muestra cmo sumar dos valores con PL/R.
Ejemplo 68: Funcin en PL/R que suma 2 nmeros pasados por parmetros

CREATE FUNCTION suma(a integer, b integer) RETURNS integer AS

$$
return (a + b)
$$
LANGUAGE plr;

4.3.2 Pasando parmetros a una funcin PL/R

Los parmetros de una funcin PL/R se pasan normalmente como en cualquier lenguaje procedural
y:
-

Pueden nombrarse, debiendo ser llamados por dicho nombre dentro de la funcin.

De no ser nombrados pueden ser accedidos con argN, siendo n el orden que ocupan en la
lista de parmetros.

El ejemplo 69 muestra la implementacin del ejemplo 68 sin nombrar los parmetros.

Ejemplo 69: Funcin en PL/R que suma 2 nmeros pasados por parmetros, sin nombrarlos

CREATE OR REPLACE FUNCTION suma(integer, integer) RETURNS integer AS

$$
return (arg1 + arg2)
$$
LANGUAGE plr;

Utilizando arreglos como parmetros

En una funcin PL/R cuando se recibe un arreglo como parmetro automticamente se convierte a
un vector c(...).

89

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

La funcin implementada en el ejemplo 70 calcula la desviacin estndar de un arreglo pasado por

parmetro.
Ejemplo 70: Clculo de la desviacin estndar desde PL/R

CREATE OR REPLACE FUNCTION desv_estandar(arreglo int[]) RETURNS real AS

$$
desv <- sd(arreglo)
return (desv)
$$
LANGUAGE plr;
-- Invocacin de la funcin
dell=# SELECT desv_estandar(array[4,2,3,4,5,6,3]);
desv_estandar
1.34519
(1 fila)

Utilizando tipos de datos compuestos

PL/R permite que se puedan pasar tipos de datos compuestos definidos por el usuario, los cuales
son convertidos en R a [Link] de una fila. El ejemplo 71 muestra cmo hacerlo.
Ejemplo 71: Pasando un tipo de dato compuesto como parmetro

-- Crear tipo de dato compuesto

CREATE TYPE mini_prod AS (nombre varchar, precio numeric);
-- Funcin que recibe un tipo de dato compuesto como parmetro
CREATE OR REPLACE FUNCTION compuesto(a mini_prod) RETURNS text AS
$$
if (a$precio == 0)
{return (print("Precio incorrecto"))}
return (print("Precio correcto"))
$$

90

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

LANGUAGE plr;
-- Invocacin de la funcin
dell=# SELECT compuesto((ROW('Suite Habana', 0)));
compuesto
Precio incorrecto
(1 fila)

4.3.3 Homologacin de tipos de datos PL/R

Segn la Documentacin Oficial de PL/R, la homologacin de tipos de datos de PostgreSQL a PL/R
se realiza como muestra la tabla 4.
Tabla 4: Homologacin de tipos de datos PL/R

PostgreSQL

boolean

logical

int8, float4, float8, cash, numeric

numeric

int, int4

integer

bytea

objetc

otro

caracter

4.3.4 Retornando valores de una funcin en PL/R

En PL/R los resultados de una funcin se retornan haciendo uso de la palabra reservada RETURN
y, entre parntesis se especifica el valor a retornar, que debe coincidir con el tipo de dato definido
en la declaracin de la funcin.
Devolviendo arreglos
Para retornar arreglos se deben devolver desde PL/R vectores o arreglos de una dimensin, por
ejemplo la funcin a continuacin devuelve el resumen estadstico de un vector como un arreglo de
PostgreSQL.
Ejemplo 72: Retorno de valores con arreglos desde PL/R

-- Funcin que realiza un resumen estadstico de un arreglo pasado por parmetro

91

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

CREATE OR REPLACE FUNCTION resumen_estadistico(a integer[]) RETURNS real[] AS

$$
resumen <- summary(a)
return (resumen)
$$
LANGUAGE plr;
-- Invocacin de la funcin
dell=# SELECT resumen_estadistico(array[2,5,3,2,2,7,8,0]);
resumen_estadistico
{0,2,2.5,3.625,5.5,8}
(1 fila)

Devolviendo tipos compuestos

Para devolver tipos compuestos se debe utilizar desde PL/R un [Link] con los respectivos
atributos del tipo de dato compuesto definido por el usuario, con los atributos en orden respecto a
los tipos de datos.
Ejemplo 73: Retorno de valores con tipos de datos compuestos desde PL/R

CREATE OR REPLACE FUNCTION devolvercompuesto() RETURNS SETOF mini_prod AS

$$
return ([Link](nombre="Los pjaros tirndole a la escopeta", precio=2.50))
$$
LANGUAGE plr;
-- Invocacin de la funcin
dell=# SELECT devolvercompuesto();
nombre | precio
Los pjaros tirndole a la escopeta | 2.5
(1 fila)

92

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

Devolviendo conjuntos
Se pueden devolver conjuntos de datos de algn tipo compuesto, TABLE o RECORD. A
continuacin se muestran dos ejemplos haciendo uso de ambos tipos.
Ejemplo 74: Retorno de conjuntos

-- Devolviendo conjuntos haciendo uso de TABLE

CREATE OR REPLACE FUNCTION devolver_varios_table() RETURNS TABLE(a text, b
numeric) AS
$$
nombres <- c("Fresa y Chocolate", "La pelcula de Ana", "Conducta")
precios <- c(25.01, 10.65, 60)
dataframe <- [Link](nombre = nombres, precio = precios)
return ([Link](dataframe))
$$
LANGUAGE plr;
-- Invocacin de la funcin
dell=# SELECT * FROM devolver_varios_table();
a|b
Fresa y Chocolate | 25.01
La pelcula de Ana | 10.65
Conducta | 60.00
(3 filas)
-- Devolviendo conjuntos haciendo uso de RECORD
CREATE OR REPLACE FUNCTION devolver_varios_record() RETURNS SETOF RECORD AS
$$
nombres <- c("Fresa y Chocolate", "La pelcula de Ana", "Conducta")
precios <- c(25.01, 10.65, 60)
dataframe <- [Link](nombre = nombres, precio = precios)
return (dataframe)

93

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

$$
LANGUAGE plr;
-- Invocacin de la funcin
dell=# SELECT * FROM devolver_varios_record() AS (a text, b numeric);
a|b
Fresa y Chocolate | 25.01
La pelcula de Ana | 10.65
Conducta | 60.00
(3 filas)

4.3.5

Ejecutando consultas en la funcin PL/R

Para ejecutar consultas desde PL/R se pueden utilizar varias funciones como:
-

[Link](consulta): donde consulta es una cadena de caracteres y la funcin devuelve los

datos de un SELECT en un [Link] de R; si es una consulta de modificacin entonces
devuelve el nmero de filas afectadas. Ver ejemplo 75.

[Link]: permite preparar consultas y salvar el plan generado para una ejecucin
posterior de las mismas; el plan solo se salvar durante la conexin o transaccin en curso.

[Link]: ejecuta una consulta previamente preparada con [Link] y permite los
argumentos para la consulta preparada.

[Link].cursor_open y [Link].cursor_fetch: empleados para el trabajo con cursores.

Ejemplo 75: Retorno del resultado de una consulta desde PL/R usando [Link]

CREATE OR REPLACE FUNCTION devolver_consulta() RETURNS SETOF mini_prod AS

$$
resultado <- [Link]("SELECT title, price FROM products WHERE prod_id <
100")
return (resultado)
$$
LANGUAGE plr;
-- Invocacin de la funcin

94

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

dell=# SELECT * FROM devolver_consulta();

nombre | precio
ACADEMY ACADEMY | 25.99
ACADEMY ACE | 20.99
ACADEMY ADAPTATION | 28.99
ACADEMY AFFAIR | 14.99
-- More --

4.3.6 Mezclando
Como se ha mostrado estos lenguajes de desconfianza son utilizados para hacer rutinas externas al
servidor de bases de daos, a continuacin se muestra un ejemplo de cmo generar un grfico de
barras en un archivo .png con el resultado de una consulta.
Ejemplo 76: Funcin que genera una grfica de barras con el resultado de una consulta en PL/R

CREATE FUNCTION barras_simple(nombre text, consulta text, texto text, ejex text[])
RETURNS integer AS
$$
png(paste(nombre,"png", sep="."))
resultado <- [Link](consulta)
barplot([Link](resultado),beside=TRUE,main=texto,col=rainbow(length(as.
matrix(resultado))), [Link]=c(ejex))
[Link]()
$$
LANGUAGE plr;
-- Invocacin de la funcin
dell=# SELECT * FROM barras_simple('grafica_barras',
dell=# 'SELECT count(products.prod_id) AS cantidad FROM products JOIN categories ON
[Link]=[Link]

GROUP

BY

[Link],

[Link] ORDER BY [Link] LIMIT 4',

dell=# 'Cantidad producto x Categora',

95

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

dell=# array(SELECT categoryname FROM categories ORDER BY categoryname LIMIT 4

)::text[]);

Figura 3: Grfica de barras generada con el resultado de una consulta en PL/R

4.3.7 Realizando disparadores con PL/R

PL/R permite escribir funciones disparadoras, para eso cuenta con un variable diccionario TD, que
posee varios pares llave/valor tiles para su trabajo; algunos se describen a continuacin:
-

[Link]: devuelve el nombre de la tabla que invoc al disparador.

[Link]: devuelve una cadena en mayscula especificando cundo se ejecut el

disparador (BEFORE o AFTER).

[Link]: devuelve una cadena en mayscula del evento que ejecut el disparador (INSERT,
UPDATE o DELETE).

[Link]: [Link] que contiene los valores nuevos que tiene la fila nueva insertada o
modificada, puede llamarse utilizando el nombre de la columna de la tabla, por ejemplo
[Link]$columna.

[Link]: [Link] que contiene los valores viejos que tiene la fila actualizada o
eliminada, puede llamarse utilizando el nombre de la columna de la tabla, por ejemplo
[Link]$columna.

Existen otras variables como [Link], [Link], [Link], [Link], que pueden analizarse
en PL/R Users Guide - R Procedural Language.
El retorno del disparador puede ser NULL, una fila en forma de [Link]([Link], [Link]) o
alguno que contenga las mismas columnas de la tabla que lo invoc. Cuando se retorna NULL
significa que el resultado de la operacin realizada va a ser ignorado.

96

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

El ejemplo 77 muestra el empleo de disparadores desde PL/R.

Ejemplo 77: Utilizando disparadores en PL/R

-- Funcin disparadora
CREATE FUNCTION trigplrfunc() RETURNS trigger AS
$$
[Link] ("Comenzado el disparador")
if ([Link] == "INSERT" & [Link]$price == 0)
{
[Link]("Registro no insertado")
return (NULL)
}
return ([Link])
$$
LANGUAGE plr;
-- Disparador
CREATE TRIGGER testplr_trigger
BEFORE INSERT OR UPDATE
ON products
FOR EACH ROW
EXECUTE PROCEDURE trigplrfunc();
-- Invocacin de la funcin
dell=# INSERT INTO products (prod_id, category, title, actor, price, special,
common_prod_id) VALUES (20003, 13, 'Un Rey en La Habana', 'Alexis Valds', 30, 0,
1000);
NOTICE: Comenzado el disparador
NOTICE: Registro no insertado
Consulta retornada exitosamente: 0 filas afectadas, tiempo de ejecucin 33 ms

97

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

4.4 Para hacer con PL/Python y PL/R

1. Desarrolle una funcin en PL/Python que devuelva el monto total(price* quantity) del
producto donde el actor es "VIVIEN COOPER".
2. Construya una funcin que permita devolver los 100 productos ms caros.
3. Elabore una funcin en PL/Python que permita exportar los datos del ejercicio 2 a un
archivo CSV.
4. Realice una funcin en PL/Python para devolver los productos que su ttulo comience con
un caracter pasado por parmetro, prepare dicha consulta antes de ejecutarla.
5. Conciba una funcin en PL/Python que permita exportar los datos del ejercicio 4 a un
archivo Excel.
6. Implemente un mecanismo basado en disparadores en PL/Python que permita:
a. Llevar un registro de las categoras eliminadas.
b. Controlar que si se agrega o modifica el precio de una produccin y este precio es
0, le enve un correo al administrador del sistema (suponga un correo
admin@[Link]) notificando la situacin.
7. Logre una funcin en PL/R que devuelva los productos que su precio sea mayor a uno
pasado por parmetro, prepare dicha consulta antes de ejecutarla.
8. Confeccione funciones en PL/R que permitan obtener grficos de:
a. Pastel con el resultado de la consulta del ejercicio 7.
b. Histograma de todos los productos del actor "PENELOPE GUINESS".
9. Realice una funcin en PL/R que permita calcular la mediana de todos los productos
realizados por el actor "JUDY REYNOLDS".
4.5 Resumen
Los llamados lenguajes de desconfianza permiten escribir lgica de negocio en el servidor de
bases de datos PostgreSQL, escritas en sus propios lenguajes. En el captulo se analizaron las
caractersticas de los lenguajes PL/Python y PL/R, los cuales pueden ser tiles para determinadas
operaciones con los datos. Para implementar una funcin en dichos lenguajes se emplea el comando
CREATE FUNCTION, en el que se define el nombre de la funcin, los parmetros que recibir y el
tipo de retorno de la funcin y; su cuerpo estar compuesto por las caractersticas del lenguaje

98

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Programacin de funciones en lenguajes procedurales de desconfianza de

PostgreSQL

especificado. Se realiz una homologacin con los tipos de datos de PostgreSQL, as como la
ejemplificacin de la implementacin de disparadores.
Se debe tener cuidado en el uso de estos lenguajes pues desde ellos se pueden acceder a recursos del
servidor ms all de los datos almacenados. Se recomiendan utilizar en entornos controlados y para
actividades que no se puedan realizar desde los lenguajes nativos como lo son el SQL y el
PL/pgSQL. El rendimiento de los mismos no suele ser el mejor.

99

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Gua para el desarrollo de lgica de negocio del lado del servidor

RESUMEN
En PL/pgSQL y otros lenguajes procedurales en PostgreSQL se destacan las ventajas de la
programacin del lado del servidor de bases de datos, enfatizando en cmo brinda esta caracterstica
el sistema de gestin de bases de datos PostgreSQL, que la implementa a travs del desarrollo de
funciones.
El libro se enfoca en dos de las formas principales de hacerlo, mediante Funciones en SQL y
Funciones en Lenguajes Procedurales. En los captulos propuestos se analizaron en detalle la forma
de implementar este tipo de funciones y su uso en algunos escenarios, abarcando con varios
ejemplos las sintaxis, estructuras y trabajo con ellas en los lenguajes SQL, PL/pgSQL, PL/Python y
PL/R.
De desarrollarse los ejercicios propuestos en los captulos 2, 3 y 4, se debe haber alcanzado una
habilidad bsica para la programacin del lado del servidor PostgreSQL con caractersticas
disponibles hasta su versin 9.3.

100

PL/pgSQL y otros lenguajes

procedurales en PostgreSQL

Gua para el desarrollo de lgica de negocio del lado del servidor

BIBLIOGRAFA
Conway, Joseph E. 2009. PL/R Users Guide - R Procedural Language. Boston : s.n., 2009.
Date, C. J. y Darwen, Hugh. 1996. A Guide to the SQL Standard . California : Addison-Wesley
Professional, 1996. ISBN: 978-0201964264.
Krosing, Hannu, Mlodgenski, Jim y Roybal, Kirk. 2013. PostgreSQL Server Programming.
Birmingham : Packt Publishing, 2013. ISBN 978-1-84951-698-3.
Matthew, Neil y Stones, Richard. 2005. Beginning databases with PostgreSQL, from novice to
professional. 2nd edition. 2005.
Melton, Jim y Simon, Alan R. 1993. Understanding the New SQL. s.l. : Morgan Kaufmann
Publishers, 1993. ISBN: 9781558602458.
Momjian, Bruce. 2001. PostgreSQL Introduction and Concepts. 2001.
Smith, Gregory. 2010. PostgreSQL 9.0 High Performance. Birmingham-Mumbai : Packt
Publishing, 2010. ISBN 978-1-849510-30-1.
The PostgreSQL Global Development Group. 2013. PostgreSQL 9.3.0 Documentation.
California : s.n., 2013.

101