如何从动态查询中插入具有多列的表？

Question

与先前 的一对一映射问题 类似，我需要一个针对源和目标中多列的解决方案。
仍可与Postgres 9.4.4一起使用，对查询和架构进行了修改，如下所示：

假设我有这两个表Table1和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：

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);

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

Answer 1

动态查询多列语句的基本查询-忽略该condition列：

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条件以使其清楚。

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

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

警告

这种方法本质上是不安全的。该condition持有表达式需要被连接起来“为是”，那么你是完全开放的 SQL注入攻击
。您需要确保不受信任的用户无法以table3任何方式写信来避免这种情况。

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

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"

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

尽管如此，您可以在WHERE子句中添加通用条件。

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 '))

在DO命令或plpgsql函数中使用此命令可以动态执行，就像我之前的答案中所指示的那样

plpgsql基本功能：

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;