将虚拟列从oracle迁移到postgres [英] MIgrating Virtual Columns from oracle to postgres

问题描述

从Oracle 11迁移到Postgres 9.5时，必须使用哪些选项处理虚拟列-无需更改应用程序中与数据库相关的代码(这意味着功能和视图不可行，并且触发器太昂贵了，因为处理大数据集)?

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)?

存在类似的问题: PostgreSQL中的计算/计算列，但解决方案对迁移方案无济于事.

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

推荐答案

如果使用BEFORE INSERT触发器，则可以在实际写入之前修改插入的值.那不应该很昂贵.如果需要最先进的性能，请在C中编写触发函数.

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)

这篇关于将虚拟列从oracle迁移到postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！