NSDT工具推荐： Three.js AI纹理开发包 - YOLO合成数据生成器 - GLTF/GLB在线编辑 - 3D模型格式在线转换 - 可编程3D场景编辑器 - REVIT导出3D模型插件 - 3D模型语义搜索引擎 - AI模型在线查看 - Three.js虚拟轴心开发包 - 3D模型在线减面 - STL模型在线切割 - 3D道路快速建模

PostgreSQL 是一个功能强大的开源关系数据库管理系统。 它使用附加功能扩展了 SQL 语言。 DBMS 的定义不仅在于其性能和开箱即用的功能，还在于其支持定制/附加用户特定功能的能力。 其中一些功能可能采用数据库结构或模块的形式，如存储过程或函数，但它们的范围通常仅限于 DBMS 公开的功能。 例如，将如何编写驻留在你的 DBMS 中的自定义查询分析应用程序？

为了支持这些选项，PostgreSQL 提供了一个可插入的架构，允许你安装扩展（extension）。 扩展可能包括配置（控制）文件、SQL 文件的组合和动态可加载的库。

这意味着你可以根据扩展的定义指南编写自己的代码并将其插入 PostgreSQL 实例，而无需更改实际的 PostgreSQL 代码树。 根据定义，扩展扩展了 PostgreSQL 的功能，但不仅如此，它还使你能够与外部实体进行交互。 这些外部实体可以是其他数据库管理系统，如 ClickHouse、Mongo 或 HDF（通常称为外部数据包装器），或其他解释器或编译器（因此允许我们用另一种语言编写数据库功能，如 Java、Python、Perl 或 TCL， ETC。）。 扩展的另一个潜在用例是代码混淆，它允许你保护你的超级机密代码免遭窥探。

1、开发自己的PostgreSQL扩展

要构建自己的扩展，不需要完整的 PostgreSQL 代码库。 可以使用已安装的 PostgreSQL 构建和安装扩展（它可能需要您安装 devel RPM 或 Debian 软件包）。 有关扩展的详细信息，请参阅 PostgreSQL 的官方文档。 在 PostgreSQL 源代码的 contrib 目录中有许多可用于不同功能的扩展。 除了 contrib 目录之外，人们还在编写可在 Internet 上轻松获得但目前不属于 PostgreSQL 源代码树的扩展。 pg_stat_statements、PL/pgSQL 和 PostGIS 是最著名或使用最广泛的扩展示例。

一般可用的 PostgreSQL 扩展可以分为四个主要类别：

  • 添加对新语言扩展的支持（PL/pgSQL、PL/Python 和 PL/Java）
  • 可以在其中引入新的数据类型扩展（（Hstore、cube 和 hstore）
  • 杂项扩展（contrib文件夹有很多杂项扩展）
  • 外部数据包装器扩展（postgres_fdw、mysqldb_fdw、clickhousedb_fdw）

构建扩展需要四种基本文件类型：

  • Makefile：它使用 PGXS PostgreSQL 的扩展构建基础设施。
  • 控制文件：携带有关扩展名的信息。
  • SQL 文件：如果扩展有任何 SQL 代码，它可能驻留在表单 SQL 文件中（可选）
  • C 代码：我们要构建的共享对象（可选）。

2、PostgreSQL扩展的makefile

要编译 C 代码，我们需要一个 makefile。 这是一个非常简单的 makefile，除了“PGXS”，它是 PostgreSQL 用于创建扩展的基础 makefile。 “PGXS”的包含是通过调用带有“–pgxs”标志的 pg_config 二进制文件来完成的。 该文件的详细信息可以在 GitHub上找到。

这是一个示例 makefile，可用于编译 C 代码。

EXTENSION = log
MODULE_big = log
DATA = log--0.0.1.sql
OBJS = log.o 
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

3、PostgreSQL扩展的控制文件

此文件必须命名为  [EXTENSION NAME].control。 控制文件可以包含许多选项，这些选项可以在官方文档 中找到。 但在这个例子中，我使用了一些基本选项。

  • comments：关于扩展的注解。
  • default_version：这是扩展的 SQL 版本。 SQL 文件的名称在文件名中包含此信息。
  • relocatable：是否可以将包含的对象移动到不同的架构（schema）中。
  • module_pathname：该信息被替换为实际的lib文件路径。
comment = 'PostgreSQL Utility Command Logger
'default_version = '0.0.1'
relocatable = true
module_pathname = '$libdir/log'

可以使用 psql 命令 \dx psql 查看文件的内容。

