foreign table can't insert with _id values.

[lifubang]

There is a mongo foreign table testmongo: CREATE FOREIGN TABLE testmongo( _id NAME, id bigint, v int) SERVER mongo_server_online OPTIONS (database 'hello-world', collection 'testmongo')

when exec a insert sql: insert into testmongo(_id, id, v) values('579b010064c667a022187e29', 1, 1);

and then select: select * from testmongo;

the result is: _id,id,v "579f2c44c1f86422d4025104";1;1

the _id column value of select result is different from insert. select 579f2c44c1f86422d4025104 insert 579b010064c667a022187e29

how to fix it?

[lifubang]

We need to use trigger to insert some data from inner table to mongo foreign table, so the _id value of these two tables must be equal.

[ahsanhadi]

The first column of every mongodb_fdw foreign table is _id, this column contains the unique ID of the row generated by mongo. This is system mongodb column, we need this column to ensure uniqueness for update and delete. Even if you insert a value in this column, it will get replaced by the value generated by mongodb....

[lifubang]

I think you know that in nodejs' mongdb driver, we can use new ObjectId() to generate _id value and insert into mongodb, and the mongodb will accept the insert action. The _id value generated by myself will not be replaced by mongodb. Because _id value contains the machine's id information and time stamp, so it will not generate two equal _id values.

OK, if the mongo_fdw don't support _id insert action, please help me to get the _id value in pg trigger function after we insert a document into mongodb foreign table:

CREATE OR REPLACE FUNCTION process_k_cands()
  RETURNS trigger AS
$BODY$
    DECLARE mongo_id NAME;
    BEGIN
        IF (TG_OP = 'INSERT') THEN
        //we have a _id value in NEW.*
        INSERT INTO "wb_Cands" SELECT NEW.*;
        //how to get the new _id value in foreign table "wb_Cands" after insert?
            INSERT INTO mon_monitor("_id","object_id","paper_id","menber_name") SELECT NEW."_id",NEW."prjId",NEW."positionId",NEW."userName";
           // after insert, the _id value in mon_monitor is not the same as the _id value in "wb_Cands', how to resolve it?
            RETURN NEW;

        END IF;
        RETURN NULL;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION process_k_cands()
  OWNER TO postgres;