如何將所有表從一個 Postgres 模式移動到另一個

我想將所有表從架構移動xyz到預設架構public。

我可以移動單個表格

ALTER TABLE table_name
   SET SCHEMA public;

我可以得到所有的桌子

SELECT table_name FROM information_schema.tables WHERE table_schema='xyz'

有沒有辦法將這兩者結合在一起？

DO在語句中使用 PL/pgSQL ：

DO
$$DECLARE
  p_table regclass;
BEGIN
  SET LOCAL search_path='xyz';
  FOR p_table IN
     SELECT oid FROM pg_class
     WHERE relnamespace = 'xyz'::regnamespace
       AND relkind = 'r'
  LOOP
     EXECUTE format('ALTER TABLE %s SET SCHEMA public', p_table);
  END LOOP;
END;$$;

Laurenz ’s Answer的改進版；

DO LANGUAGE plpgsql
$body$
DECLARE
  l_old_schema NAME = 'old_schema';
  l_new_schema NAME = 'new_schema';
  l_sql TEXT;
BEGIN
 FOR l_sql IN
   SELECT 
       format('ALTER TABLE %I.%I SET SCHEMA %I', n.nspname, c.relname, l_new_schema)
     FROM pg_class c
     JOIN pg_namespace n ON n.oid = c.relnamespace
     WHERE
       n.nspname = l_old_schema AND
       c.relkind = 'r'
 LOOP
   RAISE NOTICE 'appliying %', l_sql;
   EXECUTE l_sql;
 END LOOP;
END;
$body$;

引用自：https://dba.stackexchange.com/questions/237813