Dans PostgreSQL, des fonctions personnalisées peuvent être créées pour résoudre des problèmes complexes.

Ceux-ci peuvent être écrits en utilisant le langage de script PL/pgSQL par défaut, ou ils peuvent être écrits dans un autre langage de script.

Python, Perl, Tcl et R sont quelques-uns des langages de script pris en charge.

Bien que PL/pgSQL soit fourni avec toute installation de Postgres, l'utilisation d'autres langages nécessite une certaine configuration.

Installation de l'extension

Avant qu'une extension puisse être utilisée, le package d'extension doit être installé.

Sur Ubuntu, vous exécuteriez :

Perl

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

Le nom du package 'postgresql-plperl-14' est spécifique à PostgreSQL version 14. Si vous utilisez une version différente de PostgreSQL, vous devez modifier le numéro de version dans le nom du package pour qu'il corresponde à votre version de PostgreSQL installée.

Python 3

sudo apt-get install postgresql-plpython3-14

Activation de l'extension

Pour activer l'extension dans PostgreSQL, l'extension doit être définie à l'aide de l'instruction CREATE EXTENSION.

Perl

CREATE EXTENSION plperl;

Python

CREATE EXTENSION plpython3;

Bonjour tout le monde, exemple

Une fois l'extension créée, une fonction personnalisée peut être créée à l'aide de l'extension.

Perl

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

Python

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

Répartir cette ligne par ligne

CREATE OR REPLACE FUNCTION hello(name text)

Cette ligne explique comment une fonction est créée dans Postgres. En utilisant CREATE OR REPLACE, il écrasera toute fonction déjà définie avec le nom hello par la nouvelle fonction.

L'utilisation de CREATE FUNCTION hello(name text) empêchera la fonction d'écraser une fonction existante et générera une erreur si la fonction existe déjà.

RETURNS text AS $$

Ceci définit le type de données Postgres qui sera renvoyé, il est important que le type de données spécifié soit un type reconnu par Postgres. Un type de données personnalisé peut être spécifié, si le type personnalisé est déjà défini.

$$ est un délimiteur pour marquer le début et la fin d'un bloc de code. Dans cette ligne, cela marque le début du bloc de code.

Tout le code entre le début et la fin $$ sera exécuté par Postgres

$$ LANGUAGE plperl;

$$ désigne la fin du script et indique à Postgres dans quelle langue le script doit être analysé.

Utilisation de la fonction

Les fonctions peuvent être utilisées comme n'importe quelle fonction Postgres intégrée

SELECT hello('world');

Cela renverra une colonne avec la valeur Hello world !

Les fonctions peuvent faire partie de requêtes plus complexes :

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

Exemple plus complexe

Voici un exemple de fonction qui accepte le texte d'un champ et renvoie un nombre de mots.

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;

Cela renvoie un résultat au format JSON avec le nombre de mots.

Nous pouvons ajouter des statistiques plus détaillées à la fonction.

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;

Maintenant, lorsque nous l'utilisons dans une requête

SELECT word_count(text_field) word_count FROM table

Il renverra JSON comme

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

Considérations de sécurité

Lors de l'utilisation de fonctions personnalisées ou de langages de script externes, des considérations de sécurité supplémentaires doivent être prises en compte. Il peut être difficile de trouver le bon équilibre entre convivialité et sécurité.

Définisseur de sécurité vs invocateur de sécurité

Dans la fonction précédente, l'option SECURITY DEFINER a été ajoutée à l'instruction de fonction de création.

Il est important de réfléchir à la manière dont vous souhaitez qu'une fonction soit exécutée du point de vue de la sécurité.

Le comportement par défaut consiste à utiliser SECURITY INVOKER. Cela exécutera la fonction avec les privilèges de l'utilisateur qui exécute la fonction.

SECURITY DEFINER offre plus de contrôle sur les privilèges accordés à la fonction. En utilisant ce mode, la fonction s'exécutera avec les privilèges de l'utilisateur qui a créé la fonction.

Cela peut être à la fois bon et mauvais, si une fonction est créée par un utilisateur avec des privilèges limités, alors peu de dommages peuvent être causés à la base de données.

Si la fonction est créée par un utilisateur disposant de privilèges d'accès élevés, la fonction s'exécutera avec ces mêmes privilèges. Selon le type de fonction, cela pourrait permettre à un utilisateur d'exécuter la fonction avec plus de privilèges ouverts que ceux qui lui ont été accordés.

Il y a des moments où cela est utile, par exemple, si un utilisateur n'a pas de privilèges de lecture sur une table, mais que dans la fonction, la lecture est requise, l'utilisation de SECURITY DEFINER peut autoriser les privilèges de lecture requis pour que la fonction s'exécute.

Extensions fiables et non fiables

Lors de la création des extensions ci-dessus, plperl et plpython3 ont été utilisés. Dans la plupart des cas, ce sont les bonnes extensions à utiliser.

Ces extensions ont un accès limité au système de fichiers du serveur et aux appels système.

Des extensions peuvent également être créées avec un u (plpython3u, plperlu)

Ce sont des extensions non fiables et permettent un meilleur accès au système de fichiers des serveurs.

Il peut y avoir des cas où cela est nécessaire, par exemple si vous souhaitez utiliser des modules Perl, des bibliothèques Python ou utiliser des appels système.

Dans l'exemple ci-dessus, la sortie JSON a été générée sous forme de chaîne. Si vous le souhaitez, le module Perl JSON aurait pu être utilisé pour coder les données au format JSON. Pour ce faire, il faudrait utiliser l'extension non fiable pour accéder au module JSON.

Il est conseillé de ne pas utiliser les extensions non fiables, mais si nécessaire, utilisez-les avec prudence et comprenez les risques potentiels.

Si Perl est utilisé, Perl s'exécutera en mode contamination lorsque l'extension non fiable est utilisée.

Dernières pensées

Pouvoir profiter du traitement de texte avancé et de la gestion de la mémoire de Perl, ou des bibliothèques d'analyse de données Python dans PostgreSQL, peut être un outil vraiment puissant.

Transférer des tâches complexes à des outils plus adaptés à la gestion de la tâche peut réduire la surcharge de la base de données.

Comme toujours, lorsque vous utilisez des fonctions personnalisées et des langages de script externes, prenez des précautions pour garantir une utilisation sécurisée.