如何从动态查询插入表? [英] How to INSERT INTO table from dynamic query?

问题描述

我的Postgres版本是:

"PostgreSQL 9.4.4, compiled by Visual C++ build 1800, 32-bit"

假设我有两个表Table1和Table2，它们分别具有列col1和col2.

CREATE TABLE Table1(col1 int);
CREATE TABLE Table2(col2 int);

还有另一个表Table3，其中存储用于将数据从Table1迁移到Table2的公式:

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

INSERT INTO Table3 (tbl_src, col_src, tbl_des, col_des, condition)
SELECT 'Table1','col1','Table2','col2',NULL

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

解决方案

动态查询命令的基本查询:

SELECT format('INSERT INTO %I (%I) SELECT %I FROM %I'
            , tbl_des, col_des, col_src, tbl_src) As sql
FROM   table3;

这将产生一个查询，例如:

INSERT INTO "Table2" (col2) SELECT col1 FROM "Table1"

请注意引用的大写字母拼写.与SQL命令中不带引号的标识符自动折叠为小写字母不同，表中的字符串现在区分大小写！

• PostgreSQL列名是否区分大小写?

我建议您从不双引号标识符，并仅使用合法的小写名称.

要自动化:

DO
$$BEGIN
   EXECUTE (
      SELECT format('INSERT INTO %I (%I) SELECT %I FROM %I'
                  , tbl_des, col_des, col_src, tbl_src) As sql
      FROM   table3
      -- WHERE table3_id = 123  -- select only *one* row!
      );
END$$;

您需要了解阅读手册.. >

您也可以将其包装到plpgsql函数中，并传递其他参数:

• 表名作为PostgreSQL函数参数

My version of Postgres is:

"PostgreSQL 9.4.4, compiled by Visual C++ build 1800, 32-bit"

Let's say I have two tables Table1 and Table2, which are having column col1 and col2 respectively.

CREATE TABLE Table1(col1 int);
CREATE TABLE Table2(col2 int);

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

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

INSERT INTO Table3 (tbl_src, col_src, tbl_des, col_des, condition)
SELECT 'Table1','col1','Table2','col2',NULL

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

解决方案

The basic query to build the command dynamically:

SELECT format('INSERT INTO %I (%I) SELECT %I FROM %I'
            , tbl_des, col_des, col_src, tbl_src) As sql
FROM   table3;

This produces a query like:

INSERT INTO "Table2" (col2) SELECT col1 FROM "Table1"

Note the quoted upper-case spelling. Unlike in SQL commands, where unquoted identifiers are folded to lower-case automatically, the strings in your table are now case-sensitive!

• Are PostgreSQL column names case-sensitive?

I suggest you never double-quote identifiers and use legal, lower-case names exclusively.

To automate:

DO
$$BEGIN
   EXECUTE (
      SELECT format('INSERT INTO %I (%I) SELECT %I FROM %I'
                  , tbl_des, col_des, col_src, tbl_src) As sql
      FROM   table3
      -- WHERE table3_id = 123  -- select only *one* row!
      );
END$$;

You need to understand the format() function. Read the manual.

You can wrap this into a plpgsql function as well and pass additional parameters:

• Table name as a PostgreSQL function parameter

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