postgres=# \dx log      
List of installed extensions Name   | Version | Schema |       Description 
------------------------------------+---------+--------+---------------------------------- 
log                                 | 0.0.1   | public | PostgreSQL Utility Command Logger

4、PostgreSQL扩展的 SQL 文件

这是一个映射文件，我用它来将 PostgreSQL 函数映射到相应的 C 函数。 无论何时调用 SQL 函数，都会调用相应的 C 函数。 文件名必须是 [EXTENSION NAME]–[default-version].sql。 这与控制文件中定义的 default_version 相同。

CREATE FUNCTION pg_all_queries(OUT query TEXT, pid OUT TEXT)
RETURNS SETOF RECORDAS 'MODULE_PATHNAME', 
'pg_all_queries'
LANGUAGE C STRICT VOLATILE;

5、PostgreSQL扩展的 C 代码

可以用 C 代码编写三种函数。

第一个是使用在扩展的 SQL 文件中编写的 SQL 函数调用 C 代码函数的地方。

第二种类型的函数是回调。 通过分配函数的指针来注册该回调。 这里不需要 SQL 函数。 当特定事件发生时，会自动调用此函数。

第三类函数是自动调用的，甚至不需要注册。 这些函数在诸如扩展加载/卸载时间等事件上被调用。

这是包含 C 代码定义的 C 文件。 C 文件的名称或数量没有限制。

#include "postgres.h"
/* OS Includes */
/* PostgreSQL Includes */
PG_MODULE_MAGIC;
void _PG_init(void);
void _PG_fini(void);
Datum pg_all_queries(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(pg_all_queries);
static void process_utility(PlannedStmt *pstmt, const char *queryString,ProcessUtilityContext context,ParamListInfo params,QueryEnvironment *queryEnv,DestReceiver *dest,       char *completionTag);

你需要为扩展包含 postgres.h。 可以根据需要包括其他 PostgreSQL。 PG_MODULE_MAGIC 是一个宏，你需要将其包含在 C 文件中以进行扩展。 _PG_init 和 _PG_fini 分别是加载或卸载扩展时调用的函数。

下面是一个扩展的加载和卸载函数的示例。

void _PG_init(void)
{
    /* ... C code here at time of extension loading ... */
    ProcessUtility_hook = process_utility;
}

Void _PG_fini(void)
{
    /* ... C code here at time of extension unloading ... */
}

下面是一个回调函数的示例，可以在调用实用程序语句时调用该回调函数，例如 任何 DDL 语句。 “queryString”变量包含实际的查询文本。

static void process_utility(PlannedStmt *pstmt, 
                           const char *queryString,
                           ProcessUtilityContext context,
                           ParamListInfo params,
                           QueryEnvironment *queryEnv,DestReceiver *dest,
                           char *completionTag)
{
    /* ... C code here ... */
    standard_ProcessUtility(pstmt,   
                            queryString,   
                            context,   
                            params,   
                            queryEnv,   
                            dest, 
                            completionTag);
    /* ... C code here ... */
}

最后是一个通过用户定义的 SQL 函数调用的 C 函数示例。 这会在内部调用共享对象中包含的 C 函数。

Datum pg_all_queries(PG_FUNCTION_ARGS)
{
    /* ... C code here ... */
    tupstore = tuplestore_begin_heap(true, false, work_mem);
    /* ... C code here ... */     
    values[0] = CStringGetTextDatum(query);
    values[1] = CStringGetTextDatum(pid);
    /* ... C code here ... */
    tuplestore_donestoring(tupstore);
    return (Datum) 0;
}

6、PostgrepSQL扩展的编译安装

编译前，如果系统路径中没有pg_config，需要设置PostgreSQL的bin目录的PATH：

export PATH=/usr/local/pgsql/bin:$PATH

构建PostgrepSQL扩展：

make USE_PGXS=1

安装PostgrepSQL扩展：

make USE_PGXS=1 install

7、PostgreSQL扩展的输出

我们现在可以通过一个简单的 SQL 查询来使用我们的扩展。 以下是直接来自用 C 编程语言编写的扩展的输出。

postgres=# select * from pg_all_queries();          
query                      | pid 
--------------------------+|------ 
create table foo(a int);  +| 8196 
create table bar(a int);  +| 8196 
drop table foo;           +| 8196 
(3 rows)

8、结束语

我希望这个示例可以作为你创建更多有用的PostgreSQL扩展的起点，这些扩展不仅可以帮助您和您的公司，还可以让你有机会分享和帮助 PostgreSQL 社区发展。

完整的示例可以在 Github上找到。

原文链接：Writing PostgreSQL Extensions is Fun – C Language

BimAnt翻译整理，转载请标明出处