PostgreSQL では、複雑な問題を解決するためにカスタム関数を作成できます。

これらは、デフォルトの PL/pgSQL スクリプト言語を使用して作成することも、別のスクリプト言語で作成することもできます。

Python、Perl、Tcl、R はサポートされているスクリプト言語の一部です。

PL/pgSQL は Postgres のインストールに付属していますが、他の言語を使用するにはいくつかのセットアップが必要です。

拡張機能のインストール

拡張機能を使用するには、拡張機能パッケージをインストールする必要があります。

Ubuntu では次を実行します:

パール

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

パッケージ名「postgresql-plperl-14」は PostgreSQL バージョン 14 に固有です。別のバージョンの PostgreSQL を使用している場合は、インストールされている PostgreSQL バージョンと一致するようにパッケージ名のバージョン番号を変更する必要があります。

Python 3

sudo apt-get install postgresql-plpython3-14

拡張機能を有効にする

PostgreSQL で拡張機能をアクティブにするには、CREATE EXTENSION ステートメントを使用して拡張機能を定義する必要があります。

パール

CREATE EXTENSION plperl;

Python

CREATE EXTENSION plpython3;

こんにちは世界の例

拡張機能が作成されたら、その拡張機能を使用してカスタム関数を作成できます。

パール

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(name text) を使用すると、関数が既存の関数を上書きできなくなり、関数がすでに存在する場合はエラーが発生します。

RETURNS text AS $$

これは、どの Postgres データ型が返されるかを定義します。指定されたデータ型が Postgres によって認識される型であることが重要です。カスタム データ型がすでに定義されている場合は、カスタム データ型を指定できます。

$$ は、コード ブロックの始まりと終わりをマークする区切り文字です。この行では、コード ブロックの開始をマークしています。

開始 $$ と終了 $$ の間のすべてのコードは Postgres によって実行されます

$$ LANGUAGE plperl;

$$ はスクリプトの終わりを示し、Postgres にスクリプトを解析する言語を指示します。

機能の使用

関数は組み込みの Postgres 関数と同様に使用できます

SELECT hello('world');

これにより、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"}

セキュリティに関する考慮事項

カスタム関数または外部スクリプト言語を使用する場合は、追加のセキュリティ考慮事項を考慮する必要があります。使いやすさとセキュリティの適切なバランスを保つのは、難しい作業になる可能性があります。

セキュリティ定義者とセキュリティ呼び出し者

前の関数では、create function ステートメントに SECURITY DEFINER オプションが追加されました。

セキュリティの観点から関数をどのように実行するかを考えることが重要です。

デフォルトの動作では、SECURITY INVOKER を使用します。これにより、関数を実行しているユーザーの権限で関数が実行されます。

SECURITY DEFINER は、関数に付与される権限をより詳細に制御します。このモードを使用すると、関数は関数を作成したユーザーの権限で実行されます。

これは良いことにも悪いことにもなり得ます。権限が制限されたユーザーによって関数が作成された場合、データベースに悪影響を与える可能性はほとんどありません。

高いアクセス権限を持つユーザーによって関数が作成された場合、関数は同じ権限で実行されます。関数の種類によっては、ユーザーが付与されている以上のオープン権限で関数を実行できる可能性があります。

これが役立つ場合があります。たとえば、ユーザーがテーブルに対する読み取り権限を持っていないが、関数内で読み取りが必要な場合、SECURITY DEFINER を使用すると、関数の実行に必要な読み取り権限を許可できます。

信頼できる拡張機能と信頼できない拡張機能

上記の拡張機能を作成するときは、plperl と plpython3 が使用されました。ほとんどの状況では、これらは使用するのに適切な拡張機能です。

これらの拡張機能は、サーバーのファイル システムおよびシステム コールへのアクセスが制限されています。

拡張機能は、u (plpython3u、plperlu)

これらは信頼できない拡張子であり、サーバー ファイル システムへのアクセスをさらに許可します。

これが必要になる場合があります。たとえば、Perl モジュール、Python ライブラリを使用する場合、またはシステム コールを使用する場合です。

上記の例では、JSON 出力は文字列として生成されていますが、必要に応じて、perl JSON モジュールを使用してデータを JSON としてエンコードすることもできます。これを行うには、信頼できない拡張機能を使用して JSON モジュールにアクセスする必要があります。

信頼できない拡張機能は使用しないことをお勧めしますが、必要に応じて、潜在的なリスクを理解し、注意して使用してください。

Perl が使用されている場合、信頼できない拡張機能が使用されている場合、Perl はテイント モードで実行されます。

最終的な考え

Perls の高度なテキスト処理とメモリ管理、または PostgreSQL 内の Python データ分析ライブラリを利用できることは、非常に強力なツールになります。

複雑なタスクを、そのタスクの処理により適したツールに引き渡すことで、データベースのオーバーヘッドを削減できます。

いつものように、カスタム関数や外部スクリプト言語を使用する場合は、安全に使用できるように予防措置を講じてください。