PostgreSQL - Funciones

Como en la mayoría de bases de datos, nosotros podemos encadenar una serie de sentencias SQL y tratarlas como una unidad de un bloque funcional; las diferentes bases de datos describen este funcionamiento con diferentes nombres, stored procedures, módulos, macros, etc.

 

En PostgreSQL se denominan funciones. Aparte de unificar varias sentencias SQL, estas funciones también nos dan la posibilidad de ejecutar las sentencias SQL utilizando lenguajes procedimentales (PL).

 

En PostgreSQL tenemos múltiples opciones para esto y la posibilidad de expandirlos.

 

1. Funciones PostgreSQL

Independientemente del lenguaje utilizado para escribir las funciones, estas poseen una estructura, dicha estructura la podemos sintetizar con lo siguiente:

CREATE OR REPLACE FUNCTION func_name(
arg1_arg1datatype)
RETURNS some_type / setoff sometype / TABLE / (..) / AS
$$
BODY off function
$$
LANGUAGE language_of_function

Si describimos lo que vemos es bastante sencillo, CREATE OR REPLACE FUNCTION es la cláusula de creación de la función, func_name es el nombre que tendrá arg1 es el parámetro que recibirá y arg1_datatype es el tipo de dato que es dicho parámetro, es decir si es un entero una cadena etc. En RETURNS devolvemos el resultado de nuestra función, $$ es el inicio del bloque que dará paso al cuerpo de la función y luego finaliza igual con $$ y por ultimo LANGUAGE nos permite especificar el lenguaje en el que está escrita la función.

 

Es una estructura bien definida y bastante legible por lo que no deberíamos tener problemas creando nuestras funciones.

 

Escribir las funciones utilizando SQL es bastante fácil y rápido, es tomar nuestras sentencias SQL básicamente y agregarle la cabecera y el pie de las funciones y estamos listos.

 

Sin embargo como todo, esto viene a costa de algunos sacrificios, por ejemplo perdemos flexibilidad que sí lograríamos de trabajar con otro lenguaje para crear más ramas de control de ejecución condicional, no podemos tener más de una sentencia SQL, aunque esto se puede remediar utilizando varios métodos.

 

La mayor ventaja es que al ser SQL el planificador de PostgreSQL nos permite tomar ventaja de los índices y de esta forma hacer más veloz la ejecución de la misma, en cambio con otros lenguajes la función siempre será una caja negra

 

Ahora veamos una función escrita con SQL:

CREATE OR REPLACE FUNCTION ins_logs(param_user_name varchar, param_description text)RETURNS integer AS$$ INSERT INTO logs(user_name, description) VALUES($1, $2)RETURNING log_id; $$LANGUAGE 'sql' VOLATILE;

Vemos que seguimos la estructura definida anteriormente y al final en la sección LANGUAGE definimos 'sql' la cláusula VOLATILE que posee significa que la función puede retornar algo diferente con cada llamada que se le haga, incluso si recibe los mismos parámetros. Luego para llamar a nuestra función podemos utilizar:

SELECT ins_logs('lhsu', 'this is a test') As new_id;

Realizamos una sentencia SELECT, esta activa la función y lo que retorne es lo que recibiremos y veremos en este caso un valor que llamamos new_id y que la función retorna como log_id.
Incluso podemos utilizar una función para hacer una actualización de registro y regresar un parámetro void como en este ejemplo:

CREATE OR REPLACE FUNCTION upd_logs(log_id integer, param_user_name varchar,param_description text)RETURNS void AS$$ UPDATE logs SET user_name = $2, description = $3, log_ts = CURRENT_TIMESTAMPWHERE log_id = $1;$$LANGUAGE 'sql' VOLATILE;

Al ser void no necesitamos un campo receptor por lo que la ejecutamos de la siguiente forma:

SELECT upd_logs(12,'robe', 'Change to regina');

Aquí podemos ver que eliminamos el último paso de As new_id de la llamada anterior.
Con esto finalizamos este tutorial, ya podemos hacer nuestras funciones básicas en SQL, de esta forma facilitar y simplificar muchas actividades que podamos necesitar dentro de un programa o sistema que estemos construyendo.

 

