SELECT current_database() AS current_database,
  current_setting('port') AS current_port
\gset


CREATE EXTENSION postgres_fdw;
CREATE SERVER loopback1 FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (dbname :'current_database', port :'current_port', async_capable 'on', fetch_size '1');
CREATE SERVER loopback2 FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (dbname :'current_database', port :'current_port', async_capable 'on', fetch_size '1');
CREATE SERVER loopback3 FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (dbname :'current_database', port :'current_port', async_capable 'on', fetch_size '1');
CREATE SERVER loopback4 FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (dbname :'current_database', port :'current_port', async_capable 'on', fetch_size '1');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback1;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback2;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback3;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback4;


create or replace function create_partitioned_rel(relname text, nparts int, foreign_parts bool, fetch_size int) returns void as $$
declare 
i int;
sql text;
part_name text;
fdw_part_name text;
begin
     execute format('create table %I (i int, s bytea) partition by hash(i)', relname);
     for i in 0..nparts-1 loop
          if foreign_parts then
               part_name := format('%s_%s', relname, i);
               fdw_part_name := format('%s_%s_fdw', relname, i);
               execute format('create foreign table %I partition of %I for values with (modulus %s, remainder %s) server loopback%s options (table_name %L, fetch_size %L)', fdw_part_name, relname, nparts, i, i%4+1, part_name, fetch_size);
               execute format('create table %I (i int, s bytea)', part_name);                       
          else
               part_name := format('%s_%s', relname, i);
               execute format('create table %I partition of %I for values with (modulus %s, remainder %s)', part_name, relname, nparts, i);
          end if;
     end loop;
end 
$$ language plpgsql;