topic Re: Postgres stored procedure with dynamic inputs in Talend Studio

Postgres stored procedure with dynamic inputs

GeoffreyH — Fri, 15 Nov 2024 23:15:27 GMT

I've done some initial research around stored procedures for Postgres and seen mention of a tPostgresqlSP component in Forge but it seems this is no longer available.

I've heard mention of using tPostgresqlRow but am unsure how to get the output data from a tMap and add them to the SQL query.

How can I access the data from the output of a tMap to use in the SQL query of a

tPostgresqlRow component?

Is the only way to replace the tMap with a tJavaRow and set context variables?

Re: Postgres stored procedure with dynamic inputs

GeoffreyH — Wed, 02 Feb 2022 00:02:53 GMT

To be honest, I'm new to Talend and have no idea what you're saying here.

How can I get the output values of a tMap so as to use them in a tPostgresRow SQL query? Do I first have to set them via context properties?

Re: Postgres stored procedure with dynamic inputs

Anonymous — Tue, 15 Feb 2022 04:38:14 GMT

Hello,

You could call a procedure indeed with a tPostgresqlInput component via a select query. A new tPostgresqlInput component should be triggered in your case starting from tPostgresqlOutput_1 and with OnComponentOk.

tFileInputDelimited_1 --(row1)-> tMap_1 --(row2)-> tPostgresqlOutput_1

--------(iterate2)-> tPostgresqlInput_1 --(row2)-> tMap2 --(row3)-> ...

Best regards

Sabrina

Re: Postgres stored procedure with dynamic inputs

GeoffreyH — Tue, 15 Feb 2022 04:43:24 GMT

I managed to get it working. I discovered that when you link the output of a tMap to something, the data from the tMap is available under the name of the tMap output.

In my case, I'd named the tMap output AccessToken with some columns so in the tPostgreSQLRow I could then reference the data via AccessToken.columnName.

Simple when you think about it.