主页 / dba / 问题 / 114771
Accepted
dd_a
Asked: 2015-09-12 04:50:01 +0800 CST

如何在函数中按顺序对表列表运行 ALTER TABLE

  • 772

我有一个模式（称为import），其中数据作为表导入，以及一个处理所有数据的函数。之后，我想将这些表移到另一个模式（称为data_archive）中。现有功能效果很好，但我找不到更改架构的方法；这是我为此编写的一个函数：

我的想法是根据 aselect在为此管理制作的表格上提供的标准创建表格列表，然后遍历此列表并执行以下操作Alter table xxxx SET schema data_archive：

CREATE OR REPLACE FUNCTION archive_datasets()
  RETURNS VOID AS
$BODY$
DECLARE
   table_rec  record;

BEGIN

   FOR table_rec IN
      SELECT t.table_name
      FROM information_schema.tables AS t
      WHERE table_schema = 'import'
      AND quote_ident(t.table_name) IN (
              SELECT "table_name"
              FROM data_sets 
              WHERE status IN ('Processed', 'Archived', 'Deleted')
              ) 

   LOOP
     ALTER TABLE quote_ident(table_rec.table_name) SET schema data_archived;
   END LOOP;

END;
$BODY$
  LANGUAGE plpgsql;

但我收到此错误消息：

ERROR:  syntax error at or near "("
LINE 28:  ALTER TABLE quote_ident(table_rec.table_name) SET schema ...

所以我的问题是如何将表的名称传递给这个ALTER TABLE查询？或者我做错了什么？

我使用 PostgreSQL 9.3。

postgresql functions

1 个回答

  1. Best Answer

    问题是您不能像ALTER TABLE上面那样在直接的 SQL 语句中使用参数作为对象名称（表、列等）。

    我在这里使用“直接”作为“动态”的对立面——你需要的东西：

    ...
loop
    EXECUTE format($$ALTER TABLE %I SET SCHEMA TO data_archived$$, 
                   table_rec.table_name);
END loop;
...

    备注：

    • 在这里，我在构建动态查询时使用美元报价。例如，这允许我使用“正常”语法，而不是乘以单引号（本示例中不存在）。这样，大多数编辑都会很好地突出显示这些语句。
    • 如果需要，我还使用format()格式%I说明符来正确引用对象名称。quote_ident()这种方法比使用串联字符串常量和一些调用来构建查询更具可读性。它存在于 9.1 和更新版本中。
    • 我有一种感觉，您将类似的东西进行比较quote_ident('This_table') IN ('This_table')，这是不正确的。quote_ident()两边都使用- 或者更容易省略引用并比较字符串值。
    • 7

相关问题