Run postgresql stored procedure

Hi guys!
Could you please help me to figure out if it's possible or not to run a postgresql stored procedure within a Talend Job? And if yes I'll be very grateful for an example.
Thank you a lot in advance!!! Smiley Happy
22 REPLIES
Moderator

Re: Run postgresql stored procedure

Hi,
Usually, we use tXXXSP component to call stored procedure.
See the related KB article TalendHelpCenter:Calling a stored procedure or function.
So far, there is no available tpostgresqlSP official component in Talend product.
You can have a try on component tPostgresqlRow, if it can't meet your needs, please open a jira bug of DI project on Talend Bug Tracker for a new feature(tpostgresqlSP).
In addition that, I have searched a talend exchange and found a custom component tPostgresqlSP.
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.

Re: Run postgresql stored procedure

Hi Sabrina,
Thank you a lot for the response! I also thought about tPostgresqlRow component but I didn't see the custom SP component for PostgreSQL.
Kind regards,
Mariia
Moderator

Re: Run postgresql stored procedure

Hi,
Click the link I given you and you will find the custom component tPostgresqlSP, but the revision is compatible with: 2.3.1.
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.

Re: Run postgresql stored procedure

Yes, already done, thank you!!
I'll try it and will update this post if it works with 5.2Smiley Happy
Kind regards,
Mariia

Re: Run postgresql stored procedure

Unfortunately as I'm using Talend DI 5.2 I didn't manage to integrate mentioned custom component in my JOB. Instead I'm using tPostgresqlRow calling needed stored procedure within it.
Thank you a lot, Sabrina, for helping me Smiley Wink
Sincerely,
Mariia
Moderator

Re: Run postgresql stored procedure

Hi,
Thanks for your feedback, feel free post your issue on forum.
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.
One Star

Re: Run postgresql stored procedure

being a beginner............how to execute a sp in postgresql. i am not familiar with talend. as i am seeing the post postgresqlrow is good enough for procedure..if so can anyone tell me how to use this one.
Moderator

Re: Run postgresql stored procedure

Hi,
So far, there is no tPostgresqlSP in talend official components. You have to manage to integrate custom component http://www.talendforge.org/exchange/index.php?eid=41&product=tos&action=view&nav=1,1,1 so that it can compatible with your current talend build version.
Or take the solution by @Mariia_Kliuchnikova: using tPostgresqlRow calling needed stored procedure within it.
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.
One Star

Re: Run postgresql stored procedure

I am able to call the function by using the following query
" select my_test ( "+ ((Integer)globalMap.get("PropRow.in_var")) + ");"
Still i am not sure what is globalMap and what is PropRow. Any document regarding these is going to be really helpful for me.
Thanks,
Debashis
Moderator

Re: Run postgresql stored procedure

Hi,
globalMap is a hashtable which has been declared in Java code.
globalMap.put("key","value"), globalMap.get("key","value").
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.
One Star

Re: Run postgresql stored procedure

thanks really appreciate your support ....
what is PropRow then? and what are the other alternatives of proprow?? if any??
Thanks,
Debashis
Moderator

Re: Run postgresql stored procedure

Hi,
Would you mind giving us your current job design screenshot into forum so that I can get your situation more precisely to see if there is any other alternative solution for your case.
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.
One Star

Re: Run postgresql stored procedure

Here i am attaching the screenshot for my job.
In the component of tpostgresql1 i have mentioned the function query as i have discussed with you earlier.
what is PropRow then? and what are the other alternatives of proprow?? if any??
Moderator

Re: Run postgresql stored procedure

Hi,
It seems the screenshots are missing.
Please click on "post reply" (not through the quick post") and pay more attention on the limitation of Image Upload Box:20 images per post, each image must be less then 1024x768 pixels and 200 KB(.png is preferred).
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.
One Star

Re: Run postgresql stored procedure

hi Sabrina
I am able to complete the job with the requirement.
Now in a second job i want to update one table like
update pricing_group set pricing_group_type='DEBASHIS' where ver_num=10
as you see i am updating the field with all capital letters, now i want to use the system routine
StringHandling.DOWNCASE to make it to lower case.
in my job i am only using a table metadata that's it. How to use the routine for this purpose.
One Star

Re: Run postgresql stored procedure

Hi sabrina
please treat this one as urgent.
I want to be my database as it is before 10 hours. Is it possible from talend to do so and if it is possible then how to do it.
One Star

Re: Run postgresql stored procedure

Hi people,
I am new to this and i was making a update procedure, But I am constantly getting syntax error for the set line, and dont know what is wrong. This is the code: 
CREATE FUNCTION update_sba_sales("$IncrementID" numeric, "$EntryID" numeric, "$Soa_selectorID" character varying) RETURNS void AS
$BODY$BEGIN
update sba_sales
set sales_gc= sales_gc*$IncrementID
where entryid=$EntryID 
and soa=$Soa_selectorID;
END;$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF;
ALTER FUNCTION public.update_sba_sales(numeric, numeric, character varying)
  OWNER TO mstrwh;
GRANT EXECUTE ON FUNCTION public.update_sba_sales(numeric, numeric, character varying) TO public;
Four Stars

Re: Run postgresql stored procedure

You are getting error on Pgsql client or in Talend job? 
Vaibhav
One Star

Re: Run postgresql stored procedure

Hello guys!
Could you please help me to figure out if it's possible or not to create stored procedure within a Talend Job? and i  am using MySQL server as back end ,in my job i used tsqlscriptparser component is iterated to tmysqlrow component and i fail to create stored procedure in MySQL DB,please help me, I'll be very grateful for an example.
Thank you a lot in advance!!! Smiley Happy
Moderator

Re: Run postgresql stored procedure

Hi,
if it's possible or not to create stored procedure within a Talend Job?

Do you want to call your stored procedure in talend job? tMysqlSP?
Could you please stick to one topic:https://www.talendforge.org/forum/viewtopic.php?id=45713 so that we can pay individual attention to it.
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.
Six Stars

Re: Run postgresql stored procedure

Hi,

how do you call stored procedure inside the mentioned component tPostgresqlRow?

Moderator

Re: Run postgresql stored procedure

Hello,

You can use tPostgresqlRow for call any SQL code, do it same as PostgreSQL console - plain SQL code.

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.