В PostgreSQL можно создавать собственные функции для решения сложных задач.

Они могут быть написаны с использованием языка сценариев PL/pgSQL по умолчанию или на другом языке сценариев.

Python, Perl, Tcl и R — некоторые из поддерживаемых языков сценариев.

Хотя PL/pgSQL поставляется с любой установкой Postgres, для использования других языков требуется определенная настройка.

Установка расширения

Прежде чем использовать расширение, необходимо установить пакет расширения.

В Ubuntu вы должны запустить:

Perl

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

Имя пакета «postgresql-plperl-14» относится к PostgreSQL версии 14. Если вы используете другую версию PostgreSQL, вам необходимо изменить номер версии в имени пакета, чтобы он соответствовал установленной версии PostgreSQL.

Имя пакета «postgresql-plperl-14» относится к PostgreSQL версии 14. &&&]

Python 3

sudo apt-get install postgresql-plpython3-14

Активация расширения

Чтобы активировать расширение в PostgreSQL, расширение должно быть определено с помощью оператора CREATE EXTENSION.

Perl

CREATE EXTENSION plperl;

Python

CREATE EXTENSION plpython3;

Привет, мир, пример

После создания расширения с его помощью можно создать пользовательскую функцию.

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;

Разбивка по строкам

CREATE OR REPLACE FUNCTION hello(name text)

Эта строка описывает создание функции в Postgres. Используя CREATE OR REPLACE, он перезапишет любую функцию, уже определенную с именем hello новой функцией.

Использование CREATE FUNCTION hello(текст имени) не позволит функции перезаписать существующую функцию и выдаст ошибку, если функция уже существует.

RETURNS text AS $$

Это определяет, какой тип данных Postgres будет возвращен. Важно, чтобы указанный тип данных был типом, распознаваемым Postgres. Можно указать пользовательский тип данных, если пользовательский тип уже определен.

$$ — разделитель, обозначающий начало и конец блока кода. В этой строке отмечается начало блока кода.

Весь код между началом и концом $$ будет выполнен Postgres

$$ LANGUAGE plperl;

$$ обозначает конец скрипта и сообщает Postgres, на каком языке скрипт следует анализировать.

Использование функции

Функции можно использовать как любую встроенную функцию Postgres

SELECT hello('world');

Это вернет столбец со значением «Привет, мир!»

Функции могут быть частью более сложных запросов:

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

Более сложный пример

Вот пример функции, которая принимает текст из поля и возвращает количество слов.

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;

Это возвращает результат в формате JSON с количеством слов.

В функцию можно добавить более подробную статистику.

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;

Теперь, когда мы используем его в запросе

SELECT word_count(text_field) word_count FROM table

Он вернет JSON, например

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

Соображения безопасности

При использовании пользовательских функций или внешних языков сценариев необходимо учитывать дополнительные соображения безопасности. Чтобы найти правильный баланс между удобством использования и безопасностью, может потребоваться жонглирование.

Определитель безопасности против Security Invoker

В предыдущей функции в оператор создания функции был добавлен параметр SECURITY DEFINER.

Важно подумать о том, как вы хотите, чтобы функция запускалась с точки зрения безопасности.

По умолчанию используется SECURITY INVOKER. Функция будет запущена с привилегиями пользователя, который ее запускает.

SECURITY DEFINER обеспечивает больший контроль над привилегиями, предоставленными функции. В этом режиме функция будет запускаться с привилегиями пользователя, создавшего функцию.

Это может быть как хорошо, так и плохо: если функция создается пользователем с ограниченными привилегиями, то вреда для базы данных мало.

Если функция создана пользователем с высокими правами доступа, то функция будет запускаться с теми же правами. В зависимости от типа функции это может позволить пользователю запустить функцию с большим количеством открытых привилегий, чем ему было предоставлено.

Иногда это полезно, например, если у пользователя нет прав на чтение таблицы, но внутри функции чтение требуется, использование SECURITY DEFINER может предоставить необходимые права чтения для запуска функции.

Доверенные и ненадежные расширения

При создании вышеуказанных расширений использовались plperl и plpython3. В большинстве случаев эти расширения подходят для использования.

Эти расширения имеют ограниченный доступ к файловой системе серверов и системным вызовам.

Расширения также можно создавать с помощью u (plpython3u, plperlu)

Это ненадежные расширения, которые предоставляют больший доступ к файловой системе сервера.

В некоторых случаях это может потребоваться, например, если вы хотите использовать модули Perl, библиотеки Python или использовать системные вызовы.

В приведенном выше примере выходные данные JSON были сгенерированы в виде строки. При желании модуль Perl JSON можно было использовать для кодирования данных в формате JSON. Для этого потребуется использовать ненадежное расширение для доступа к модулю JSON.

Желательно не использовать ненадежные расширения, но при необходимости используйте их с осторожностью и понимайте потенциальные риски.

Если используется Perl, Perl будет работать в вредоносном режиме, когда используется ненадежное расширение.

Последние мысли

Возможность воспользоваться преимуществами расширенной обработки текста и управления памятью Perls или библиотек анализа данных Pythons в PostgreSQL может стать действительно мощным инструментом.

Передача сложных задач инструментам, более подходящим для решения этой задачи, может снизить нагрузку на базу данных.

Как всегда, при использовании пользовательских функций и внешних языков сценариев соблюдайте меры предосторожности для обеспечения безопасного использования.