如何从动态查询中插入具有多列的表？ [英] How to INSERT INTO table having multiple column from dynamic query?

问题描述

类似于> 上一个问题 对于一对一映射，我需要一个针对源和目标中的多个列的解决方案。

仍在使用Postgres 9.4.4，对查询和架构进行了修改，如下所示：

Similar to this previous question for a one-to-one mapping I need a solution for multiple columns in source and destination.
Still working with Postgres 9.4.4, the query and schema are modified and are as below:

假设我有这两个表 Table1 和 Table2 ：

Let's say I have these two tables Table1 and Table2:

Create table Table1(col1 int, col2 varchar(100), col3 varchar(100));
Create table Table2(col1 int, col2 varchar(100), col3 varchar(100));

还有另一个表 Table3 将数据从 Table1 迁移到 Table2 ：

There is another table Table3 storing a formula for migrating data from Table1 to Table2:

CREATE TABLE Table3 (     
  tbl_src character varying(200),
  col_src character varying(500),
  tbl_des character varying(200),
  col_des character varying(100),
  condition character varying(500)
);

Insert into Table3(tbl_src, col_src, tbl_des, col_des, condition)
VALUES ('Table1','col1','Table2','col1', 'WHERE col1>=1')
     , ('Table1','col2','Table2','col2', NULL)
     , ('Table1','col3','Table2','col3', NULL);

如何在动态查询中编译此公式并将其插入目标表？

How to compile this formula in a dynamic query and insert into the destination table?

推荐答案

为多列动态创建语句的基本查询-忽略条件列：

The basic query to build the statement for multiple columns dynamically - ignoring the condition column:

SELECT format(
      'INSERT INTO %I (%s) SELECT %s FROM %I'
     , tbl_des
     , string_agg(quote_ident(col_des), ', ')
     , string_agg(quote_ident(col_src), ', ')
     , tbl_src) AS sql
FROM   table3
WHERE  tbl_des = 'Table2'
AND    tbl_src = 'Table1'
GROUP  BY tbl_des, tbl_src;

结果：

INSERT INTO "Table2" (col1, col2, col3)
SELECT CASE col1, col2, col3 FROM "Table1"

这假定一个 单个 源和一个 单个 目标表。否则事情会变得更加复杂。我添加了 WHERE 条件以明确这一点。我添加到我先前的答案的有关区分大小写的注释仍然适用。

This assumes a single source and a single destination table. Or things get more complicated. I added WHERE conditions to make that clear. The notes on case-sensitivity I added to my previous answer still apply.

以上内容仍然忽略条件。首先，不要在条件列中包含关键字 WHERE 。只是噪音而已，无济于事：

The above still ignores the condition. First of all, don't include the keyword WHERE in the condition column. That's just noise and not helpful:

INSERT INTO Table3(tbl_src, col_src, tbl_des, col_des, condition)
VALUES ('Table1','col1','Table2','col1', 'col1>=1')  -- without WHERE!
     , ('Table1', ...

警告

这种方法本质上是不安全的。条件包含需要按原样连接的表达式，因此您完全可以接受 SQL注入攻击。您需要确保不受信任的用户无法以任何方式写入 table3 来避免这种情况。

Warning

This approach is inherently unsafe. The condition holds expressions that need to be concatenated "as is", so you are completely open to SQL injection attacks. You need to make sure that untrusted users cannot write to table3 in any way to avoid this.

在此基础上，并假设每个条件仅仅适用于其各自的列，我们可以通过将列包装在 CASE 表达式中来解决此问题：

Building on this, and assuming each condition shall apply to its respective column only, we can solve it by wrapping the column in a CASE expression:

SELECT format(
      'INSERT INTO %I (%s) SELECT %s FROM %I'
     , tbl_des
     , string_agg(quote_ident(col_des), ', ')
     , string_agg(
         CASE WHEN condition IS NULL
            THEN quote_ident(col_src)
            ELSE format('CASE WHEN %s THEN %I END'
                 , condition, col_src)  -- condition is unsafe!
         END, ', ')
     , tbl_src) AS sql
FROM   table3
WHERE  tbl_des = 'Table2'
AND    tbl_src = 'Table1'
GROUP  BY tbl_des, tbl_src;

产生以下形式的语句：

INSERT INTO "Table2" (col1, col2, col3)
SELECT CASE WHEN col1>=1 THEN col1 END, col2, col3 FROM "Table1"

或者，就像您在后面的注释中添加的一样，条件可以应用于整个行。从逻辑上讲，这是另一个灰色区域。条件存储在特定的列中，但适用于整行...

Or, like you added in a later comment, conditions can apply to whole rows. Logically, this is another grey area. The condition is stored with a particular column, but applies to the whole row ...

如果可能的话，可以在<$ c $中添加通用条件c> WHERE 子句。

Be that as it may, you can add universal conditions in a WHERE clause.

SELECT format(
      'INSERT INTO %I (%s) SELECT %s FROM %I%s'
     , tbl_des
     , string_agg(quote_ident(col_des), ', ')
     , string_agg(quote_ident(col_src), ', ')
     , tbl_src
     , ' WHERE ' || string_agg(condition, ' AND ')) AS sql
FROM   table3
WHERE  tbl_des = 'Table2'
AND    tbl_src = 'Table1'
GROUP  BY tbl_des, tbl_src;

条件为AND，并且 WHERE 子句仅在有条件的情况下附加-否则，结果NULL值将吞噬表达式'WHERE'||中的添加关键字。 string_agg（condition，'AND'））

Conditions are ANDed, and the WHERE clause is only appended if there are any conditions - else the resulting NULL values swallows the added keyword in the expression ' WHERE ' || string_agg(condition, ' AND '))

在 DO 命令中使用或像我上一个答案中所指示的那样动态执行plpgsql函数：

Use this in a DO command or plpgsql function to execute dynamically like instructed in my previous answer:

• 如何从动态查询中插入表？

• How to INSERT INTO table from dynamic query?

基本plpgsql函数：

Basic plpgsql function:

CREATE OR REPLACE FUNCTION f_test()
  RETURNS void AS
$func$
BEGIN
   EXECUTE (
   SELECT format(
         'INSERT INTO %I (%s) SELECT %s FROM %I%s'
        , tbl_des
        , string_agg(quote_ident(col_des), ', ')
        , string_agg(quote_ident(col_src), ', ')
        , tbl_src
        , ' WHERE ' || string_agg(condition, ' AND ')) AS sql
   FROM   table3
   WHERE  tbl_des = 'Table2'
   AND    tbl_src = 'Table1'
   GROUP  BY tbl_des, tbl_src
   );
END
$func$ LANGUAGE plpgsql;

SQL小提琴。

这篇关于如何从动态查询中插入具有多列的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！