change schema of all tables postgres

Solutions on MaxInterview for change schema of all tables postgres by the best coders in the world

showing results for - "change schema of all tables postgres"
Jonas
10 Mar 2018
1DO
2$$DECLARE
3   p_table regclass;
4BEGIN
5   SET LOCAL search_path='xyz';
6   FOR p_table IN
7      SELECT oid FROM pg_class
8      WHERE relnamespace = 'xyz'::regnamespace
9        AND relkind = 'r'
10   LOOP
11      EXECUTE format('ALTER TABLE %s SET SCHEMA public', p_table);
12   END LOOP;
13END;$$;