topic Re: Postgresql using returning feature in Talend Studio

Postgresql using returning feature

Anonymous — Sat, 16 Nov 2024 03:11:39 GMT

Hello,

I need to return the primary key inserted in a table to use this as a foreign key in another table. I know that the command "returning" in postgresql do exactly what I want. Anyone knows how can I do this in Talend Open Studio? I'm trying to use the "returning" because other process will be running at the same time and consuming the same table, so I think that if a use the tDBOutput and after I use the tDBInput to get the last record inserted, maybe I get the record inserted by another process and not the current one. And also, how can I save this data to use at the end of the process?

Scenario:

I had a table that I will insert a first register in the beginning of the job, and need to use the pk of this record as a foreign key in another table that will be inserted at the end of the job.

Thank you!

Re: Postgresql using returning feature

Anonymous — Wed, 26 Feb 2020 06:07:52 GMT

Hi
Take a look at this page on stackoverflow, it introduces several ways to return the last inserted id.
https://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id

After you get the last inserted id, you can store it to a context variable for used later.

Regards
Shong

Re: Postgresql using returning feature

Anonymous — Wed, 26 Feb 2020 13:59:12 GMT

Hello @shong,

Thank for your reply.

However, what I asked was to how can I perform this in TOS.

After a few searches on the community, I founded one component that helped me with this. To help other people, follow bellow what I've done:

In tDBRow component, I inserted the sql script:

"INSERT INTO generic VALUES ((SELECT COALESCE(MAX(pk_generic) + 1, 1) FROM generic), 1, 1, 1) RETURNING pk_generic"

Get the returning statement result with the keyword "RETURNING" and parse this resultset with tParseRecordSet. After that I created a new global var with the value of this resultset.

If exist some other solution for this, please let me know.

Thank you!