Procedimientos almacenados y PL/pgSQL

Rafael Martinez Guerrero
Words: 1950
Reading time: 10 min.
postgres • sql • postgresql-es • español

En este artículo vamos a dar una introducción a los llamados procedimientos almacenados (stored procedures) en PostgreSQL. Un procedimiento almacenado se puede definir como un programa, procedimiento ó función, el cual está almacenado en la base de datos y listo para ser usado.

Este artículo es una introducción a este tema, la documentación completa con todos los detalles e información necesaria está disponible en la documentación oficial de PostgreSQL, “Capítulo 37. Procedural Languages”

Existen dos ventajas evidentes al utilizar procedimientos almacenados en nuestro sistema:

  • La ejecución del procedimiento ocurre en el servidor de bases de datos. Esto probablemente aumentará el rendimiento de nuestra aplicación al no tenerse que mandar datos entre el cliente y el servidor, y no tener que procesar resultados intermedios en el cliente para obtener el resultado final.

  • Al tener la lógica de la aplicación implementada en la base de datos no tendremos que implentarla en los clientes, con el consiguiente ahorro de lineas de código redundante y complejidad.

    Si tenemos diferentes tipos de clientes implementados en diferentes sistemas ó lenguajes de programación y accediendo a la misma base de datos, no tendremos que programar la misma lógica en todos, al estar esta disponible en la base de datos. Tendremos una API a la lógica de la aplicación lista para usarse desde diferentes clientes

Un procedimiento almacenado en PostgreSQL se puede escribir en multiples lenguajes de programación. En una instalación por defecto de PostgreSQL podremos tener disponibles los siguientes lenguajes: PL/pgSQL, PL/Perl, PL/Tcl y PL/Python.

El único lenguaje que está disponible automáticamente es PL/pgSQL. Para utilizar PL/Perl, PL/Tcl o PL/Python tendremos que haber configurado/compilado PostgreSQL con estos parámetros --with-perl --with-tcl --with-python.

Tambien existen muchos otros lenguajes disponibles como módulos adicionales, entre ellos, PL/Java, PL/PHP, PL/R, PL/Ruby, PL/Sheme y PL/sh, pero estos tienen que descargarse e instalarse por separado.

Suponiendo que tenemos PostgreSQL instalado con los lenguajes que queremos utilizar tendremos que realizar dos operaciones para poder empezar a utilizar un procedimiento almacenado en nuestra base de datos:

*Instalar, si no lo tenemos instalado, el lenguaje que vayamos a utilizar para programar nuestro procedimiento (solamente necesitamos hacer esto una sola vez por base de datos) *Programar nuestro procedimiento e instalarlo en la base de datos

PL/pgSQL

En este artículo vamos a utilizar el lenguaje de procedimientos PL/pgSQL por ser el que tendremos seguro disponible. PL/pgSQL es muy parecido al lenguaje PL/SQL utilizado por Oracle y bajo mi punto de vista uno de los mejores lenguajes de procedimientos que podemos usar en PostgreSQL, es fácil de aprender, potente y siempre está disponible.

Los objetivos de PL/pgSQL cuando se creo fueron:

  • Poder ser usado para crear funciones y disparadores (triggers)
  • Añadir estructuras de control al lenguaje SQL
  • Poder realizar cálculos complejos
  • Heredar todos los tipos, funciones y operadores definidos por el usuario
  • Poder ser definido como un lenguaje “de confianza”
  • Fácil de usar

PL/pgSQL es un lenguaje estructurado en bloques. Como mínimo tendremos un bloque principal en nuestro procedimiento almacenado y dentro de este podremos tener subbloques. Un bloque se define de la siguiente manera (Todo entre los corchetes [] es opcional):

[ << etiqueta >> ]
[ DECLARE
    declaraciones de variables ]
BEGIN
    codigo
END [ etiqueta ];

Podemos definir e instalar un procedimiento en PL/pgSQL de la siguiente manera:

CREATE [ OR REPLACE ] FUNCTION 
nombre_funcion([ [ argmodo ] [ argnombre ] argtipo [, ...] ]) 
RETURNS tipo AS $$

[ DECLARE ]
 [ declaraciones de variables ]

BEGIN
  codigo  

END;
$$ LANGUAGE plpgsql
   | IMMUTABLE | STABLE | VOLATILE
   | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
   | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
   | COST execution_cost
   | ROWS result_rows
   | SET configuration_parameter { TO value | = value | FROM CURRENT }
;

No os asusteis al ver esto, es más fácil de lo que parece y todas las opciones despues de “LANGUAGE plpgsql” tienen unos valores por defecto que simplifican mucho la definición de un procedimiento. La documentación completa está disponible en la sección CREATE FUNCTION de la documentación oficial.

