.::: Create External Table PostgreSQL, EDB using Foreign Data Wrapper (postgres_fdw) like DBLink :::.

1. create new externaldb for sample
create database externaldb;
\c externaldb;

teguhth=# create database externaldb;
CREATE DATABASE
teguhth=# \c externaldb;
You are now connected to database "externaldb" as user "postgres".
externaldb=#

2. install postgres_fdw;
CREATE EXTENSION postgres_fdw;
SELECT * FROM pg_extension;

externaldb=# SELECT * FROM pg_extension;
  oid  |   extname    | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+--------------+----------+--------------+----------------+------------+-----------+--------------
 14267 | plpgsql      |       10 |           11 | f              | 1.0        |           |
 25012 | postgres_fdw |       10 |         2200 | t              | 1.1        |           |
(2 rows)
 

3. create server foreign data

CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '10.10.10.8', port '5432', dbname 'teguhth');
\des
select * from pg_foreign_server;

externaldb=# select * from pg_foreign_server;
  oid  |    srvname    | srvowner | srvfdw | srvtype | srvversion | srvacl |                 srvoptions
-------+---------------+----------+--------+---------+------------+--------+--------------------------------------------
 25019 | remote_server |       10 |  25015 |         |            |        | {host=10.10.10.8,port=5432,dbname=teguhth}
(1 row)

externaldb=#
 

4. create user maping

CREATE USER MAPPING FOR postgres SERVER remote_server OPTIONS (user 'admin', password 'admin');
select * from  pg_user_mappings;

externaldb=# CREATE USER MAPPING FOR postgres SERVER remote_server OPTIONS (user 'admin', password 'admin');
CREATE USER MAPPING
externaldb=# select * from  pg_user_mappings;
 umid  | srvid |    srvname    | umuser | usename  |          umoptions
-------+-------+---------------+--------+----------+-----------------------------
 25020 | 25019 | remote_server |     10 | postgres | {user=admin,password=admin}
(1 row)

externaldb=#
 

5. create schema & import schema for external
CREATE SCHEMA local_schema;
IMPORT FOREIGN SCHEMA public from SERVER remote_server into local_schema;

externaldb=# CREATE SCHEMA local_schema;
CREATE SCHEMA
externaldb=# IMPORT FOREIGN SCHEMA public from SERVER remote_server into local_schema;
IMPORT FOREIGN SCHEMA
externaldb=#

or

IMPORT FOREIGN SCHEMA public from SERVER remote_server into public;

6. check after import schema
select * from information_schema.foreign_tables;

externaldb=# select * from information_schema.foreign_tables;
 foreign_table_catalog | foreign_table_schema | foreign_table_name | foreign_server_catalog | foreign_server_name
-----------------------+----------------------+--------------------+------------------------+---------------------
 externaldb            | local_schema         | barang             | externaldb             | remote_server
 externaldb            | local_schema         | customer           | externaldb             | remote_server
 externaldb            | local_schema         | local_table        | externaldb             | remote_server
 externaldb            | local_schema         | pasok              | externaldb             | remote_server
 externaldb            | local_schema         | pembelian          | externaldb             | remote_server
 externaldb            | local_schema         | suplier            | externaldb             | remote_server
(6 rows)

externaldb=#
 

7. check table
select * from local_schema.barang;
select * from local_schema.pembelian;

externaldb=# select * from local_schema.barang;
 kode_barang | nama_barang | satuan_barang | stok_barang
-------------+-------------+---------------+-------------
 ELK-01      | RICE COOKER | BUAH          |          20
 ELK-02      | LEMARI ES   | UNIT          |           8
 ELK-03      | TELEVISI    | UNIT          |          30
 ELK-04      | RADIO/TAPE  | BUAH          |          35
 ELK-05      | KOMPUTER    | UNIT          |          28
 ELK-06      | KIPAS ANGIN | BUAH          |          38
(6 rows)

externaldb=# select * from local_schema.pembelian;
 kode_pembelian | kode_barang | kode_customer | tanggal_pembelian | jumlah_pembelian
----------------+-------------+---------------+-------------------+------------------
 BEL-E001       | ELK-01      | J-0001        | 2002-05-20        |                3
 BEL-E002       | ELK-01      | J-0001        | 2002-05-21        |                4
 BEL-E003       | ELK-01      | J-0002        | 2002-05-20        |                2
 BEL-E004       | ELK-01      | B-0001        | 2002-05-20        |                2
 BEL-E005       | ELK-01      | B-0002        | 2002-05-22        |                3
(5 rows)

externaldb=#
 

