Example: Using the MongoDB Foreign Data Wrapper

Before using the MongoDB foreign data wrapper, you must connect to your database with a client application. The following examples demonstrate using the wrapper with the psql client. After connecting to psql, you can follow the steps in the example below:

-- load extension first time after install
CREATE EXTENSION mongo_fdw;

-- create server object
CREATE SERVER mongo_server
          FOREIGN DATA WRAPPER mongo_fdw
          OPTIONS (address '127.0.0.1', port '27017');

-- create user mapping
CREATE USER MAPPING FOR postgres
          SERVER mongo_server
          OPTIONS (username 'mongo_user', password 'mongo_pass');

-- create foreign table
CREATE FOREIGN TABLE warehouse
            (
                    _id name,
                    warehouse_id int,
                    warehouse_name text,
                    warehouse_created timestamptz
            )
          SERVER mongo_server
          OPTIONS (database 'db', collection 'warehouse');

-- Note: first column of the table must be "_id" of type "name".

-- select from table
SELECT * FROM warehouse WHERE warehouse_id = 1;
           _id            | warehouse_id | warehouse_name |     warehouse_created
--------------------------+--------------+----------------+---------------------------
 53720b1904864dc1f5a571a0 |            1 | UPS            | 2014-12-12 12:42:10+05:30
(1 row)

db.warehouse.find
(
            {
                    "warehouse_id" : 1
            }
).pretty()
{
            "_id" : ObjectId("53720b1904864dc1f5a571a0"),
            "warehouse_id" : 1,
            "warehouse_name" : "UPS",
            "warehouse_created" : ISODate("2014-12-12T07:12:10Z")
}

-- insert row in table
INSERT INTO warehouse VALUES (0, 2, 'Laptop', '2015-11-11T08:13:10Z');

db.warehouse.insert
(
            {
                    "warehouse_id" : NumberInt(2),
                    "warehouse_name" : "Laptop",
                    "warehouse_created" : ISODate("2015-11-11T08:13:10Z")
            }
)

-- delete row from table
DELETE FROM warehouse WHERE warehouse_id = 2;

db.warehouse.remove
(
            {
                    "warehouse_id" : 2
            }
)

-- update a row of table
UPDATE warehouse SET warehouse_name = 'UPS_NEW' WHERE warehouse_id = 1;

db.warehouse.update
(
            {
                    "warehouse_id" : 1
            },
            {
                    "warehouse_id" : 1,
                    "warehouse_name" : "UPS_NEW",
                    "warehouse_created" : ISODate("2014-12-12T07:12:10Z")
            }
)

-- explain a table
EXPLAIN SELECT * FROM warehouse WHERE warehouse_id = 1;
                           QUERY PLAN
-----------------------------------------------------------------
 Foreign Scan on warehouse  (cost=0.00..0.00 rows=1000 width=84)
   Filter: (warehouse_id = 1)
   Foreign Namespace: db.warehouse
(3 rows)

-- collect data distribution statistics
ANALYZE warehouse;