I need to create an external view on Postgres. On this db, I created the staging table, status table and destination table. Once I deployed the vdb, the process starts and the staging table is populated and the status table is regurarly updated. The process doesn't transfer the data from the staging table to destination table. In Internet, I found this example https://docs.jboss.org/author/display/TEIID/External+Materialization, but I don't know what is the syntax used for the MATVIEW_AFTER_LOAD_SCRIPT. In particular, I used the following comand: exec model_name.native('truncate table model_name.staging_table'), but I see this error:
TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE array_get(strings, index)" with the SQL statement "exec model_name.native('truncate table model_name.st_territories')" due to: TEIID30357 model_name.native does not exist.
Substantially, I need to execute Postgres commands by the vdb. I set the following properties of translator:
- supportsDirectQueryProcedure = "true"
- SupportsNativeQueries" ="true"
- SupportsDirectQueryProcedure" ="true"