Programación

Introducción

La programación del lado servidor consiste principalmente en crear procedimientos almacenados en el servidor utilizando un administrador de procedimientos. PostgreSQL no gestiona los lenguajes de procedimientos, sino que delega esta tarea en un administrador dedicado, lo que permite implementar numerosos lenguajes de programación como lenguaje de procedimientos almacenados. Por ejemplo, es el caso de PL/Perl o PL/Python, que se basa en un intérprete externo a PostgreSQL, mientras que el lenguaje PL/pgSQL se entrega con PostgreSQL.

Procedimientos almacenados

Los lenguajes de procedimientos almacenados no están disponibles por defecto en una base de datos. Se deben instalar en cada base de datos con el comando CREATE LANGUAGE o con el comando de sistema equivalente createlang. Es posible instalar un lenguaje en una base de datos modelo como template1 para hacer que esté disponible automáticamente en las bases de datos creadas a partir de este modelo.

1. SQL

Una función del tipo SQL puede ejecutar una lista de consultas SQL.

En este tipo de función no es posible utilizar comandos de control de transacciones como SAVEPOINT o COMMIT, ni comandos de herramientas como VACUUM, que no se pueden ejecutar dentro de una transacción.

Se pueden ejecutar sentencias DML como SELECT, INSERT, UPDATE, DELETE y las sentencias DDL como CREATE, ALTER o DROP. El cuerpo de la función puede estar enmarcado por comillas simples o por el símbolo del dólar doble, particularmente útiles cuando las consultas contienen comillas simples.

Solo se pueden devolver los datos de la última sentencia SQL. Por defecto, en una función que solo devuelve una única tupla, solo se devolverá la primera línea del último registro, sea cual sea la ordenación aplicada en esta consulta. También es posible para una función devolver un conjunto de registros.

Los argumentos utilizados como entrada de la función se pueden utilizar de dos maneras. Cuando no tienen nombre, su índice permite usarlos directamente, como en el siguiente ejemplo:

CREATE FUNCTION addition(integer, integer)   
RETURNS integer   
LANGUAGE SQL  
AS $$  
   SELECT $1 + $2;  
$$;

Esta función simplemente suma los dos números enteros que se pasan como argumento. Esta misma función se puede escribir utilizando argumentos con nombre, como en el siguiente ejemplo:

CREATE FUNCTION addition( m integer, n integer)   
RETURNS integer   
LANGUAGE SQL  
AS $$  
   SELECT m + n;  
$$;

La elección de los nombres de los argumentos es importante porque pueden existir conflictos con los nombres de los atributos; los atributos son elegidos prioritariamente. Se puede utilizar como prefijo el nombre del argumento junto al nombre de la función, por ejemplo...

Triggers

Un trigger es una función especial que se llama asociada a un evento INSERT, UPDATE o DELETE de una tabla. Independientemente del lenguaje de procedimientos almacenados que utilice para escribir estas funciones, se pueden implementar con el comando CREATE TRIGGER:

CREATE TRIGGER nombre   
{ BEFORE | AFTER | INSTEAD OF }   
{ INSERT | UPDATE [ OF columna [, ...]]   
 | DELETE | TRUNCATE [ OR ... ] }   
   ON tabla   
   [ FOR [ EACH ] { ROW | STATEMENT } ]   
   WHEN ( condición )   
   EXECUTE PROCEDURE nombrefunc ( argumentos )

Un trigger se utiliza antes, después o en lugar de un evento y se puede usar sobre cada registro afectado o una única vez para la operación, con la opción FOR EACH STATEMENT.

Una función de trigger generalmente no tiene argumentos de entrada y devuelve un tipo de datos trigger.

1. Código PL/pgSQL

Cuando una función trigger se escribe en PL/pgSQL, algunas variables se definen implícitamente para conocer el contexto de utilización de la función. Las variables implícitas son las siguientes:

TG_WHEN: momento del desencadenamiento: AFTER o BEFORE, es decir, antes o después de la sentencia SQL que desencadena la llamada a la función.
TG_OP: operación INSERT, UPDATE o DELETE.
TG_TABLE_NAME: nombre de la tabla sobre la que se desencadena la función.
TG_SCHEMA_NAME: nombre del esquema de la tabla sobre la que se desencadena la función.
TG_NARGS, TG_ARGV: número de argumentos y tabla de los argumentos de entrada de la función.

Durante...

Control de funciones

La extensión plpgsql_check permite controlar el código PL/PgSQL de las funciones. El perfilador permite evaluar el detalle de la ejecución de las funciones.

1. Perfilador de funciones

Desde la versión 9.6 de PostgreSQL, existe una extensión que propone perfilar la ejecución de funciones PL/pgSQL para estudiar el comportamiento y, por lo tanto, optimizar su funcionamiento y rendimiento. Esta extensión no está disponible en forma de paquete binario en las distribuciones RedHat o Debian, por lo que es necesario compilar el módulo. Además, no se recomienda realizar las perfilaciones de funciones en un sistema de producción, sino en un entorno de pruebas.

El perfilador está formado por dos partes: un módulo cargado en la instancia PostgreSQL que recoge la información y un comando cliente que recupera la información de la instancia y produce el informe de perfilado.

2. Instalación

El código fuente del perfilador de funciones está disponible en la siguiente dirección: https://www.openscg.com/bigsql/docs/plprofiler/. La página de descarga es la siguiente: https://pypi.org/project/plprofiler/#files. En el momento de escribir este libro, la versión más reciente es la 3.3 y el archivo de las fuentes correspondiente es el siguiente: plprofiler-3.3.tar.gz.

La instalación del perfilador necesita algunas herramientas y librerías, así como los archivos de encabezado de PostgreSQL. Estas dependencias se pueden instalar utilizando el sistema de paquetes de la distribución. En un sistema RedHat:

sudo yum install postgresql10-devel gcc python-setuptools  
python-devel perl

En un sistema Debian:

sudo apt-get install postgresql-server-dev-10 gcc python-setuptools  
python-dev perl

Una vez que se instalan las dependencias, el módulo se compila utilizando los siguientes comandos en un sistema RedHat:

unzip openscg-plprofiler-380a6e19d5a7.zip...