PostgreSQL: move all objects from one tablespace to default one

gistfile1.sql

-- The following code will generate a set of ALTER queries
-- to move tables and indexes from one tablespace to another.
-- WARNING: Running such ALTERs in production means huge stress for DB.
-- Use pg_repack with "-s" option (http://reorg.github.io/pg_repack/)

-- TODO: generate pg_repack call, smth like:
--   pg_repack [connection creds] -s TABLESPACENAME \
--     -t tbl1 \
--     ...
--     -i idx1 \
--     ...

set postgres_dba.tablespace_from to 'hdd';
set postgres_dba.tablespace_to to 'pg_default';

do $$
declare
  rec record;
  sql text;
begin
  sql := '';
  for rec in select * from pg_indexes where tablespace = current_setting('postgres_dba.tablespace_from') loop
    sql := sql || format(
      e'\nalter index %I.%I set tablespace %I;',
      rec.schemaname,
      rec.indexname,
      current_setting('postgres_dba.tablespace_to')
    );
  end loop;
  for rec in select * from pg_tables where tablespace = current_setting('postgres_dba.tablespace_from') loop
    sql := sql || format(
      e'\nalter table %I.%I set tablespace %I;',
      rec.schemaname,
      rec.tablename,
      current_setting('postgres_dba.tablespace_to')
    );
  end loop;
  raise info '%', sql;
end;
$$ language plpgsql;

reset postgres_dba.tablespace_from;
reset postgres_dba.tablespace_to;