8. check query if using join
select b.nama_barang,s.nama_suplier,p.tanggal_pasok,p.jumlah_pasok from local_schema.barang b,local_schema.suplier s,local_schema.pasok p where b.kode_barang=p.kode_barang and s.kode_suplier=p.kode_suplier;

externaldb=# select b.nama_barang,s.nama_suplier,p.tanggal_pasok,p.jumlah_pasok from local_schema.barang b,local_schema.suplier s,local_schema.pasok p where b.kode_barang=p.kode_barang and s.kode_suplier=p.kode_suplier;
 nama_barang |    nama_suplier     | tanggal_pasok | jumlah_pasok
-------------+---------------------+---------------+--------------
 RICE COOKER | PT ACTRON           | 2002-01-01    |            8
 RICE COOKER | PT MULYA ELEKTRONIK | 2002-01-01    |            5
 LEMARI ES   | PT ACTRON           | 2002-02-01    |            2
 LEMARI ES   | PT MULYA ELEKTRONIK | 2002-02-02    |            3
 LEMARI ES   | PT SUPERTRON        | 2002-01-01    |            2
 TELEVISI    | PT ACTRON           | 2002-03-03    |            5
 TELEVISI    | PT ACTRON           | 2002-03-04    |            2
 TELEVISI    | PT ACTRON           | 2002-03-03    |            3
 TELEVISI    | PT ULTRASOUND       | 2002-03-13    |            4
(9 rows)

externaldb=#

9. continue if using default schema (public)

IMPORT FOREIGN SCHEMA public from SERVER remote_server into public;

externaldb=# select * from information_schema.foreign_tables;
 foreign_table_catalog | foreign_table_schema | foreign_table_name | foreign_server_catalog | foreign_server_name
-----------------------+----------------------+--------------------+------------------------+---------------------
 externaldb            | local_schema         | barang             | externaldb             | remote_server
 externaldb            | local_schema         | customer           | externaldb             | remote_server
 externaldb            | local_schema         | local_table        | externaldb             | remote_server
 externaldb            | local_schema         | pasok              | externaldb             | remote_server
 externaldb            | local_schema         | pembelian          | externaldb             | remote_server
 externaldb            | local_schema         | suplier            | externaldb             | remote_server
 externaldb            | public               | barang             | externaldb             | remote_server
 externaldb            | public               | customer           | externaldb             | remote_server
 externaldb            | public               | local_table        | externaldb             | remote_server
 externaldb            | public               | pasok              | externaldb             | remote_server
 externaldb            | public               | pembelian          | externaldb             | remote_server
 externaldb            | public               | suplier            | externaldb             | remote_server
(12 rows)

externaldb=#

10. cek contain table
select * from barang;
select b.nama_barang,s.nama_suplier,p.tanggal_pasok,p.jumlah_pasok from local_schema.barang b,local_schema.suplier s,local_schema.pasok p where b.kode_barang=p.kode_barang and s.kode_suplier=p.kode_suplier;

externaldb=# select * from barang;
 kode_barang | nama_barang | satuan_barang | stok_barang
-------------+-------------+---------------+-------------
 ELK-01      | RICE COOKER | BUAH          |          20
 ELK-02      | LEMARI ES   | UNIT          |           8
 ELK-03      | TELEVISI    | UNIT          |          30
 ELK-04      | RADIO/TAPE  | BUAH          |          35
 ELK-05      | KOMPUTER    | UNIT          |          28
 ELK-06      | KIPAS ANGIN | BUAH          |          38
(6 rows)

externaldb=#
externaldb=# select b.nama_barang,s.nama_suplier,p.tanggal_pasok,p.jumlah_pasok from local_schema.barang b,local_schema.suplier s,local_schema.pasok p where b.kode_barang=p.kode_barang and s.kode_suplier=p.kode_suplier;
 nama_barang |    nama_suplier     | tanggal_pasok | jumlah_pasok
-------------+---------------------+---------------+--------------
 RICE COOKER | PT ACTRON           | 2002-01-01    |            8
 RICE COOKER | PT MULYA ELEKTRONIK | 2002-01-01    |            5
 LEMARI ES   | PT ACTRON           | 2002-02-01    |            2
 LEMARI ES   | PT MULYA ELEKTRONIK | 2002-02-02    |            3
 LEMARI ES   | PT SUPERTRON        | 2002-01-01    |            2
 TELEVISI    | PT ACTRON           | 2002-03-03    |            5
 TELEVISI    | PT ACTRON           | 2002-03-04    |            2
 TELEVISI    | PT ACTRON           | 2002-03-03    |            3
 TELEVISI    | PT ULTRASOUND       | 2002-03-13    |            4
(9 rows)

externaldb=#