MYSQL_FDW in Action

-- load extension first time after install

CREATE EXTENSION mysql_fdw;

-- create server object

CREATE SERVER mysql_server

FOREIGN DATA WRAPPER mysql_fdw

OPTIONS (host '127.0.0.1', port '3306');

-- create user mapping 

CREATE USER MAPPING FOR postgres

SERVER mysql_server

OPTIONS (username 'foo', password 'bar');

-- create foreign table

CREATE FOREIGN TABLE warehouse(

warehouse_id int,

warehouse_name text,

warehouse_created datetime)

SERVER mysql_server

OPTIONS (dbname 'db', table_name 'warehouse');

-- insert new rows in table

INSERT INTO warehouse values (1, 'UPS', sysdate());

INSERT INTO warehouse values (2, 'TV', sysdate());

INSERT INTO warehouse values (3, 'Table', sysdate());

-- select from table

SELECT * FROM warehouse;

warehouse_id | warehouse_name | warehouse_created

--------------+----------------+--------------------

1 | UPS            | 29-SEP-1423:33:46

2 | TV             | 29-SEP-1423:34:25

3 | Table          | 29-SEP-1423:33:49

-- delete row from table

DELETE FROM warehouse where warehouse_id = 3;

-- update a row of table

UPDATE warehouse set warehouse_name = 'UPS_NEW' where warehouse_id = 1;

-- explain a table

EXPLAIN SELECT warehouse_id, warehouse_name FROM warehouse WHERE warehouse_name LIKE 'TV' limit 1;

QUERY PLAN

---------------------------------------------------