So I wrote to a postgres database all the data and now I want to index it using a sql executor and have a timestamp on the index. I tried to use
"CREATE INDEX ct_table_data@Value('current_timestamp') on ct_table(data)
The big issue I have is it doesnt seem to time_stamp. I am useing the FME sql date function tool but no luck. Anyone have a good solution?
A few things come to mind:
1) SQLExecutor will execute for each feature and try to create an index on the table for each feature which will fail at the second feature as the field is indexed.
2) SQLExecutor is a transformer, so the table has to exist before the features arrive at the writer.
3) Why use the quotes in the @Value function?
4) I assume the current_timestamp attribute has the same value for each feature. If not this procedure will try to create an index for each feature using a different index name but on the same table attribute. This will generate errors.
For creation of an index I would either use the PostGres client interface or the "End SQLStatement" option.
I dont use the writer to do the indexing because I have multiple writers going and i want to make sure all are done writing before I index. i was really just trying to figure out the index statement that has a timestamp. and the @Value isnt looking at a attribute from the workbench but the SQL_DATE_FUNCTIONS build into sql executor if that makes any sense. So I use the executor after the writers put in the sql statements. i can get it all to work, but i want timestamps on the indes but cant figure out how to write that in the sqlexecutor
I would use a TimeStamper before I use the SQLExecutor in this case:
The use the _timestamp attribute to concatenate the SQL statement.
Make sure the timestamp does not contain characters, that are not allowed in the index name!