# use pg_dump and pg_restore to migrate some databases from another server

psql $DBNAME << EOF
DO $$
DECLARE
    myschema RECORD;
    myview RECORD;
    myfunc RECORD;
    myseq RECORD;
BEGIN

    FOR myschema IN (SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name <> 'information_schema')
    LOOP

        -- Drop all views
        FOR myview IN (SELECT viewname FROM pg_catalog.pg_views WHERE schemaname=myschema.schema_name)
        LOOP
            EXECUTE 'DROP VIEW ' || quote_ident(myschema.schema_name) || '.'  || quote_ident(myview.viewname) || ';';
        END LOOP;

        -- Drop all sequences
        FOR myseq IN (SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema=myschema.schema_name)
        LOOP
            EXECUTE 'DROP SEQUENCE ' || quote_ident(myschema.schema_name) || '.'  || quote_ident(myseq.sequence_name) || ';';
        END LOOP;

        -- Drop all functions
        FOR myfunc IN (SELECT routine_name FROM information_schema.routines WHERE routine_type='FUNCTION' AND specific_schema=myschema.schema_name )
        LOOP
            EXECUTE 'DROP FUNCTION ' || quote_ident(myschema.schema_name) || '.'  || quote_ident(myfunc.routine_name) || ';';
        END LOOP;

    END LOOP;

END;
$$ LANGUAGE plpgsql;