2. Funciones PostgreSQL en otros lenguajes

Una de las características más atractivas de PostgreSQL es que no se limita solamente a lenguaje SQL, gracias a la carga de módulos podemos optar por incorporar funcionalidades avanzadas, entre ellas la posibilidad de utilizar diferentes lenguajes para construir funciones, con esto podemos lograr una gran flexibilidad utilizando mejores capacidades de generación de condicionales y las ventajas inherentes a los diversos lenguajes.

 

En el momento en que notamos que el estándar SQL se nos queda corto para las consultas que deseamos realizar en una función, siempre podemos apelar al uso de PL/pgSQL; una de sus diferencias y mejoras en relación al SQL es que se pueden declarar variables locales utilizando la sentencia DECLARE, podemos también tener control sobre el flujo y debemos encerrar el cuerpo de la función en un bloque BEGIN END.

 

Veamos un ejemplo de una función escrita en este lenguaje:

CREATE FUNCTION sel_logs_rt(param_user_name varchar)
RETURNS TABLE (log_id int, user_name varchar(50), description text, log_ts timestamptz) AS
$$
BEGIN
RETURN QUERY
SELECT log_id, user_name, description, log_ts
FROM logs WHERE user_name = param_user_name;
END;
$$
LANGUAGE 'plpgsql' STABLE;

Ahora veamos cómo escribir funciones con Python.

 

Python es un lenguaje de programación bastante limpio, que posee una gran cantidad de librerías disponibles.
PostgreSQL es el único motor de bases de datos que permite utilizar Python para construir funciones.

 

Para poder obtener la posibilidad de crear funciones con Python primero debemos asegurarnos que poseemos el lenguaje instalado en nuestro servidor. Una vez que sepamos que lo tenemos instalado debemos habilitar las extensiones dentro de PostgreSQL utilizando los siguientes comandos:

CREATE EXTENSION plpython2u;
CREATE EXTENSION plpython3u;

Debemos asegurarnos que Python este arriba y corriendo antes de habilitar las extensiones para evitar los errores.

 

Una vez que ya tenemos todo activado para poder utilizar Python vamos a iniciar a construir nuestra función, es importante saber que PostgreSQL puede convertir sus tipos de datos a tipos de datos de Python y viceversa. PL/Python es incluso capaz de retornar arrays y tipos compuestos.

 

Veamos a continuación una función que realiza una búsqueda de texto en un recurso en línea, algo que no pudiera realizarse con PL/pgSQL, en la siguiente imagen veremos el código y luego haremos la explicación correspondiente.

• Importamos las librerías que vamos a utilizar.
• Hacemos la búsqueda Web concatenando los parámetros de entrada del usuario.
• Leemos la respuesta y la salvamos a un archivo HTML llamado raw_html.
• Salvamos la parte del HTML que inicia con <!-- docbot goes here --> y termina antes del inicio de <!-- pgContentWrap -->.
• Quitamos las etiquetas HTML y los espacios en blanco y volvemos a salvar a la variable llamada resultado.
• Retornamos el resultado final.
• Otra funcionalidad interesante de utilizar Python es que podemos interactuar directamente con el sistema operativo, veamos una función que hace un listado de directorios, cabe destacar que esto debe crearlo un superusuario:

CREATE OR REPLACE FUNCTION list_incoming_files()
RETURNS SETOF text AS
$$
import os
return os.listdir('/incoming')
$$
LANGUAGE 'plpython2u' VOLATILE SECURITY DEFINER;

¿Cuál es la utilidad de esto? Podemos preguntarnos, pues imaginemos que queremos consultar los archivos que tenemos disponibles en un sistema, la llamada a la función sería algo así:

SELECT filename FROM list_incoming_files() As filename WHERE filename ILIKE '%.csv'

Con esto finalizamos este tutorial, ya manejamos la creación de funciones en otros lenguajes en PostgreSQL, lo que nos da un campo infinito a la hora de poder realizar nuestros requerimientos.