如何在一个操作中更改多个PostgreSQL表的架构? [英] How to change schema of multiple PostgreSQL tables in one operation?

问题描述

我有一个PostgreSQL 9.1数据库，其中包含100个左右的表，这些表已加载到公共"模式中.我想将这些表(但不是公开"中的所有功能)移到数据"模式.

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.

我知道我可以使用以下内容一次移动1张桌子.

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

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?

推荐答案

做将达到目的:

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

这篇关于如何在一个操作中更改多个PostgreSQL表的架构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！