[ https://issues.jboss.org/browse/TEIID-2584?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-2584: ------------------------------------- For Example if the DDL of view is like this (note all the OPTION properties with "teiid_rel:MATVIEW_" prefix are being proposed) {code:lang=SQL} CREATE VIEW actor ( actor_id integer, first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, last_update timestamp NOT NULL ) OPTIONS (MATERIALIZED 'TRUE', MATERIALIZED_TABLE 'pg.public.mat_actor', teiid_rel:MATVIEW_STATUS_TABLE 'pg.public.status', teiid_rel:MATVIEW_STAGING_TABLE 'pg.public.mat_actor_staging' teiid_rel:MATVIEW_TRUNCATE_SCRIPT 'truncate table pg.public.mat_actor_staging' teiid_rel:MATVIEW_SWAP_SCRIPT 'ALTER TABLE pg.public.mat_actor RENAME TO pg.public.mat_actor_temp; ALTER TABLE pg.public.mat_actor_staging RENAME TO pg.public.mat_actor; ALTER TABLE pg.public.mat_actor_temp RENAME TO pg.public.mat_actor_staging;') teiid_rel:MATVIEW_LOAD_SCRIPT 'select * into pg.public.mat_actor from sakila.actor OPTION NOCACHE;') AS SELECT actor_id, first_name, last_name, last_update from pg."public".actor; {code} then based on above metadata I will either execute a anonymous block or create a "load" procedure like {code} CREATE VIRTUAL PROCEDURE load() BEGIN DECLARE string status; status = (select state FROM teiid_rel:MATVIEW_STATUS_TABLE WHERE tablename='actor'); IF (status <> 'LOADING' or status <> 'LOADED') BEGIN update teiid_rel:MATVIEW_STATUS_TABLE set state = 'LOADING' where tablename = 'sakila.actor'; teiid_rel:MATVIEW_TRUNCATE_SCRIPT teiid_rel:MATVIEW_LOAD_SCRIPT teiid_rel:MATVIEW_SWAP_SCRIPT update teiid_rel:MATVIEW_STATUS_TABLE set state = 'LOADED', valid=true, updated=now(), cardinality = VARIABLES.ROWCOUNT where tablename = 'sakila.actor'; EXCEPTION e update teiid_rel:MATVIEW_STATUS_TABLE set state = 'FAILED_LOAD', valid=false, updated=now(), cardinality = -1 where tablename = 'sakila.actor'; END END {code} and {code} CREATE VIRTUAL PROCEDURE needsLoading(ttl, tblName) returns boolean BEGIN DECLARE boolean valid; DECLARE long last_update; LOOP ON select valid, last_update FROM teiid_rel:MATVIEW_STATUS_TABLE WHERE tablename='tblName' AS status BEGIN IF (status.valid = null ) BEGIN insert into teiid_rel:MATVIEW_STATUS_TABLE (tablename, valid, state, cardinality, updated) values ('tblName', 'false', 'NEEDS_LOADING', -1, now()); END ELSE (status.valid = false or (status.valid = true and status.last_update < now()-ttl) BEGIN update teiid_rel:MATVIEW_STATUS_TABLE set state = 'NEEDS_LOADING' where tablename = 'sakila.actor'; END END END {code} Where during the execution check the "needsLoading" if true, then execute the "load" -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira