I created an updatable view
V1 in an Oracle 11g DB (it has
INSTEAD OF triggers for
INSERT); it originally had a
GROUP BY query in it, which I moved to another view
V2. This other view is joined in my view
V1, making the view
V1 updatable from Oracle.
I create a foreign table in a PostgreSQL DB from the Oracle view
V1 using oracle_fdw;
OPTIONS (key 'true') is set for the primary key column.
I expect the foreign table to be updatable, but I receive this error:
Error synchronizing data with database
Reason: SQL-Fehler [HV00L]: ERROR: error executing query: OCIStmtExecute failed to execute remote query Detail: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
from view with DISTINCT, GROUP BY, etc.
Is there a way I can tell oracle_fdw to allow updating the foreign table?
UPDATE on the view
V1 works fine from Oracle side.
SELECT ... FOR UPDATE (from the Oracle side) fails with the same error ORA-02014.
I guess it comes down to preventing oracle_fdw to do a
SELECT FOR UPDATE on the view when
UPDATEing the foreign table, but the documentation seems to hint at this row-by-row processing being by design of foreign data wrappers.
So, is there a way? Or do I have to place my
INSTEAD OF triggers on a view of the PostgreSQL side and only map Oracle base tables as foreign tables?