Example: End-to-end v5

-- 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 enterprisedb
          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")
        }
)
-- Note: The given value for "_id" column will be ignored and allow MongoDB to insert 
-- the unique value for the "_id" column.

-- 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;

-- drop foreign table
DROP FOREIGN TABLE warehouse;

-- drop user mapping
DROP USER MAPPING FOR enterprisedb SERVER mongo_server;

-- drop server
DROP SERVER mongo_server;