扩展PostgreSQL：复数数据类型

最新推荐文章于 2024-05-10 09:42:15 发布

danpu0978

最新推荐文章于 2024-05-10 09:42:15 发布

阅读量425

点赞数

文章标签： python java 编程语言数据库 mysql

几年前，我讨论了使用PL / java创建用户定义的类型。（ PostgreSQL PL / Java简介，第4部分：用户定义的类型）今天，我继续关注以下评论：我们将在实践中使用标准的C语言PostgreSQL扩展。

什么是扩展名？

PostgreSQL扩展仅是捆绑的SQL语句和可选的本机库。仅SQL扩展名仅包含SQL语句。

与独立脚本相比，扩展具有两个主要优点。首先，将语句作为一个单元加载和卸载。无法更改扩展中定义的任何内容。这是可维护性和安全性的一大胜利。其次，扩展版本。这样就可以随着时间的推移对SQL构件进行干净的更新，这对于用户定义的类型尤其重要，因为简单地删除UDT会导致数据丢失。

PostgreSQL对扩展有明确的支持。请参阅将相关对象打包到扩展中。

加载扩展

CREATE EXTENSION IF NOT EXISTS pg_complex;

卸载扩展

DROP EXTENSION pg_complex;

重要的是要记住，许多（如果不是大多数）托管数据库即服务（DAAS）提供程序（例如Amazon RDS）将不允许您安装任意扩展。您仍然可以通过手动运行创建脚本来加载仅SQL扩展，但是C语言扩展将来可能会引起问题。

什么是用户定义类型（UDT）？

用户定义的类型是数据库的扩展，以支持新的数据类型。 SQL纯粹主义者希望表只包含标准原语。具有结构的数据可以捕获在单独的表和外键中，例如，将“地址”表从“客户”表中拉出。

许多开发人员认识到某些数据比其他数据更紧密地绑定，有用的操作通常需要多个原始值。地理位置（纬度，经度）和密码信息是经典示例。这些对象通常很小，以至于没有单独的表格可以比较它们-比较邮件地址与该地址的经度。

（可能还有其他原因将UDT存储在单独的表中，例如，以提高敏感信息的安全性。）

UDT的另一个用途是为BLOB添加类型安全性。例如，拥有用户定义的函数以返回图像的高度或宽度或PDF文档的页数是合理的。您可以轻松地编写接受BLOB（字节）的函数，但是不能确保传递给该函数的值是适当的类型。定义UDT（例如pdf或jpeg ）为开发人员提供了强大的工具。

总之，在以下情况下应考虑UDT：1）如果缺少任何元素，则对象无意义；或2）否则，该对象将是BLOB，并且您要提供类型安全的存储过程和用户定义的函数。否则，我们应该坚持使用标准原语。

复数？

我将在下面为复杂数字创建UDT。我认为在关联数据库中存储复数的需求尚未得到满足，但是出于教育目的，这是一个不错的选择，因为它需要自定义类型，函数，强制类型转换，运算符和聚合函数。唯一缺少的是订购。

此实现使用PostgreSQL复合类型，结合使用SQL存储过程和C用户定义的函数。复数将显示为（a，b），而不是传统的a + bi，但后者可能需要做更多工作。

SQL定义

这是PostgreSQL扩展，因此我们应该先定义期望在改进的数据库中看到的内容。

定义复杂的UDT

复杂的 UDT由两个字段组成-实数（re）分量和虚数（im）分量。

CREATE TYPE complex AS (re float8, im float8);

下面是一个非常简单的使用说明。

$> CREATE TABLE t (c complex);
CREATE TABLE

-- insert a value. Note that we are inserting '(1,2)', not '1,2'.
$> INSERT INTO t VALUES((1,2));
INSERT 0 1

-- select full UDT
$> SELECT c FROM t;
   c   
-------
 (1,2)

-- select components. Note that we must surround field with parentheses.
$> SELECT (c).re, (c).im FROM t;
 re | im 
----+----
  1 |  2

自动推广浮点到复数

提取复数的实数部分很容易，但是将实数转换为复数仍然很麻烦。如果我们定义CAST，PostgreSQL可以透明地做到这一点。

