In PostgreSQL können benutzerdefinierte Funktionen erstellt werden, um komplexe Probleme zu lösen.

Diese können mit der Standard-Skriptsprache PL/pgSQL oder in einer anderen Skriptsprache geschrieben werden.

Python, Perl, Tcl und R sind einige der unterstützten Skriptsprachen.

Während PL/pgSQL in jeder Postgres-Installation enthalten ist, sind für die Verwendung anderer Sprachen einige Einstellungen erforderlich.

Installieren der Erweiterung

Bevor eine Erweiterung verwendet werden kann, muss das Erweiterungspaket installiert werden.

Auf Ubuntu würden Sie Folgendes ausführen:

Perl

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

Der Paketname „postgresql-plperl-14“ ist spezifisch für PostgreSQL Version 14. Wenn Sie eine andere Version von PostgreSQL verwenden, müssen Sie die Versionsnummer im Paketnamen ändern, damit sie mit Ihrer installierten PostgreSQL-Version übereinstimmt.

Python 3

sudo apt-get install postgresql-plpython3-14

Aktivieren der Erweiterung

Um die Erweiterung in PostgreSQL zu aktivieren, muss die Erweiterung mit der CREATE EXTENSION-Anweisung definiert werden.

Perl

CREATE EXTENSION plperl;

Python

CREATE EXTENSION plpython3;

Hallo Welt-Beispiel

Sobald die Erweiterung erstellt wurde, kann eine benutzerdefinierte Funktion mit der Erweiterung erstellt werden.

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;

Dies Zeile für Zeile aufschlüsseln

CREATE OR REPLACE FUNCTION hello(name text)

In dieser Zeile wird eine Funktion in Postgres erstellt. Durch die Verwendung von CREATE OR REPLACE wird jede Funktion, die bereits mit dem Namen hello definiert ist, mit der neuen Funktion überschrieben.

Die Verwendung von CREATE FUNCTION hello(name text) verhindert, dass die Funktion eine vorhandene Funktion überschreibt, und führt zu einem Fehler, wenn die Funktion bereits vorhanden ist.

RETURNS text AS $$

Dies definiert, welcher Postgres-Datentyp zurückgegeben wird. Es ist wichtig, dass der angegebene Datentyp ein von Postgres erkannter Typ ist. Ein benutzerdefinierter Datentyp kann angegeben werden, wenn der benutzerdefinierte Typ bereits definiert ist.

$$ ist ein Trennzeichen, um den Anfang und das Ende eines Codeblocks zu markieren. In dieser Zeile markiert es den Anfang des Codeblocks.

Der gesamte Code zwischen Start und Ende $$ wird von Postgres ausgeführt

$$ LANGUAGE plperl;

$$ bezeichnet das Ende des Skripts und teilt Postgres mit, in welcher Sprache das Skript analysiert werden soll.

Verwendung der Funktion

Funktionen können wie jede integrierte Postgres-Funktion verwendet werden

SELECT hello('world');

Dies gibt eine Spalte mit dem Wert „Hallo Welt!“ zurück.

Funktionen können Teil komplexerer Abfragen sein:

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

Komplexeres Beispiel

Hier ist eine Beispielfunktion, die Text aus einem Feld akzeptiert und eine Wortanzahl zurückgibt.

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;

Dies gibt ein JSON-formatiertes Ergebnis mit der Wortanzahl zurück.

Wir können der Funktion detailliertere Statistiken hinzufügen.

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;

Wenn wir es jetzt in einer Abfrage verwenden

SELECT word_count(text_field) word_count FROM table

Es wird JSON zurückgegeben wie

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

Sicherheitsüberlegungen

Bei der Verwendung benutzerdefinierter Funktionen oder externer Skriptsprachen müssen zusätzliche Sicherheitsaspekte berücksichtigt werden. Es kann eine schwierige Aufgabe sein, die richtige Balance zwischen Benutzerfreundlichkeit und Sicherheit zu finden.

Security Definer vs. Security Invoker

In der vorherigen Funktion wurde die Option SECURITY DEFINER zur Anweisung zum Erstellen der Funktion hinzugefügt.

Es ist wichtig, darüber nachzudenken, wie eine Funktion aus Sicherheitsgründen ausgeführt werden soll.

Das Standardverhalten ist die Verwendung von SECURITY INVOKER. Dadurch wird die Funktion mit den Berechtigungen des Benutzers ausgeführt, der die Funktion ausführt.

SECURITY DEFINER bietet mehr Kontrolle über die der Funktion gewährten Berechtigungen. In diesem Modus wird die Funktion mit den Berechtigungen des Benutzers ausgeführt, der die Funktion erstellt hat.

Dies kann sowohl gut als auch schlecht sein. Wenn eine Funktion von einem Benutzer mit eingeschränkten Berechtigungen erstellt wird, kann der Datenbank kaum Schaden zugefügt werden.

Wenn die Funktion von einem Benutzer mit hohen Zugriffsrechten erstellt wird, wird die Funktion mit denselben Rechten ausgeführt. Abhängig von der Art der Funktion kann dies einem Benutzer ermöglichen, die Funktion mit mehr offenen Berechtigungen auszuführen, als ihm gewährt wurden.

Es gibt Zeiten, in denen dies nützlich ist, zum Beispiel wenn ein Benutzer keine Leserechte für eine Tabelle hat, aber innerhalb der Funktion ein Lesevorgang erforderlich ist, kann die Verwendung von SECURITY DEFINER die erforderlichen Leserechte für die Ausführung der Funktion zulassen.

Vertrauenswürdige und nicht vertrauenswürdige Erweiterungen

Beim Erstellen der oben genannten Erweiterungen wurden plperl und plpython3 verwendet. In den meisten Fällen sind dies die richtigen Erweiterungen.

Diese Erweiterungen haben eingeschränkten Zugriff auf das Dateisystem und die Systemaufrufe des Servers.

Erweiterungen können auch mit einem u (plpython3u, plperlu)

Dies sind nicht vertrauenswürdige Erweiterungen und ermöglichen mehr Zugriff auf das Dateisystem des Servers.

Es kann Fälle geben, in denen dies erforderlich ist, beispielsweise wenn Sie Perl-Module, Python-Bibliotheken oder Systemaufrufe verwenden möchten.

Im obigen Beispiel wurde die JSON-Ausgabe als String generiert. Bei Bedarf hätte das Perl-JSON-Modul verwendet werden können, um die Daten als JSON zu kodieren. Dazu müsste die nicht vertrauenswürdige Erweiterung verwendet werden, um auf das JSON-Modul zuzugreifen.

Es wird empfohlen, die nicht vertrauenswürdigen Erweiterungen nicht zu verwenden. Gehen Sie bei Bedarf jedoch mit Vorsicht vor und verstehen Sie die potenziellen Risiken.

Wenn Perl verwendet wird, wird Perl im Taint-Modus ausgeführt, wenn die nicht vertrauenswürdige Erweiterung verwendet wird.

Abschließende Gedanken

Die Möglichkeit, die erweiterte Textverarbeitung und Speicherverwaltung von Perl oder die Datenanalysebibliotheken von Python in PostgreSQL zu nutzen, kann ein wirklich leistungsstarkes Werkzeug sein.

Die Übergabe komplexer Aufgaben an Tools, die besser für die Bearbeitung der Aufgabe geeignet sind, kann den Overhead für die Datenbank reduzieren.

Treffen Sie wie immer Vorsichtsmaßnahmen, um eine sichere Nutzung zu gewährleisten, wenn Sie benutzerdefinierte Funktionen und externe Skriptsprachen verwenden.