rename_schema.sql

DO $$
DECLARE
    old_schema TEXT := 'old_schema';  -- ← replace with the current schema name
    new_schema TEXT := 'new_schema';  -- ← replace with the target schema name
    r RECORD;
BEGIN
    -- Ensure the target schema exists
    EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I;', new_schema);

    -- Move all tables from old_schema to new_schema
    FOR r IN
        SELECT tablename
        FROM pg_tables
        WHERE schemaname = old_schema
    LOOP
        EXECUTE format(
            'ALTER TABLE %I.%I SET SCHEMA %I;',
            old_schema, r.tablename, new_schema
        );
    END LOOP;

    -- Move all views from old_schema to new_schema
    FOR r IN
        SELECT viewname
        FROM pg_views
        WHERE schemaname = old_schema
    LOOP
        EXECUTE format(
            'ALTER VIEW %I.%I SET SCHEMA %I;',
            old_schema, r.viewname, new_schema
        );
    END LOOP;

    -- Move all sequences from old_schema to new_schema
    FOR r IN
        SELECT sequence_name
        FROM information_schema.sequences
        WHERE sequence_schema = old_schema
    LOOP
        EXECUTE format(
            'ALTER SEQUENCE %I.%I SET SCHEMA %I;',
            old_schema, r.sequence_name, new_schema
        );
    END LOOP;

    -- Move all functions from old_schema to new_schema
    FOR r IN
        SELECT p.proname,
               pg_get_function_identity_arguments(p.oid) AS args
        FROM pg_proc p
        JOIN pg_namespace n ON p.pronamespace = n.oid
        WHERE n.nspname = old_schema
    LOOP
        EXECUTE format(
            'ALTER FUNCTION %I.%I(%s) SET SCHEMA %I;',
            old_schema, r.proname, r.args, new_schema
        );
    END LOOP;

    RAISE NOTICE 'Migration completed: % → %', old_schema, new_schema;
END
$$;