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

This is a follow-up question to: How to cast to int array in PostgreSQL?
I am thinking how to convert Python's datatype of array-array of signed integer into to int of PostgreSQL quickly:

这是一个后续问题：如何在PostgreSQL中转换为int数组？我正在考虑如何快速将Python的有符号整数数组的数据类型转换为PostgreSQL的int：

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

where [] should be replaced by {} and surrounded by ' if manually. In PostgreSQL, the following is accepted as the syntax of the datatype

其中[]应由{}替换，并由'if手动包围。在PostgreSQL中，以下内容被接受为数据类型的语法

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

@JohnMee's suggestion

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

@ErwinBrandstetter's suggestion

Stick to signed integers because it is supported by SQL standard. Map to int, so just in PostgreSQL side:

坚持使用有符号整数，因为SQL标准支持它。映射到int，所以只是在PostgreSQL方面：

TG_ARGV::int[]

I want to stick to this Erwin's suggestion.

我想坚持这个欧文的建议。

Test run of simpler version of @ErwinBrandstetter's answer

I have to simplify his answer to keep it enough focused here by removing the table-name from function so just keeping the trigger for one initial table 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;

And I run:

我跑：

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

But get empty output:

但得到空输出：

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

(1 row)

How can you map to int for PostgreSQL 9.4 in Python?

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

1 个解决方案

#1

Summing up the situation

You want to create triggers (repeatedly?) using the same trigger function like outlined in my previous answer on dba.SE. You need to pass values to the trigger function to create multiple rows with multiple column values, hence the two-dimensional array. (But we can really work with any clearly defined string!)

你想使用相同的触发函数创建触发器（重复吗？），如我之前在dba.SE上的回答中所述。您需要将值传递给触发器函数以创建具有多个列值的多个行，因此创建二维数组。 （但我们可以使用任何明确定义的字符串！）

The only way to pass values to a PL/pgSQL trigger function are text parameters, which are accessible inside the function as 0-based array of text TG_ARGV[]. You could pass a variable number of parameters, but we discussed a single string literal representing your 2-dimenstional array earlier.

将值传递给PL / pgSQL触发器函数的唯一方法是文本参数，可以在函数内部访问基于0的文本TG_ARGV []数组。您可以传递可变数量的参数，但我们之前讨论过代表您的2维数组的单个字符串文字。

The values you want to pass come from a 2-dimenstional Python array with signed integer numbers, so they will fit into the Postgres integer type. You would have to use the Postgres type bigint for unsigned integer numbers to be sure, like I commented.

您想要传递的值来自具有有符号整数的2维Python数组，因此它们将适合Postgres整数类型。你必须使用Postgres类型的bigint来表示无符号整数，就像我评论的那样。

The text representation in Python looks like this:

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

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

The syntax for a Postgres array literal looks like this:

Postgres数组文字的语法如下所示：

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

And it seems like you want to automate the process.

似乎你想要自动化这个过程。

Full automation

You can concatenate the string for the CREATE TRIGGER statement in your client or you can persist the logic in a server-side function and just pass parameters.

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

Demonstrating an example function where we pass the table name and the string that's used as parameter to the trigger function. The trigger function insaft_function() is defined in your previous question on dba.SE.

演示一个示例函数，我们将表名和用作参数的字符串传递给触发器函数。触发器函数insaft_function（）在您之前关于dba.SE的问题中定义。

CREATE OR REPLACE FUNCTION f_create_my_trigger(_tbl regclass, _arg0 text)
  RETURNS void 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$ LANGUAGE plpgsql;

Call:

呼叫：

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

Or:

要么：

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

SQL Fiddle demo.

SQL小提琴演示。

Now you can pass either [[1,2],[3,4]] (with square brackets) or {{1,2},{3,4}} (with curly braces). Both work the same. translate(_arg0, '[]', '{}' transforms the first into the second form.

现在你可以传递[[1,2]，[3,4]]（带方括号）或{{1,2}，{3,4}}（带花括号）。两者都是一样的。 translate（_arg0，'[]'，'{}'将第一个转换为第二个形式。

This function automatically drops a trigger of the same name if it exists, before creating the new one. You may want to drop or keep this line:

在创建新触发器之前，此函数会自动删除相同名称的触发器（如果存在）。您可能想要删除或保留此行：

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

This runs with the privileges of the calling DB role. You could make it run with superuser (or any other) privileges if need be.

它以调用DB角色的权限运行。如果需要，您可以使用超级用户（或任何其他）权限运行它。

There are many other ways to achieve this. It all depends on exact requirements.

还有很多其他方法可以实现这一目标。这完全取决于具体要求。

Explaining format()

format() and the data type regclass help to safely concatenate the DDL command while making SQL injection impossible. Details:

format（）和数据类型regclass有助于安全地连接DDL命令，同时使SQL注入无法进行。细节：

• Table name as a PostgreSQL function parameter
• 表名作为PostgreSQL函数参数

The first argument is the "format string", followed by arguments to be imbedded in the string. I use dollar-quoting, which is not strictly necessary for the example, but generally a good idea for concatenating long strings that might contain literal quotes: $$DROP TRIGGER ... $$

第一个参数是“格式字符串”，后面跟着要嵌入字符串的参数。我使用美元引用，这对于示例来说并不是绝对必要的，但通常是连接可能包含文字引号的长字符串的好主意：$$ DROP TRIGGER ... $$

format() is modeled along the C function sprintf. %1$s is a format specifier of the format() function. It means that the first (1$) argument after the format string is inserted as unquoted string (%s), hence: %1$s. The first argument to format is _tbl in the example - the regclass parameter is rendered as legal identifier automatically, double-quoted if necessary, so format() does not have to do more. Hence just %s, not %I (identifier). Read the linked answer above for details.
The other format specifier in use is %2$L: Second argument as quoted string literal.

format（）是沿着C函数sprintf建模的。 ％1 $ s是format（）函数的格式说明符。这意味着格式字符串之后的第一个（1 $）参数作为不带引号的字符串（％s）插入，因此：％1 $ s。格式的第一个参数是示例中的_tbl - regclass参数自动呈现为合法标识符，必要时双引号，因此format（）不必执行更多操作。因此只是％s，而不是％I（标识符）。阅读上面的链接答案了解详情。使用的另一个格式说明符是％2 $ L：第二个参数作为带引号的字符串文字。

If you are new to format(), play with these simple examples to understand:

如果您不熟悉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]', '[]', '{}'));

And read the manual.

并阅读手册。