在 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中文网其他相关文章！
