INSERT INTO查询的动态表名称 [英] Dynamic table name for INSERT INTO query

问题描述

我试图弄清楚如何使用表名称和源列名称作为参数编写INSERT INTO查询.

I am trying to figure out how to write an INSERT INTO query with table name and column name of the source as parameter.

对于初学者来说，我只是想对源表名称进行参数化.我写了以下查询.现在，我直接声明并分配变量tablename的值，但是在实际示例中，它将来自其他某些来源/列表.目标表只有一列.

For starters I was just trying to parametrize the source table name. I have written the following query. For now I am declaring and assigning the value of the variable tablename directly, but in actual example it would come from some other source/list. The target table has only one column.

CREATE OR REPLACE FUNCTION foo()
RETURNS void AS
$$
DECLARE
    tablename text;
BEGIN
   tablename := 'Table_1';
   EXECUTE 'INSERT INTO "Schemaname"."targettable"
   SELECT "Col_A"
   FROM "schemaname".'
   ||quote_ident(tablename);
END
$$ LANGUAGE PLPGSQL;

尽管查询运行没有任何错误，但目标表上没有反映任何更改.在运行查询时，我得到以下输出.

Although the query runs without any error no changes are reflected at the target table. On running the query I get the following output.

查询正常，受影响的行数为0(执行时间:296毫秒；总时间:296毫秒)

Query OK, 0 rows affected (execution time: 296 ms; total time: 296 ms)

我希望更改反映在目标表上.我不知道如何解决问题.

I want the changes to be reflected at the target table. I don't know how to resolve the problem.

推荐答案

审核的代码

CREATE OR REPLACE FUNCTION foo()
   RETURNS void AS
$func$
DECLARE
   _tbl text := 'Table_1';  -- or 'table_1'?
BEGIN       
   EXECUTE 'INSERT INTO schemaname.targettable(column_name)
   SELECT  "Col_A"
   FROM    schemaname.' || quote_ident(_tbl);  -- or "Schemaname"?
END
$func$  LANGUAGE plpgsql;

• 对于持久化的INSERT语句始终使用显式目标列表.

  • Always use an explicit target list for persisted INSERT statements.

    您可以在声明时分配变量.

    You can assign variables at declare time.

    使用双引号标识符来保存否则可能是非法的拼写是一种普遍的愚蠢行为.您必须在名称的其余部分保留双引号.这些错误中的一个或多个似乎已经潜入您的代码中:"Schemaname"或"schemaname"? Table_1或"Table_1"?

    It's a wide-spread folly to use double-quoted identifiers to preserve otherwise illegal spelling. You have to keep double-quoting the name for the rest of its existence. One or more of those errors seem to have crept into your code: "Schemaname" or "schemaname"? Table_1 or "Table_1"?

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

    当您提供一个标识符(如表名)作为text参数并用quote_ident()转义时，它是 区分大小写 ！
    除非使用双引号，否则SQL代码中的标识符将强制转换为小写.但是quote-ident()(您必须 用来防止SQL注入)在必要时会保留双引号提供的拼写.

    When you provide an identifier like a table name as text parameter and escape it with quote_ident(), it is case sensitive!
    Identifiers in SQL code are cast to lower case unless double-quoted. But quote-ident() (which you must use to defend against SQL injection) preserves the spelling you provide with double-quotes where necessary.

    CREATE OR REPLACE FUNCTION foo(_tbl text)
       RETURNS void AS
    $func$
    BEGIN       
       EXECUTE 'INSERT INTO schemaname.targettable(column_name)
       SELECT  "Col_A"
       FROM    schemaname.' || quote_ident(_tbl);
    END
    $func$  LANGUAGE plpgsql;

    致电:

    SELECT foo('tablename');  -- tablename is case sensitive
    

    还有其他方法:

    • 表名作为PostgreSQL函数参数

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