En PostgreSQL, se pueden crear funciones personalizadas para resolver problemas complejos.

Estos se pueden escribir utilizando el lenguaje de secuencias de comandos PL/pgSQL predeterminado, o se pueden escribir en otro lenguaje de secuencias de comandos.

Python, Perl, Tcl y R son algunos de los lenguajes de programación admitidos.

Si bien PL/pgSQL viene con cualquier instalación de Postgres, para usar otros lenguajes se requiere cierta configuración.

Instalación de la extensión

Antes de poder utilizar una extensión, es necesario instalar el paquete de extensión.

En Ubuntu ejecutarías:

Perl

sudo apt-get -y install postgresql-plperl-14

El nombre del paquete 'postgresql-plperl-14' es específico de la versión 14 de PostgreSQL. Si está utilizando una versión diferente de PostgreSQL, debe cambiar el número de versión en el nombre del paquete para que coincida con su versión de PostgreSQL instalada.

Python 3

sudo apt-get install postgresql-plpython3-14

Activando la extensión

Para activar la extensión en PostgreSQL, la extensión debe definirse usando la instrucción CREATE EXTENSION.

Perl

CREATE EXTENSION plperl;

Pitón

CREATE EXTENSION plpython3;

ejemplo de hola mundo

Una vez creada la extensión, se puede crear una función personalizada usando la extensión.

Perl

CREATE OR REPLACE FUNCTION hello(name text) 
RETURNS text AS $$
    my ($name) = @_;
    return "Hello, $name!";
$$ LANGUAGE plperl;

Pitón

CREATE OR REPLACE FUNCTION hello(name text)
RETURNS text AS $$
    return "Hello, "   name   "!"
$$ LANGUAGE plpython3;

Desglosando esto línea por línea

CREATE OR REPLACE FUNCTION hello(name text)

Esta línea es cómo se crea una función en Postgres. Al usar CREAR O REEMPLAZAR, sobrescribirá cualquier función que ya esté definida con el nombre hola con la nueva función.

El uso de CREATE FUNCTION hola (nombre del texto) evitará que la función sobrescriba una función existente y generará un error si la función ya existe.

RETURNS text AS $$

Esto define qué tipo de datos de Postgres se devolverá; es importante que el tipo de datos especificado sea un tipo reconocido por Postgres. Se puede especificar un tipo de datos personalizado, si el tipo personalizado ya está definido.

$$ es un delimitador para marcar el principio y el final de un bloque de código. En esta línea marca el inicio del bloque de código.

Todo el código entre el inicio y el final $$ será ejecutado por Postgres

$$ LANGUAGE plperl;

$$ indica el final del script y le dice a Postgres en qué idioma se debe analizar el script.

Usando la función

Las funciones se pueden utilizar como cualquier función integrada de Postgres

SELECT hello('world');

Esto devolverá una columna con el valor ¡Hola mundo!

Las funciones pueden ser parte de consultas más complejas:

SELECT id, title, hello('world') greeting FROM table;

Ejemplo más complejo

Aquí hay una función de ejemplo que acepta texto de un campo y devuelve un recuento de palabras.

CREATE OR REPLACE FUNCTION word_count(paragraph text)
RETURNS json AS $$
use strict;
use warnings;

my ($text) = @_;

my @words = $text =~ /\w /g;
my $word_count = scalar @words;

my $result = '{' .
    '"word_count":' . $word_count .
'}';
return $result;
$$ LANGUAGE plperl;

Esto devuelve un resultado en formato JSON con el recuento de palabras.

Podemos agregar estadísticas más detalladas a la función.

CREATE OR REPLACE FUNCTION word_count(paragraph text)
RETURNS json AS $$
use strict;
use warnings;

my ($text) = @_;

my @words = $text =~ /\w /g;

my $word_count = scalar @words;

my $sentence_count = ( $text =~ tr/!?./!?./ ) || 0;

my $average_words_per_sentence =
  $sentence_count > 0 ? $word_count / $sentence_count : 0;

my $result = '{' .
    '"word_count":' . $word_count . ',' .
    '"sentence_count":' . $sentence_count . ',' .
    '"average_words_per_sentence":"' . sprintf("%.2f", $average_words_per_sentence) . '"' .
'}';

return $result;
$$ LANGUAGE plperl SECURITY DEFINER;

Ahora cuando lo usamos en una consulta

SELECT word_count(text_field) word_count FROM table

Devolverá JSON como

{"word_count":116,"sentence_count":15,"average_words_per_sentence":"7.73"}

Consideraciones de Seguridad

Al utilizar funciones personalizadas o lenguajes de secuencias de comandos externos, hay consideraciones de seguridad adicionales que se deben tener en cuenta. Puede ser un acto de malabarismo conseguir el equilibrio adecuado entre usabilidad y seguridad.

Definidor de seguridad frente a invocador de seguridad

En la función anterior, se agregó la opción SECURITY DEFINER a la declaración de creación de función.

Es importante pensar en cómo desea que se ejecute una función desde el punto de vista de la seguridad.

El comportamiento predeterminado es utilizar SECURITY INVOKER. Esto ejecutará la función con los privilegios del usuario que ejecuta la función.

SECURITY DEFINER proporciona más control sobre los privilegios otorgados a la función. Al usar este modo, la función se ejecutará con los privilegios del usuario que creó la función.

Esto puede ser tanto bueno como malo, si una función es creada por un usuario con privilegios limitados, entonces hay poco daño que se le pueda hacer a la base de datos.

Si la función es creada por un usuario con altos privilegios de acceso, entonces la función se ejecutará con esos mismos privilegios. Dependiendo del tipo de función, esto podría permitir a un usuario ejecutar la función con más privilegios abiertos de los que se le han otorgado.

Hay ocasiones en las que esto es útil, por ejemplo, si un usuario no tiene privilegios de lectura en una tabla, pero dentro de la función, se requiere lectura, usar SECURITY DEFINER puede permitir los privilegios de lectura necesarios para que se ejecute la función.

Extensiones confiables y no confiables

Al crear las extensiones anteriores, se utilizaron plperl y plpython3. En la mayoría de las circunstancias, estas son las extensiones adecuadas a utilizar.

Estas extensiones tienen acceso limitado al sistema de archivos del servidor y a las llamadas al sistema.

Las extensiones también se pueden crear con un u (plpython3u, plperlu)

Estas son extensiones que no son de confianza y permiten más acceso al sistema de archivos del servidor.

Puede haber casos en los que esto sea necesario, por ejemplo, si desea utilizar módulos Perl, bibliotecas Python o utilizar llamadas al sistema.

En el ejemplo anterior, la salida JSON se generó como una cadena; si se desea, se podría haber utilizado el módulo JSON de Perl para codificar los datos como JSON. Para hacer esto, sería necesario utilizar la extensión que no es de confianza para acceder al módulo JSON.

Es recomendable no utilizar extensiones que no sean de confianza, pero si es necesario, úselas con precaución y comprenda los riesgos potenciales.

Si se utiliza Perl, Perl se ejecutará en modo corrupto cuando se utilice la extensión que no es de confianza.

Pensamientos finales

Poder aprovechar el procesamiento de texto avanzado y la administración de memoria de Perls, o las bibliotecas analíticas de datos de Pythons dentro de PostgreSQL, puede ser una herramienta realmente poderosa.

Pasar tareas complejas a herramientas más adecuadas para manejar la tarea puede reducir la sobrecarga de la base de datos.

Como siempre, cuando utilice funciones personalizadas y lenguajes de secuencias de comandos externos, tome precauciones para garantizar un uso seguro.