PostgreSQL 9.1, meet MySQL

So having spent the last few months with my head buried deep in a project at work, I finally managed to get back to my previous hacking on SQL/MED at the weekend after the kids went away for a week (good $DEITY it's quiet here)! Within a couple of hours, I had my half-baked Foreign Data Wrapper for MySQL up and running, and am now able to create foreign table objects in PostgreSQL 9.1 that map either directly to tables in a MySQL database, or to queries run on the MySQL server.

Here's an example:

-- Create the required functions for the FDW.
CREATE FUNCTION mysql_fdw_handler()
RETURNS fdw_handler
AS '$libdir/mysql_fdw'
LANGUAGE C STRICT;

CREATE FUNCTION mysql_fdw_validator(text[], oid)
RETURNS void
AS '$libdir/mysql_fdw'
LANGUAGE C STRICT;

-- Create the data wrapper or "transport".
CREATE FOREIGN DATA WRAPPER mysql_fdw
HANDLER mysql_fdw_handler
VALIDATOR mysql_fdw_validator;

-- 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 (
id integer,
name text,
address text)
SERVER mysql_svr
OPTIONS (table 'hr.employees');

CREATE FOREIGN TABLE ex_staff (
id integer,
name text,
address text)
SERVER mysql_svr
OPTIONS (query 'SELECT * FROM hr.employees WHERE date_left IS NOT NULL');