geographica/postgis-fdw-mysql repository overview

docker run --name pgsql-fdq-mysql -d  geographica/postgis-fdw-mysql

docker exec -it pgsql-fdq-mysql psql -U postgres

CREATE EXTENSION postgis;

CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server
     FOREIGN DATA WRAPPER mysql_fdw
     OPTIONS (host '127.0.0.1', port '3306');

CREATE USER MAPPING FOR postgres
SERVER mysql_server
OPTIONS (username 'foo', password 'bar');

CREATE FOREIGN TABLE warehouse(
     warehouse_id int,
     warehouse_name text,
     warehouse_created datetime)
SERVER mysql_server
     OPTIONS (dbname 'db', table_name 'warehouse');

INSERT INTO warehouse values (1, 'UPS', sysdate());
INSERT INTO warehouse values (2, 'TV', sysdate());
INSERT INTO warehouse values (3, 'Table', sysdate());

SELECT * FROM warehouse;

warehouse_id | warehouse_name | warehouse_created  

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

        1 | UPS            | 29-SEP-14 23:33:46

        2 | TV             | 29-SEP-14 23:34:25

        3 | Table          | 29-SEP-14 23:33:49

DELETE FROM warehouse where warehouse_id = 3;

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

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

                                   QUERY PLAN                                                   
Limit  (cost=10.00..11.00 rows=1 width=36)
->  Foreign Scan on warehouse  (cost=10.00..13.00 rows=3 width=36)
     Local server startup cost: 10
     Remote query: SELECT warehouse_id, warehouse_name FROM db.warehouse WHERE ((warehouse_name like 'TV'))