oracle_fdw cannot insert into a table with generated always identity column

[KingZhaoTPG]

Hi Laurenz,

Sorry to bother you, I want to ask a question. I created a foreign table using oracle_fdw, but an error occurred during insertion. The table creation information is as follows:

--oracle table:
CREATE TABLE test(
id NUMBER GENERATED ALWAYS AS IDENTITY,
name VARCHAR2(100)
);

--postgres foreign table:

CREATE FOREIGN TABLE t1(
id number,
name varchar2(100)
)
SERVER fixora21
OPTIONS (schema 'C##ZZHASHE', table 'TEST);

postgres=# insert into t1(name) values ('t1--hhh');
ERROR: HV00L: error executing query: OCIStmtExecute failed to execute remote query
DETAIL: ORA-32795: cannot insert into a generated always identity column

Thanks!

[laurenz]

That's tricky. I don't think that there is a way to insert into that foreign table...

Here is a possible way to work around that limitation:

• Create a view in Oracle:

  CREATE VIEW test_view AS SELECT name FROM test;
  

• Define a foreign table for that view in PostgreSQL:

  IMPORT FOREIGN SCHEMA "LAURENZ" LIMIT TO (test_view) FROM SERVER oracle INTO laurenz;
  

• Define a BEFORE INSERT trigger on t1:

  CREATE FUNCTION ins_test() RETURNS trigger
     LANGUAGE plpgsql AS
  $$BEGIN
     INSERT INTO test_view (name) VALUES (NEW.name);
     RETURN NULL;
  END;$$;
  
  CREATE TRIGGER ins_test BEFORE INSERT ON t1
     FOR EACH ROW EXECUTE FUNCTION ins_test();
  

• Now you can insert:

  INSERT INTO t1(name) VALUES ('t1--hhh');
  INSERT 0 0
  TABLE t1;
   id │  name   
  ════╪═════════
    1 │ t1--hhh
  (1 row)