so pretty much the definition of ETL, eh? Anyway, after (each iteration) I have loaded the data to a table, I should call a PostgreSQL function to process the loaded data:
CREATE OR REPLACE FUNCTION flush_the_bin(customer character varying) RETURNS integer AS --... RETURN some_code;
In SQL I would:
SELECT flush_the_bin('Customer Inc.');
I assume the call to the function is done with tPostgresqlRow that is connected to tFileList_1 with another iterate arrow. But how do I catch the return value from the called function? JB
You could call this procedure indeed with a tPostgresqlInput component this way. Because of the select you will get an ResultSet and thats what the input component depends on. The schema in this case has only one column (name does not matter) and the data type Integer.
Please avoid the ; at the end, this will cause problems in PostgreSQL. This example provides the file name of one file read be tFileList. You can easily adapt that for any other source of parameters.
My suggested new tPostgresqlInput component should be triggered in your case starting from tPostgresqlOutput_1 and with OnComponentOk.
I guess there might be a problem because as long as I know you cannot do any insert/updates within a select. We should think about this if it the case. At the moment I see only an ugly solution with tJDBCSP.
I use t<DB>Row to execute a select query, but after use a tParseRecordSet with t<DB>Row to parse the recordset.but how to retrieve column data from tParseRecordSet data i am getting error of column not found
