CREATE EXTENSION postgres_fdw;

CREATE SERVER novu_core
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'novu_core_development');

CREATE USER MAPPING FOR jeremym SERVER novu_core OPTIONS (user 'jeremym', password '');

CREATE FOREIGN TABLE novu_core_members (
  id int,
  hermes_id int,
  prime_id int,
  prime_last_sign_in_at timestamp without time zone,
  prime_member_state text,
  prime_portal_url text,
  tuid text,
  rewards_able boolean
)
SERVER novu_core
OPTIONS (table_name 'members');

SELECT m.*,
       fdw.*
FROM members m
  JOIN novu_core_members fdw ON fdw.hermes_id = m.id;