CREATE OR REPLACE FUNCTION pgx_complex_from_int(int) RETURNS complex AS $$
   SELECT ROW($1::float8, 0)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_from_bigint(bigint) RETURNS complex AS $$
   SELECT ROW($1::float8, 0)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_from_numeric(numeric) RETURNS complex AS $$
   SELECT ROW($1::float8, 0)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_from_numeric(float8) RETURNS complex AS $$
   SELECT ROW($1, 0)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

定义算术运算符

复数是数字，因此我们要为其实现标准算术运算符。由于所有C函数名称必须唯一，因此会被抹黑。 SQL函数名称不必唯一，因为还考虑了函数签名。

CREATE OPERATOR = (
   LEFT_ARG = complex,
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_eq,
   NEGATOR = <>,
   HASHES,
   MERGES
);

CREATE OPERATOR  (
   LEFT_ARG = complex,
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_ne,
   NEGATOR = <>,
   HASHES,
   MERGES
);

CREATE OPERATOR ~= (
   LEFT_ARG = complex,
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_near,
   NEGATOR = <~>
);

CREATE OPERATOR  (
   LEFT_ARG = complex,
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_not_near,
   NEGATOR = <>
);

CREATE OPERATOR - (
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_negate,
   NEGATOR = -
);

CREATE OPERATOR ~ (
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_conjugate,
   NEGATOR = ~
);

-- variants mixing 'complex' and 'numeric' types elided
CREATE OPERATOR + (
   LEFT_ARG = complex,
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_add
);

-- variants mixing 'complex' and 'numeric' types elided
CREATE OPERATOR - (
   LEFT_ARG = complex,
   RIGHT_ARG = complex,
   PROCEDURE = pgxdefine_complex_subtract
);

-- variants mixing 'complex' and 'numeric' types elided
CREATE OPERATOR * (
   LEFT_ARG = complex,
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_multiply
);

-- variants mixing 'complex' and 'numeric' types elided
CREATE OPERATOR / (
   LEFT_ARG = complex,
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_divide
);

定义聚合函数

聚合功能是将智能放入数据库中而不是将其视为美化文件系统的主要方法之一。这些是对值的集合进行操作并返回某种类型的合计值的函数。集合函数可以具有多个参数。

不要以为聚合只能消耗和产生数字数据。考虑一个具有（x，y）对并产生结果的.png图的函数。

聚合函数可以选择支持窗口函数（ http://www.postgresql.org/docs/9.4/static/tutorial-window.html ）。这些是一个相对较新的功能，功能非常强大。实现从概念上讲很简单-我们只需要函数即可在聚合函数的“状态”中添加或删除值-但实际上，操作可能是不可逆的。就是这种情况–如果我们计算“ 1e20 + 1 – 1e20”，结果应为“ 1”，但由于分辨率有限，可能为零。

--
-- UDT that keeps track of sums and sums-of-squares of a collection
-- of complex values. Tracking all three values allows us to compute
-- a wide variety of statistical values.
--
CREATE TYPE complex_accum AS (
   cnt  int,
   sum  complex,
   sofs complex
);

--
-- Calculate sum of a collection of complex values
--
CREATE AGGREGATE sum(complex) (
   sfunc = pg_complex_add,
   stype = complex_accum,
   initcond = '(0, 0)',
   -- msfunc = pg_complex_add,
   -- minvfunc = pg_complex_subtract,
   -- mstype = complex_accum,
   -- minitcond = (0, 0)'
);

--
-- Calculate average of a collection of complex values.
--
CREATE AGGREGATE avg(complex) (
   sfunc = pg_complex_accum,
   stype = complex_accum,
   finalfunc = pg_complex_avg
   -- msfunc = pg_complex_accum,
   -- minvfunc = pg_complex_disaccum,
   -- mstype = complex_accum,
);

（请参阅： http : //www.postgresql.org/docs/9.4/static/xaggr.html 。）

定义用户定义的功能

现在，我们知道了必须实现的功能和签名。在这种情况下，我们可以在纯SQL中完成大多数功能，但选择在C中执行一些功能以演示高级技术。

