\c template1

-- Create database localdb and foreigndb;
CREATE DATABASE localdb;
CREATE DATABASE foreigndb;

-- Create tables in foreigndb
\c foreigndb

DROP TABLE IF EXISTS dept1; 

-- #Case 1, deptno datatype is NUMERIC. 
CREATE TABLE dept1 (
  deptno  NUMERIC(10) NOT NULL CONSTRAINT dept1_pk PRIMARY KEY,
  dname   VARCHAR(32) NOT NULL DEFAULT (md5(random()::VARCHAR))
);

INSERT INTO dept1 VALUES (generate_series(1,100100));

DROP TABLE IF EXISTS dept2; 

-- #Case 2, deptno datatype is VARCHAR.
CREATE TABLE dept2 (
  deptno  VARCHAR(10) NOT NULL CONSTRAINT dept2_pk PRIMARY KEY,
  dname   VARCHAR(32) NOT NULL DEFAULT (md5(random()::VARCHAR))
);

INSERT INTO dept2 VALUES (trim(to_char(generate_series(1,100100),'9999999')));

DROP TABLE IF EXISTS dept3; 

-- #Case 3, deptno datatype is TEXT.
CREATE TABLE dept3 (
  deptno  TEXT NOT NULL CONSTRAINT dept3_pk PRIMARY KEY,
  dname   VARCHAR(32) NOT NULL DEFAULT (md5(random()::VARCHAR))
);

INSERT INTO dept3 VALUES (trim(to_char(generate_series(1,100100),'9999999')));

-- create tables in localdb
\c localdb;

-- create extension postgres_fdw
CREATE EXTENSION postgres_fdw;

DROP TABLE IF EXISTS emp1; 

-- #Case 1, deptno datatype is NUMERIC.
CREATE TABLE emp1 (
  empno   NUMERIC(4) NOT NULL CONSTRAINT emp1_pk PRIMARY KEY,
  ename   VARCHAR(10),
  deptno  NUMERIC(10)
);
 
INSERT INTO emp1 VALUES (7369,  'SMITH',  '20');
INSERT INTO emp1 VALUES (7499,  'ALLEN',  '30');

DROP TABLE IF EXISTS emp2;

-- #Case 2, deptno datatype is varchar. 
CREATE TABLE emp2 (
  empno   NUMERIC(4) NOT NULL CONSTRAINT emp2_pk PRIMARY KEY,
  ename   VARCHAR(10),
  deptno  VARCHAR(10)
);

INSERT INTO emp2 VALUES (7369,  'SMITH',  '20');
INSERT INTO emp2 VALUES (7499,  'ALLEN',  '30');

DROP TABLE IF EXISTS emp3; 

-- #Case 3, deptno datatype is TEXT.
CREATE TABLE emp3 (
  empno   NUMERIC(4) NOT NULL CONSTRAINT emp3_pk PRIMARY KEY,
  ename   VARCHAR(10),
  deptno  TEXT
);
 
INSERT INTO emp3 VALUES (7369,  'SMITH',  '20');
INSERT INTO emp3 VALUES (7499,  'ALLEN',  '30');

DROP SERVER IF EXISTS pgfdwsrv cascade;

CREATE SERVER pgfdwsrv
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'localhost', port '5432', dbname 'foreigndb', fdw_startup_cost '100', fdw_tuple_cost '100');

CREATE USER MAPPING FOR jeevan
  SERVER pgfdwsrv OPTIONS (user 'jeevan');

CREATE FOREIGN TABLE fdw_dept1
(
  deptno  NUMERIC(10),
  dname   VARCHAR(32)
) SERVER pgfdwsrv OPTIONS (schema_name 'public', table_name 'dept1', use_remote_estimate 'true');

CREATE FOREIGN TABLE fdw_dept2
(
  deptno  VARCHAR(10),
  dname   VARCHAR(32)
) SERVER pgfdwsrv OPTIONS (schema_name 'public', table_name 'dept2', use_remote_estimate 'true');

CREATE FOREIGN TABLE fdw_dept3
(
  deptno  TEXT,
  dname   VARCHAR(32)
) SERVER pgfdwsrv OPTIONS (schema_name 'public', table_name 'dept3', use_remote_estimate 'true');


-- #Case 1 Where condition is included in the Remote Query.
EXPLAIN (ANALYZE,VERBOSE)
  SELECT a.ename, d.dname
  FROM emp1 a,fdw_dept1 d
   WHERE d.deptno = a.deptno
   AND a.empno = 7369;

-- #Case 2 Where condition is not included in the Remote Query
EXPLAIN (ANALYZE,VERBOSE)
  SELECT a.ename, d.dname
  FROM emp2 a,fdw_dept2 d
   WHERE d.deptno = a.deptno
   AND a.empno = 7369;

-- #Case 3 Where condition is not included in the Remote Query
EXPLAIN (ANALYZE,VERBOSE)
  SELECT a.ename, d.dname
  FROM emp3 a,fdw_dept3 d
   WHERE d.deptno = a.deptno
   AND a.empno = 7369;