Writable materialized view with a custom column (merged) [message #434841] |
Fri, 11 December 2009 03:49  |
adry1
Messages: 1 Registered: December 2009
|
Junior Member |
|
|
Hello folk!
I will really appreciate your help with the following:
I need to have a Materialized View. It should be 'Fast'-refreshed on demand. In addition to columns from tables which form the basis for MV, I need to have there an additional column which stores some operational data and must be updatable.
So, I create the MV as:
CREATE MATERIALIZED VIEW schema.test_view REFRESH FAST ON DEMAND FOR UPDATE AS
SELECT column_a, 'Initial Value' AS custom_column FROM my_table
Then, some external logic makes updates for the custom_column like
update test_view set custom_column = 'Done'
But as a refresh for the MV is being made, I get for custom_column its initial value ('Initial Value').
In documentation, it is stated:
Quote:Users can perform DML operations on a writeable materialized view, but if you refresh the materialized view, then these changes are not pushed back to the master and the changes are lost in the materialized view itself.
So, no solution for my case..?
Thank you very much!
Andrey
|
|
|
|