tpostgresqlRow and return values?

One Star

tpostgresqlRow and return values?

Hello.
I'm trying to figure out how to catch a return value from of a
PostgreSQL function using tPostgresqlRow component. My job
is roughly:
tFileList_1 --(iterate1)-> tFileInputDelimited_1 --(row1)-> tMap_1 --(row2)-> tPostgresqlOutput_1

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
Seventeen Stars

Re: tpostgresqlRow and return values?

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.

SELECT flush_the_bin('" + ((String) globalMap.get("tFileList_1_CURRENT_FILE")) + "')

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.
Moderator

Re: tpostgresqlRow and return values?

Hi,
If you use t<DB>Row to execute a select query, you have to use a tParseRecordSet after t<DB>Row to parse the recordset.

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Seventeen Stars

Re: tpostgresqlRow and return values?

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.
One Star

Re: tpostgresqlRow and return values?

You could call this procedure indeed with a tPostgresqlInput component this way.

Thank you for the tip. I implemented it with tPostgresqlInput indeed.
My suggested new tPostgresqlInput component should be triggered in your case starting from tPostgresqlOutput_1 and with OnComponentOk.

I attached the tPostgresqlinput to tFileList with an iterate arrow and
that seemed to work also.
tFileList_1 --(iterate1)-> tFileInputDelimited_1 --(row1)-> tMap_1 --(row2)-> tPostgresqlOutput_1
    \
     --------(iterate2)-> tPostgresqlInput_1 --(row2)-> tMap2 --(row3)-> ...

I guess there might be a problem because as long as I know you cannot do any insert/updates within a select.

That isn't a problem.
Thank you again.
JB