CREATE DATABASE mergeDatabase;
\connect mergedatabase;

CREATE EXTENSION mysql_fdw;

CREATE SERVER server1
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host 'db1', port '3306');

CREATE SERVER server2
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host 'db2', port '3306');

CREATE USER MAPPING FOR postgres
SERVER server1
OPTIONS (username 'user1', password 'secret1');

CREATE USER MAPPING FOR postgres
SERVER server2
OPTIONS (username 'user2', password 'secret2');

CREATE FOREIGN TABLE users1(
  id SERIAL,
  dummy_data text,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
)
SERVER server1
OPTIONS (dbname 'db1', table_name 'user1');

CREATE FOREIGN TABLE users2(
  id SERIAL,
  dummy_data text,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
)
SERVER server2
OPTIONS (dbname 'db2', table_name 'user2');

CREATE VIEW myView AS
SELECT id,
    dummy_data AS country,
    created_at,
    updated_at
  FROM users1
  UNION ALL
SELECT (id + (SELECT MAX(id) FROM users1)) AS id,
    dummy_data AS country,
    created_at,
    updated_at
  FROM users2;