在 PostgreSQL 中创建自定义函数

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