python - 要快速从 Python 数组转换为 PostgreSQL？

Question

这是一个后续问题:How to cast to int array in PostgreSQL?
我在考虑如何将 Python 的 array-array of signed integer 数据类型快速转换为 PostgreSQL 的 int:

import numpy as np; # use any data format of Python here
event = np.array([[1,2],[3,4]]);

如果手动，[] 应替换为 {} 并由 ' 包围。在 PostgreSQL 中，接受以下作为数据类型的语法

...
FOR EACH ROW EXECUTE PROCEDURE insaft_function('{{1,2},{3,4}}'); 

@JohnMee的建议

str(event).replace('[','{').replace(']','}').replace('\n ',',')

@ErwinBrandstetter 的建议

坚持使用有符号整数，因为 SQL 标准支持它。映射到 int，所以就在 PostgreSQL 端:

TG_ARGV::int[]

我想坚持这个 Erwin 的建议。

@ErwinBrandstetter 回答的简单版本的测试运行

我必须通过从函数中删除表名来简化他的回答以使其足够集中在这里，因此只保留一个初始表的触发器 measurements:

CREATE OR REPLACE FUNCTION f_create_my_trigger(_arg0 text)
  RETURNS void AS
$func$
BEGIN
EXECUTE format($$
    DROP TRIGGER IF EXISTS insaft_ids ON measurements;
    CREATE TRIGGER insaft_ids
    AFTER INSERT ON measurements
    FOR EACH ROW EXECUTE PROCEDURE insaft_function(%1$L)$$
    , _arg0
);

END
$func$ LANGUAGE plpgsql;

然后我跑:

sudo -u postgres psql detector -c "SELECT f_create_my_trigger('[[1,2],[3,4]]');"

但得到空输出:

 f_create_my_trigger 
---------------------

(1 row)

如何在 Python 中为 PostgreSQL 9.4 映射到 int？

Answer 1

设置

您想(重复？)使用与 my related answer on dba.SE 中概述的相同触发器函数创建触发器.您需要将值传递给触发器函数以创建具有多个 列值的多个 行，因此是二维数组。 (但我们可以使用任何明确定义的字符串!)

将值传递给 PL/pgSQL 触发器函数(触发行的列值除外)的唯一方法是 text 参数，它们可以在函数内部作为 0-based array of text in the special array variable TG_ARGV[] 访问.您可以传递数量可变的参数，但我们之前讨论了表示二维数组的单个字符串文字。

输入来自具有有符号整数 数字的二维 Python 数组，符合 Postgres 类型 integer。使用 Postgres 类型 bigint 来覆盖无符号整数，as commented .

Python 中的文本表示如下所示:

[[1,2],[3,4]]

Postgres 数组文字的语法:

{{1,2},{3,4}}

并且您想自动化该过程。

全自动化

您可以在客户端中为 CREATE TRIGGER 语句连接字符串，或者您可以将逻辑保留在服务器端函数中并只传递参数。

演示一个示例函数，该函数采用表名和传递给触发器函数的字符串。触发函数insaft_function() is defined in your previous question on dba.SE .

CREATE OR REPLACE FUNCTION f_create_my_trigger(_tbl regclass, _arg0 text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format($$
      DROP TRIGGER IF EXISTS insaft_%1$s_ids ON %1$s;
      CREATE TRIGGER insaft_%1$s_ids
      AFTER INSERT ON %1$s
      FOR EACH ROW EXECUTE PROCEDURE insaft_function(%2$L)$$
                , _tbl
                , translate(_arg0, '[]', '{}')
      );
END 
$func$;

调用:

SELECT f_create_my_trigger('measurements', '[[1,2],[3,4]]');

或者:

SELECT f_create_my_trigger('some_other_table', '{{5,6},{7,8}}');

db<> fiddle here
<子>旧sqlfiddle

现在您可以传递 [[1,2],[3,4]](带方括号)或 {{1,2},{3,4}} (带花括号)。两者的工作原理相同。 translate(_arg0, '[]', '{}' 将第一种形式转换为第二种形式。

如果存在同名触发器，此函数会在创建新触发器之前删除它。您可能想要删除或保留此行:

DROP TRIGGER IF EXISTS insaft_%1$s_ids ON %1$s;

这以调用数据库角色的权限运行。如果需要，您可以让它以 super 用户(或任何其他)权限运行。见:

• Is there a way to disable updates/deletes but still allow triggers to perform them?

有很多方法可以实现这一点。这取决于具体要求。

解释 format()

format()和数据类型 regclass 有助于安全地连接 DDL 命令并使 SQL 注入(inject)成为不可能。见:

• Table name as a PostgreSQL function parameter

第一个参数是“格式字符串”，后面是要嵌入到字符串中的参数。我用 dollar-quoting ，这对于示例来说并不是绝对必要的，但通常是连接包含单引号的长字符串的好主意:$$DROP TRIGGER ... $$

format() 是根据 C 函数 sprintf 建模的。 %1$s 是 format() 的格式说明符功能。这意味着格式字符串之后的第一个 (1$) 参数被插入为 unquoted string (%s)，因此: %1$s。 format 的第一个参数是示例中的 _tbl - regclass 参数自动呈现为合法标识符，必要时加双引号，因此 format() 不必做更多。因此只有 %s，而不是 %I(标识符)。阅读上面的链接答案了解详情。
另一个正在使用的格式说明符是 %2$L:第二个参数作为带引号的字符串文字。

如果您是 format() 的新手，请尝试这些简单的示例以理解:

SELECT format('input -->|%s|<-- here', '[1,2]')
     , format('input -->|%s|<-- here', translate('[1,2]', '[]', '{}'))
     , format('input -->|%L|<-- here', translate('[1,2]', '[]', '{}'))
     , format('input -->|%I|<-- here', translate('[1,2]', '[]', '{}'));

和read the manual .