A continuación vamos a ver algunas de las opciones y valores más importantes.

argmodo: El modo de un argumento puede ser IN, OUT, or INOUT. Por defecto se usa IN si no se define.

argtipo: Los tipos que podemos utilizar son todos los disponibles en PostgreSQL y todos los definidos por el usuario

declaraciones de variables: Las declaraciones de variables se pueden realizar de la siguiente manera ($n = orden de declaración del argumento.):

nombre_variable ALIAS FOR $n;
nombre_variable [ CONSTANT ] tipo [ NOT NULL ] [ { DEFAULT | := } expresion ];

código: en este artículo no tenemos espacio para ver como podemos escribir la parte de código de un procedimiento. Teneis que leer la sección de la documentación oficial de PostgreSQL que trata sobre el tema, “Capítulo 38. PL/pgSQL - SQL Procedural Language”, especialmente las secciones 38.3. Declarations, 38.5. Basic Statements y 38.6. Control Structures.

IMMUTABLE | STABLE | VOLATILE:

IMMUTABLE: Indica que la función no puede alterar a la base de datos y que siempre devolverá el mismo resultado, dados los mismos valores como argumentos. Este tipo de funciones no pueden realizar consultas en la base de datos.

STABLE: Indica que la función no puede alterar a la base de datos y que siempre devolverá el mismo resultado en una consulta individual de una tabla, dados los mismos valores como argumentos. El resultado podria cambiar entre sentencias SQL.

VOLATILE: Indica que la función puede devolver diferentes valores, incluso dentro de una consulta individual de una tabla (valor por defecto)

CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT:

CALLED ON NULL INPUT: Indica que la función se ejecutará aunque algunos de los argumentos sean NULL. El usuario tiene la responsabilidad de comprobar si algún argumento es NULL cuando sea necesario tener esto en cuenta.(valor por defecto)

RETURNS NULL ON NULL INPUT / STRICT: Indican que la función no se ejecutará y devolverá el valor NULL si alguno de los argumentos es NULL.

SECURITY INVOKER | SECURITY DEFINER:

SECURITY INVOKER: Indica que la función se ejecutará con los privilegios del usuario que la ejecuta (valor por defecto)

SECURITY DEFINER: Indica que la función se ejecutará con los privilegios del usuario que la creo.

El resto de opciones son avanzadas y podeis leer sobre ellas en la documentación oficial.

Aplicando la poca teoria que hemos visto, vamos a ver unos cuantos ejemplos que nos aclaren un poco como definir, instalar y usar un procedimiento almacenado en PL/pgSQL (estos ejemplos han sido comprobados en postgreSQL 8.3.7).

Creamos una base de datos para utilizarla con nuestros ejemplos:

postgres@server:~$ psql
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# CREATE DATABASE test001;
CREATE DATABASE

postgres=# \c test001
You are now connected to database "test001".

test001=#

Lo primero que tenemos que hacer es instalar el lenguaje plpgsql si no lo tenemos instalado.

CREATE PROCEDURAL LANGUAGE plpgsql;

Si queremos que cualquier usuario con acceso a la base de datos pueda usarlo sin tener que ser el administrador postgres, tendremos que utilizar TRUSTED con el comando anterior.

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql;

A continuación creamos nuestro primer procedimiento. (Podemos copiar y pegar en el cliente psql, escribirlo a mano ó usar el editor interno en psql (\e)):

CREATE OR REPLACE FUNCTION ejemplo() RETURNS integer AS $$
BEGIN
 RETURN 104;
END;
$$ LANGUAGE plpgsql;

Este procedimiento se puede usar de la siguiente manera:

test001=# SELECT ejemplo();

 ejemplo1 
----------
      104
(1 row)

Ahora definimos la función con un argumento:

CREATE OR REPLACE FUNCTION ejemplo(integer) RETURNS integer AS $$
BEGIN
 RETURN $1;
END;
$$ LANGUAGE plpgsql;

Este procedimiento se podria haber escrito tambien de las siguientes maneras:

CREATE OR REPLACE FUNCTION ejemplo(numero integer) RETURNS integer AS $$
BEGIN
 RETURN numero;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION ejemplo(integer) RETURNS integer AS $$
DECLARE
 numero ALIAS FOR $1;

BEGIN
 RETURN numero;
END;
$$ LANGUAGE plpgsql;

Este procedimiento se puede usar de la siguiente manera:

test001=# SELECT ejemplo(104);
 ejemplo 
---------
     104
(1 row)

Vamos a empezar a complicar un poco las cosas usando dos argumentos y definiendo algunas variables:

