MIgrating Virtual Columns from oracle to postgres

Question

What options does one have to deal with virtual columns when migrating from Oracle 11 to Postgres 9.5 - without having to change database related code in an application (which means functions and views are out of the picture and triggers are way too expensive as dealing with large data sets)?

A similar question exists : Computed / calculated columns in PostgreSQL but the solutions do not help with the migration scenario.

Answer 1

If you use a BEFORE INSERT trigger, you can modify the values inserted before they actually are written. That shouldn't be very expensive. If cutting edge performance is required, write the trigger function in C.

But I think that a view is the best solution. You can use an updatable view, that way you wouldn't have to change the application code:

CREATE TABLE data(
   id integer PRIMARY KEY,
   factor1 integer NOT NULL,
   factor2 integer NOT NULL
);

CREATE VIEW interface AS
   SELECT id, factor1, factor2,
          factor1 * factor2 AS product
   FROM data;

test=> INSERT INTO interface VALUES (1, 6, 7), (2, 3, 14);
INSERT 0 2
test=> UPDATE interface SET factor1 = 7 WHERE id = 1;
UPDATE 1
test=> DELETE FROM interface WHERE id = 1;
DELETE 1
test=> SELECT * FROM interface;
┌────┬─────────┬─────────┬─────────┐
│ id │ factor1 │ factor2 │ product │
├────┼─────────┼─────────┼─────────┤
│  2 │       3 │      14 │      42 │
└────┴─────────┴─────────┴─────────┘
(1 row)