注意：根据TDD原则，我们仅应实施足以允许测试运行的方式。在这种情况下，函数应返回null。我在这里不做这些，因为功能是如此简单，可以一目了然。任何多行函数都应遵循TDD原则并返回null。

--
-- create functions implemented in C.
--
CREATE OR REPLACE FUNCTION pgx_complex_near(complex, complex)
RETURNS bool
AS 'pg_complex', 'pgx_complex_near'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_divide(complex, complex)
RETURNS complex
AS 'pg_complex', 'pgx_complex_divide'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION norm(complex)
RETURNS complex
AS 'pg_complex', 'pgx_complex_norm'
LANGUAGE C IMMUTABLE STRICT;

--
-- create functions implemented in SQL.
--
CREATE OR REPLACE FUNCTION pgx_complex_from_int(int) RETURNS complex AS $$
   SELECT ROW($1::float8, 0)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_from_bigint(bigint) RETURNS complex AS $$
   SELECT ROW($1::float8, 0)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_from_numeric(numeric) RETURNS complex AS $$
   SELECT ROW($1::float8, 0)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_eq(complex, complex) RETURNS bool AS $$
   SELECT $1.re = $2.re AND $1.im = $2.im;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_ne(complex, complex) RETURNS bool AS $$
   SELECT $1.re <> $2.re OR $1.im <> $2.im;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_not_near(complex, complex) RETURNS bool AS $$
   SELECT NOT pgx_complex_near($1, $2);
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_negate(complex) RETURNS complex AS $$
   SELECT ROW(-$1.re, -$1.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_conjugate(complex) RETURNS complex AS $$
   SELECT ROW($1.re, -$1.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_add(complex, complex) RETURNS complex AS $$
   SELECT ROW($1.re + $2.re, $1.im + $2.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_add_f8(float8, complex) RETURNS complex AS $$
   SELECT ROW($1 + $2.re, $2.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_add_f8(complex, float8) RETURNS complex AS $$
   SELECT ROW($1.re + $2, $1.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_subtract(complex, complex) RETURNS complex AS $$
   SELECT ROW($1.re - $2.re, $1.im - $2.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_subtract_f8(float8, complex) RETURNS complex AS $$
   SELECT ROW($1 - $2.re, -$2.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_subtract_f8(complex, float8) RETURNS complex AS $$
   SELECT ROW($1.re - $2, $1.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_multiply(complex, complex) RETURNS complex AS $$
   SELECT ROW($1.re * $2.re - $1.im * $2.im, $1.re * $2.im + $1.im * $2.re)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_multiply_f8(float8, complex) RETURNS complex AS $$
   SELECT ROW($1 * $2.re, $1 * $2.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_multiply_f8(complex, float8) RETURNS complex AS $$
   SELECT ROW($1.re * $2, $1.im * $2)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION magnitude(complex) RETURNS float8 AS $$
   SELECT sqrt($1.re * $1.re + $1.im * $1.im);
$$ LANGUAGE SQL IMMUTABLE STRICT;

创建骨架扩展

现在，我们准备创建骨架扩展。此扩展遵循测试驱动开发（TDD）的做法–我们希望最少的代码失败量。在这种情况下，这意味着扩展将加载并定义用户定义的函数和类型，但是所有函数和运算符都会立即返回NULL。

最简单的方法是PGXN实用程序。

首先，确保安装了以下软件包：

pgxnclient
PostgreSQL服务器开发9.4
使
Ruby
ruby2.1-dev
海湾合作委员会

（这适用于Ubuntu下的PostgreSQL 9.4。进行相应调整。）

其次，克隆github存储库guedes / pgxn-utils 。

第三，安装这些工具。

$ sudo pgxnclient install pgxn_utils

# verify utilities have been installed.
$ pgxn-utils help
PGXN Utils version: 0.1.4
Commands:
  pgxn-utils bundle [extension_name]  # Bundles the extension in a zip file
  pgxn-utils change [extension_name]  # Changes META's attributes in current extension
  pgxn-utils help [COMMAND]           # Describe available commands or one specific command
  pgxn-utils release filename         # Release an extension to PGXN
  pgxn-utils skeleton extension_name  # Creates an extension skeleton in current directory

第四，使用我们的新实用程序为基于C的PostgreSQL扩展创建框架。

$ pgxn skeleton -m "Bear Giles <bgiles@coyotesong.com>" --template=c pg_complex
      create  pg_complex
      create  pg_complex/pg_complex.control
      create  pg_complex/.gitignore
      create  pg_complex/.template
      create  pg_complex/META.json
      create  pg_complex/Makefile
      create  pg_complex/README.md
      create  pg_complex/doc/pg_complex.md
      create  pg_complex/sql/pg_complex.sql
      create  pg_complex/sql/uninstall_pg_complex.sql
      create  pg_complex/src/pg_complex.c
      create  pg_complex/test/expected/base.out
      create  pg_complex/test/sql/base.sql

第五，编辑META.json，README.md和doc / pg_complex.md文件以描述扩展名。如果您打算将扩展名发布给其他人，这也是将LICENSE文件复制到此目录的好时机。您未来的自我将感谢您提供此文档。

META.json文件使我们可以指定扩展之间的依赖关系。

第六，为每个函数创建一个虚拟实现，该函数立即返回null。

#include "postgres.h"
    #include "fmgr.h"
     
    PG_MODULE_MAGIC;
     
    /**
     * Are two points "near" each other. This function requires reading
     * composite types.
     */
    PG_FUNCTION_INFO_V1(pgx_complex_near);
     
    Datum
    pgx_complex_near(PG_FUNCTION_ARGS) {
        PG_RETURN_NULL();
    }
     
    /**
     * Divide one complex number by another. This function requires reading
     * and returning composite types.
     */
    PG_FUNCTION_INFO_V1(pgx_complex_divide);
     
    Datum
    pgx_complex_divide(PG_FUNCTION_ARGS) {
        PG_RETURN_NULL();
    }
     
    /**
     * Scale a complex number so it on the unit circle. This function requires
     * reading and returning composite types.
     */
    PG_FUNCTION_INFO_V1(pgx_complex_norm);
     
    Datum
    pgx_complex_norm(PG_FUNCTION_ARGS) {
        PG_RETURN_NULL();
    }

我们的问题非常简单，SQL存储过程实现了所需的功能。可以将更复杂的存储过程实现为返回null的plpsql存储过程。

第七，建立系统。

$ make
$ sudo make install

您可能需要在第一次调用“ make install”之前加载扩展。之后没有必要重新加载它。

$ sudo pgxn load ./

Eigth ，运行测试。

标准框架支持回归测试。

$ make installcheck

回归测试在test/sql下运行所有脚本，并验证结果是否与test/expected中的相应文件匹配。实际结果保存在results因此可以轻松编写测试，根据需要修改代码，然后在看到所需的行为后将文件从results复制到test/expected的地方。

您也可以通过pgxn运行测试。

$ pgxn check -d somedb pg_complex

可选的pgTAP（ http://pgtap.org/ ）扩展使我们能够编写xJunit类型的测试。对于开发人员而言，这些测试可能比回归测试更舒适。

有关将pgTAP集成到构建过程中的信息，请参见http://pgtap.org/integration.html和https://gkoenig.wordpress.com/2011/03/04/pgtap-unit-tests-for-postgresql/ 。

第九，在测试框架之外安装和部署扩展。

$ pgxn install --sudo -- pg_complex
$ pgxn load -d somedb --sudo -- pg_complex

您可以使用类似的命令取消部署和卸载扩展程序。

$ pgxn unload --sudo -- pg_complex
$ pgxn uninstall --sudo -- pg_complex

第十，发布扩展名。您可能不想使用框架实现来执行此操作，但这是记录该过程的自然位置。如果我们是PGXN的成员并希望将扩展程序公开，则我们将从捆绑扩展程序开始

$ pgxn bundle

然后将其上传到https://manager.pgxn.org/ 。

测试中

作为优秀的测试驱动开发开发人员，我们从编写测试开始。在我们的例子中，它是直接的SQL，或更确切地说是可以通过psql运行的文件。

典型的测试脚本是：

test / sql / math.sql

\set ECHO None
BEGIN;
\i sql/complex.sql
\set ECHO all

\set c1 (1,2)::complex
\set c2 (1,1)::complex
\set c3 (3,4)::complex
\set c4 (3,8)::complex

SELECT 1::complex AS a, (1::int8)::complex AS b, 1.0::complex AS c;

SELECT (1,2)::complex AS a, -(1,2)::complex AS b, ~(1,2)::complex AS c;

SELECT :c1 + (3,4)::complex AS a, 3 + :c1 AS b, :c1 + 3 AS c;

SELECT :c1 - (3,6)::complex AS a, 3 - :c1 AS b, :c1 - 3 AS c;

SELECT :c1 * (3,5)::complex AS a, 3 * :c1 AS b, :c1 * 3 AS c;
SELECT :c1 * (3,5)::complex AS a, 3.0::double precision * :c1 AS b, :c1 * 3.0 AS c;

SELECT :c4 / :c1  AS a, (:c4 / :c1) * :c1 = :c4 AS b;
SELECT :c4 / (2,0)::complex AS a, (2,0)::complex * (:c4 / (2,0)::complex)  = :c4 AS b;
SELECT :c4 / (0,2)::complex AS a, (0,2)::complex * (:c4 / (0,2)::complex) = :c4 AS b;
SELECT :c4 / 3 AS a, 3 * (:c4 / 3) = :c4 AS b;
SELECT 3 / :c4 AS a, :c4 * (3 / :c4) = 3::complex AS b;

--
-- check magnitude
--
SELECT magnitude(:c1) AS magnitude;
SELECT magnitude(:c2) AS magnitude;
SELECT magnitude(:c3) AS magnitude;

ROLLBACK;

相应的预期结果是：

测试/预期/数学

\set ECHO None
\set c1 (1,2)::complex
\set c2 (1,1)::complex
\set c3 (3,4)::complex
\set c4 (3,8)::complex
SELECT 1::complex AS a, (1::int8)::complex AS b, 1.0::complex AS c;
   a   |   b   |   c   
-------+-------+-------
 (1,0) | (1,0) | (1,0)
(1 row)

SELECT (1,2)::complex AS a, -(1,2)::complex AS b, ~(1,2)::complex AS c;
   a   |    b    |   c    
-------+---------+--------
 (1,2) | (-1,-2) | (1,-2)
(1 row)

SELECT :c1 + (3,4)::complex AS a, 3 + :c1 AS b, :c1 + 3 AS c;
   a   |   b   |   c   
-------+-------+-------
 (4,6) | (4,2) | (4,2)
(1 row)

SELECT :c1 - (3,6)::complex AS a, 3 - :c1 AS b, :c1 - 3 AS c;
    a    |   b    |   c    
---------+--------+--------
 (-2,-4) | (2,-2) | (-2,2)
(1 row)

SELECT :c1 * (3,5)::complex AS a, 3 * :c1 AS b, :c1 * 3 AS c;
    a    |   b   |   c   
---------+-------+-------
 (-7,11) | (3,6) | (3,6)
(1 row)

SELECT :c1 * (3,5)::complex AS a, 3.0::double precision * :c1 AS b, :c1 * 3.0 AS c;
    a    |   b   |   c   
---------+-------+-------
 (-7,11) | (3,6) | (3,6)
(1 row)

SELECT :c4 / :c1  AS a, (:c4 / :c1) * :c1 = :c4 AS b;
     a     | b 
-----------+---
 (3.8,0.4) | t
(1 row)

SELECT :c4 / (2,0)::complex AS a, (2,0)::complex * (:c4 / (2,0)::complex)  = :c4 AS b;
    a    | b 
---------+---
 (1.5,4) | t
(1 row)

SELECT :c4 / (0,2)::complex AS a, (0,2)::complex * (:c4 / (0,2)::complex) = :c4 AS b;
    a     | b 
----------+---
 (4,-1.5) | t
(1 row)

SELECT :c4 / 3 AS a, 3 * (:c4 / 3) = :c4 AS b;
          a           | b 
----------------------+---
 (1,2.66666666666667) | t
(1 row)

SELECT 3 / :c4 AS a, :c4 * (3 / :c4) = 3::complex AS b;
                   a                    | b 
----------------------------------------+---
 (0.123287671232877,-0.328767123287671) | t
(1 row)

--
-- check magnitude
--
SELECT magnitude(:c1) AS magnitude;
    magnitude     
------------------
 2.23606797749979
(1 row)

SELECT magnitude(:c2) AS magnitude;
    magnitude    
-----------------
 1.4142135623731
(1 row)

SELECT magnitude(:c3) AS magnitude;
 magnitude 
-----------
         5
(1 row)

ROLLBACK;

通过运行一次测试，从results / math.out获取结果，然后编辑该文件以显示预期结果，来创建“ expected”文件可能是最容易的。在纯TDD实现中，所有测试最初应返回null，但我们已经定义了上面的许多功能。

实作

要添加三个SQL语言功能。细节很重要–没有值的总和定义为0 + 0i，但是没有值的平均值是不确定的（空），而不是任何特定值。

--
-- accumulator function is similar to float8_accum. Question: should the result
-- be the product of p * p or the product of p * ~p ?
--
CREATE OR REPLACE FUNCTION pgx_complex_accum(complex_accum, complex) RETURNS complex_accum AS $$
   SELECT CASE WHEN $1 IS NULL THEN 1 ELSE $1.cnt + 1 END,
          CASE WHEN $1 IS NULL THEN $2 ELSE $1.sum + $2 END,
          CASE WHEN $1 IS NULL THEN $2 * ~$2 ELSE $1.sofs + $2 * ~$2 END;
$$ LANGUAGE SQL;

--
-- disaccumulator(?) function is similar to pgx_complex_accum. It is required in order
-- to implement windowing functions.
--
CREATE OR REPLACE FUNCTION pgx_complex_disaccum(complex_accum, complex) RETURNS complex_accum AS $$
   SELECT pgx_complex_accum($1, -$2);
$$ LANGUAGE SQL;

--
-- average function returns quotient of sum over count.
--
CREATE OR REPLACE FUNCTION pgx_complex_avg(complex_accum) RETURNS complex AS $$
   SELECT CASE WHEN $1 IS NULL THEN NULL
               WHEN $1.cnt = 0 THEN (0,0)::complex
               ELSE $1.sum / $1.cnt END;
$$ LANGUAGE SQL;

第一个C语言函数演示了如何读取复合值并返回原语。在这种情况下，我们按名称获取“ re”和“ im”组件。

PG_MODULE_MAGIC;

/**
 * Test complex numbers for proximity. This avoids the problems with testing floats
 * and doubles but does not guarantee absolute equality.
 */
PG_FUNCTION_INFO_V1(pgx_complex_near);

Datum
pgx_complex_near(PG_FUNCTION_ARGS) {
    double re[2];
    double im[2];
    double p, q;
    int i;

    // unwrap values.    
    for (i = 0; i < 2; i++) {
        HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(i);
        bool isnull[2];

        Datum dr = GetAttributeByName(t, "re", &isnull[0]);
        Datum di = GetAttributeByName(t, "im", &isnull[1]);

        // STRICT prevents the 'complex' value from being null but does
        // not prevent its components from being null.        
        if (isnull[0] || isnull[1]) {
            PG_RETURN_NULL();
        }
        
        re[i] = DatumGetFloat8(dr);
        im[i] = DatumGetFloat8(di);
    }

    // compute distance between points, distance of points from origin.
    p = hypot(re[0] - re[1], im[0] - im[1]);
    q = hypot(re[0], im[0]) + hypot(re[1], im[1]);
    
    if (q == 0) {
        PG_RETURN_BOOL(1);
    }
    
    // we consider the points 'near' each other if the distance between them is small
    // relative to the size of them. 
    PG_RETURN_BOOL(p / q < 1e-8); 
}

第二种情况返回一个复合值。有两种返回复合值的方法。这是较旧的方法，需要更多的工作。较新的方法要求所有内容都以字符串形式返回-带有原始值的开销不大，但是封送和取消封送用户定义的类型可能会很昂贵。

/**
 * Divide complex number by another. We do this by multiplying nominator and denominator
 * by the conjugate of the denominator. The denominator then becomes the scalar square of
 * the magnitude of the number.
 */
PG_FUNCTION_INFO_V1(pgx_complex_divide);

Datum
pgx_complex_divide(PG_FUNCTION_ARGS) {
    TupleDesc tupdesc;
    HeapTuple tuple;
    double re[2];
    double im[2];
    int i;
    double q;
    Datum datum[2];
    bool isnull[2];
 
    // build a tuple descriptor for our result type 
    if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
        ereport(ERROR,
                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                 errmsg("function returning record called in context "
                        "that cannot accept type record")));

    // unwrap values.    
    for (i = 0; i < 2; i++) {
        HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(i);
        bool isnull[2];
        Datum dr, di;

        dr = GetAttributeByName(t, "re", &isnull[0]);
        di = GetAttributeByName(t, "im", &isnull[1]);

        // STRICT prevents the 'complex' value from being null but does
        // not prevent its components from being null.        
        if (isnull[0] || isnull[1]) {
            PG_RETURN_NULL();
        }
        
        re[i] = DatumGetFloat8(dr);
        im[i] = DatumGetFloat8(di);
    }

    // the denominator is the square of the magnitude of the divisor.
    q = re[1] * re[1] + im[1] * im[1];
    
    // should I throw error instead of returning null?
    if (q == 0.0) {
        PG_RETURN_NULL();
    }

    datum[0] = Float8GetDatum((re[0] * re[1] + im[0] * im[1]) / q);
    datum[1] = Float8GetDatum((im[0] * re[1] - im[1] * re[0]) / q);

    BlessTupleDesc(tupdesc);
    tuple = heap_form_tuple(tupdesc, datum, isnull);
 
    PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
}

第三个示例还使用并产生一个复合值。

/**
 * Calculate the norm of a complex number. This is the complex number on the unit
 * circle so that magnitude(norm(x)) = 1 and magnitude(x) * norm(x) = x.
 */
PG_FUNCTION_INFO_V1(pgx_complex_norm);

Datum
pgx_complex_norm(PG_FUNCTION_ARGS) {
    HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(0);
    TupleDesc tupdesc;
    HeapTuple tuple;
    double re;
    double im;
    bool isnull[2];
    Datum datum[2];
    double m;
 
    // build a tuple descriptor for our result type 
    if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
        ereport(ERROR,
                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                 errmsg("function returning record called in context "
                        "that cannot accept type record")));
        
    // unwrap values.    
    datum[0] = GetAttributeByName(t, "re", &isnull[0]);
    datum[1] = GetAttributeByName(t, "im", &isnull[1]);

    // STRICT prevents the 'complex' value from being null but does
    // not prevent its components from being null.        
    if (isnull[0] || isnull[1]) {
        PG_RETURN_NULL();
    }
        
    re = DatumGetFloat8(datum[0]);
    im = DatumGetFloat8(datum[1]);

    m = hypot(re, im);
   
    // should I throw error instead of returning null?
    if (m == 0.0) {
        PG_RETURN_NULL();
    } 

    datum[0] = Float8GetDatum(re / m);
    datum[1] = Float8GetDatum(im / m);

    BlessTupleDesc(tupdesc);
    tuple = heap_form_tuple(tupdesc, datum, isnull);
 
    PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
}

包起来

这包装了复数扩展名。大多数扩展将简单得多，但之所以选择此问题正是因为它展示了您可以集成扩展的深度。

源代码

http://github.com/beargiles/pg-complex
http://pgxn.org/dist/complex
其他资源
- PXGN博客
- PGXN方法
- PGXN META.json规范
- PGXN客户端文档
- PGXN用法
- pgTab （测试框架）

翻译自: https://www.javacodegeeks.com/2015/08/extending-postgresql-complex-number-data-type.html

danpu0978

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
扩展PostgreSQL：复数数据类型

几年前，我讨论了使用PL / java创建用户定义的类型。（ PostgreSQL PL / Java简介，第4部分：用户定义的类型）今天，我继续关注以下评论：我们将在实践中使用标准的C语言PostgreSQL扩展。什么是扩展名？ PostgreSQL扩展仅是捆绑的SQL语句和可选的本机库。仅SQL扩展名仅包含SQL语句。与独立脚本相比，扩展具有两个主要优点。首先，将语句...
复制链接

扫一扫