如何在一次操作中更改多个 PostgreSQL 表的架构？

How to change schema of multiple PostgreSQL tables in one operation?

提问by RyanKDalton

I have a PostgreSQL 9.1 database with 100 or so tables that were loaded into the 'public' schema. I would like to move those tables (but not all of the functions in 'public') to a 'data' schema.

我有一个 PostgreSQL 9.1 数据库，其中包含 100 个左右的表，这些表已加载到“公共”模式中。我想将这些表（但不是“公共”中的所有功能）移动到“数据”模式。

I know that I can use the following to move 1 table at a time.

我知道我可以使用以下方法一次移动 1 个表。

ALTER TABLE [tablename] SET SCHEMA [new_schema]

Is it possible to move all of the tables to the new schema in one operation? If so, what would be the most efficient way to accomplish this task?

是否可以在一次操作中将所有表移动到新模式？如果是这样，完成这项任务的最有效方法是什么？

回答by Frank Heikens

DOwill do the trick:

DO将这样的伎俩：

DO
$$
DECLARE
    row record;
BEGIN
    FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' -- and other conditions, if needed
    LOOP
        EXECUTE 'ALTER TABLE public.' || quote_ident(row.tablename) || ' SET SCHEMA [new_schema];';
    END LOOP;
END;
$$;