Easily create foreign data wrappers

a.rb

#!/usr/bin/env ruby

require 'uri'
require 'pg'

uri = URI.parse(ENV['REMOTE_DATABASE_URL'])

preamble = %Q(
      CREATE EXTENSION IF NOT EXISTS postgres_fdw;
      DROP SERVER IF EXISTS fdw_db;
      CREATE SERVER fdw_db
            FOREIGN DATA WRAPPER postgres_fdw
            OPTIONS (dbname '#{uri.path[1..-1]}', host '#{uri.host}');
      CREATE USER MAPPING FOR CURRENT_USER
            SERVER fdw_db
            OPTIONS (user '#{uri.user}', password '#{uri.password}');
);


table_sql = %Q(SELECT
        'CREATE FOREIGN TABLE '
        || quote_ident('fdw_' || c.relname)
        || '(' || array_to_string(array_agg(quote_ident(a.attname) || ' ' || t.typname), ', ') || ') '
        || ' SERVER fdw_db OPTIONS'
        || ' (schema_name ''' || quote_ident(n.nspname) || ''', table_name ''' || quote_ident(c.relname) || ''');'
      FROM
        pg_class     c,
        pg_attribute a,
        pg_type      t,
        pg_namespace n
      WHERE
        a.attnum > 0
        AND a.attrelid = c.oid
        AND a.atttypid = t.oid
        AND n.oid = c.relnamespace
        AND c.relkind in ('r', 'v')
        AND n.nspname <> 'pg_catalog'
        AND n.nspname <> 'information_schema'
        AND n.nspname !~ '^pg_toast'
        AND pg_catalog.pg_table_is_visible(c.oid)
      GROUP BY c.relname, n.nspname
      ORDER BY c.relname;
)


def connection_hash(str)
  uri = URI.parse(str)
  connect = {}
  connect[:host] = uri.host
  connect[:port] = uri.port
  connect[:dbname] = uri.path[1..-1]
  connect[:user] = uri.user
  connect[:password] = uri.password
  connect[:sslmode] = :prefer
  connect
end

zd = PG::Connection.connect(connection_hash(ENV['REMOTE_DATABASE_URL']))
result = zd.exec(table_sql).values
zd.finish
result = preamble + result.join(" ")

begin
  db = PG::Connection.connect(connection_hash(ENV['LOCAL_DATABASE_URL']))
  db.exec(result)
  db.finish
  puts 'success'
rescue Exception => e
  puts e.message
end