Postgresql snippets

change_all_tables.sql

SELECT tablename FROM pg_tables WHERE schemaname = 'public';
-- Move out of public
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 valimail_defend_stage_reporting_20181127_super;';
    END LOOP;
END;
$$;

SELECT tablename FROM pg_tables WHERE schemaname = 'valimail_defend_stage_reporting_20181127_super';

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