在 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 版本。
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 或 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"}
使用自訂函數或外部腳本語言時，需要考慮其他安全注意事項。在可用性和安全性之間取得適當的平衡可能是一種兼顧的行為。
在上一個函數中，建立函數語句中新增了 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 資料分析函式庫可以成為一個非常強大的工具。
將複雜的任務交給更適合處理任務的工具可以減少資料庫的開銷。
一如既往，在使用自訂函數和外部腳本語言時，請採取預防措施以確保安全使用。
以上是在 PostgreSQL 中建立自訂函數的詳細內容。更多資訊請關注PHP中文網其他相關文章！
