MySQL FDW for PostgreSQL 9.1+
This PostgreSQL extension implements a Foreign Data Wrapper (FDW) for
This code is experimental, and largely intended as a pet project for me
to experiment with and learn about FDWs in PostgreSQL.
By all means use it, but do so entirely at your own risk! You have been
Install MySQL, or just the C client library, and Once that's done, the
extension can be built with:
PATH=/usr/local/pgsql/bin/:/usr/local/mysql/bin:$PATH make USE_PGXS=1
sudo PATH=/usr/local/pgsql/bin/:/usr/local/mysql/bin:$PATH make USE_PGXS=1 install
(assuming you have PostgreSQL 9.1 in /usr/local/pgsql and MySQL in
I've tested on Mac OS X 10.7 only, but other *nix's should also work.
I haven't tested on Windows, but the code should be good on MinGW.
- No attempt is made to pushdown quals to MySQL.
- The MySQL connection used to plan queries isn't currently reused
The following parameters can be set on a MySQL foreign server:
address: The address or hostname of the MySQL server.
port: The port number on which the MySQL server is listening.
The following parameter can be set on a MySQL foreign table:
database: The name of the MySQL database to query.
query: An SQL query to define the data set on the MySQL server.
table: The name of a table (quoted and qualified as required)
on the MySQL table.
Note that the query and table paramters are mutually exclusive. Using
query can provide either a simple way to push down quals (which of
course is fixed at definition time), or to base remote tables on
more complex SQL queries.
The following parameter can be set on a user mapping for a MySQL
username: The username to use when connecting to MySQL
password: The password to authenticate to the MySQL server with.
-- Install the extension
CREATE EXTENSION mysql_fdw;
-- Create the foreign server, a pointer to the MySQL server.
CREATE SERVER mysql_svr
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (address '127.0.0.1', port '3306');
-- Create one or more foreign tables on the MySQL server. The first of
-- these maps to a remote table, whilst the second uses an SQL query.
CREATE FOREIGN TABLE employees (
OPTIONS (table 'hr.employees');
CREATE FOREIGN TABLE overtime_2010 (
OPTIONS (query 'SELECT id, employee_id, hours FROM hr.overtime WHERE year = 2010;');
-- Create a user mapping to tell the FDW the username/password to
-- use to connect to MySQL, for PUBLIC. This could be done on a per-
-- role basis.
CREATE USER MAPPING FOR PUBLIC
OPTIONS (username 'dpage', password '');