CREATE OR REPLACE FUNCTION ejemplo(integer, integer) RETURNS integer AS $$
DECLARE
 numero1 ALIAS FOR $1;
 numero2 ALIAS FOR $2;

 constante CONSTANT integer := 100;
 resultado integer;

BEGIN
 resultado := (numero1 * numero2) + constante;

 RETURN resultado;
END;
$$ LANGUAGE plpgsql;

Este procedimiento se puede usar de la siguiente manera:

test001=# SELECT ejemplo(2,2);
 ejemplo 
---------
     104
(1 row)

A continuacion vamos a usar una sentencia IF … THEN en nuestra función:

CREATE OR REPLACE FUNCTION ejemplo_txt(integer, integer) RETURNS text AS $$
DECLARE
 numero1 ALIAS FOR $1;
 numero2 ALIAS FOR $2;

 constante CONSTANT integer := 100;
 resultado INTEGER;

 resultado_txt TEXT DEFAULT 'El resultado es 104'; 

BEGIN
 resultado := (numero1 * numero2) + constante;

 IF resultado <> 104 THEN
    resultado_txt :=  'El resultado NO es 104';
 END IF;

 RETURN resultado_txt;
END;
$$ LANGUAGE plpgsql;

Este procedimiento se puede usar de la siguiente manera:

test001=# SELECT ejemplo_txt(2,2);
     ejemplo_txt     
---------------------
 El resultado es 104
(1 row)


test001=# SELECT ejemplo_txt(2,3);
      ejemplo_txt       
------------------------
 El resultado NO es 104
(1 row)

Podriamos seguir modificando y complicando nuestro ejemplo, pero como introducción es suficiente para que os hagais una idea de como funciona.

Solo queda decir que en la definición de un procedimiento no solo se tiene en cuenta el nombre del mismo para diferenciarlo de otros, los argumentos de la función tambien se tienen en cuenta. ejemplo(), ejemplo(integer), ejemplo(integer, integer) y ejemplo(text) son todos procedimientos diferentes, aunque se llamen igual.

En psql existe un comando muy bueno que nos enseña como una función está definida en la base de datos.

test001=# \x
Expanded display is on.

test001=# \df+ ejemplo
List of functions
-[ RECORD 1 ]-------+----------------------
Schema              | public
Name                | ejemplo
Result data type    | integer
Argument data types | 
Volatility          | volatile
Owner               | postgres
Language            | plpgsql
Source code         | 
                    : BEGIN
                    :  RETURN 104;
                    : END;
                    : 
Description         | 
-[ RECORD 2 ]-------+----------------------
Schema              | public
Name                | ejemplo
Result data type    | integer
Argument data types | integer
Volatility          | volatile
Owner               | postgres
Language            | plpgsql
Source code         | 
                    : DECLARE
                    :  numero ALIAS FOR $1;
                    : 
                    : BEGIN
                    :  RETURN numero;
                    : END;
                    : 
Description         | 
-[ RECORD 3 ]-------+-----------------------------------------------
Schema              | public
Name                | ejemplo
Result data type    | integer
Argument data types | integer, integer
Volatility          | volatile
Owner               | postgres
Language            | plpgsql
Source code         | 
                    : DECLARE
                    :  numero1 ALIAS FOR $1;
                    :  numero2 ALIAS FOR $2;
                    : 
                    :  constante CONSTANT integer := 100;
                    :  resultado integer;
                    : 
                    : BEGIN
                    :  resultado := (numero1 * numero2) + constante;
                    : 
                    :  RETURN resultado;
                    : END;
                    : 
Description         | 

test001=# \df+ ejemplo_txt
List of functions

-[ RECORD 1 ]-------+----------------------------------------------------
Schema              | public
Name                | ejemplo_txt
Result data type    | text
Argument data types | integer, integer
Volatility          | volatile
Owner               | postgres
Language            | plpgsql
Source code         | 
                    : DECLARE
                    :  numero1 ALIAS FOR $1;
                    :  numero2 ALIAS FOR $2;
                    : 
                    :  constante CONSTANT integer := 100;
                    :  resultado INTEGER;
                    : 
                    :  resultado_txt TEXT DEFAULT 'El resultado es 104'; 
                    : 
                    : BEGIN
                    :  resultado := (numero1 * numero2) + constante;
                    : 
                    :  IF resultado <> 104 THEN
                    :     resultado_txt :=  'El resultado NO es 104';
                    :  END IF;
                    : 
                    :  RETURN resultado_txt;
                    : END;
                    : 
Description         |

El resto es solo cuestión de imaginación y de leer la documentación detalladamente. Las posibilidades son infinitas y os aseguro que una vez que empeceis a usar procedimientos almacenados no podreis dejar de usarlos.