I'm using TIS PE v4.1.2.

I have a need to setup CDC (i.e. Talend CDC) on a set of tables within a PostgreSQL database (v8.4) at run-time. I have written shell scripts using the psql command and SQL code generated from Talend Studio that will setup the tables, trigger functions, and triggers. I currently invoke these scripts using the tSystem component.

This is sub-optimal because I would like to encapsulate all of this code within the Talend jobs, instead of maintaining them outside of Talend in shell scripts. If I can run this SQL within Talend (e.g. within sub-jobs) then I can use the versioning capability of Talend to better manage the process.

So, what I'm looking for is some guidance on what component(s) to use to just execute some SQL and get the result (e.g. to determine if the SQL command was successful). Typical operations that I'm trying to perform include,

- Check for the existence of a table or schema, e.g. using SELECT EXISTS(...)
    - The output of SELECT EXISTS() is a boolean ("f" for False and "t" for True) so I just need a way to execute the query and get the output, e.g. perhaps in a tJava component.
- Create a table (e.g. CREATE TABLE....   with constraints and what not)
    - I realize there is a tCreateTable component but it doesn't appear to accomodate setting up constraints (at least, not for PostgreSQL)?
    - tCreateTable also does not appear to support all the data types available in PostgreSQL?
- Alter the privileges on the table (e.g. GRANT ROLE...)
- Create trigger functions
- Create triggers on tables using the aforementioned trigger functions

I've been experimenting with tPostgreSQLRow and I can get it to execute the SQL, however, I cannot seem to get it to spit out the results from the query in a way I can construct logic around it, e.g. SELECT EXISTS(...) produces either "f" for False or "t" for True so I want to get the output from the query and either use a "Run-If" trigger on the tPostgreSQLRow component or product output that I can use in another component, e.g. tJava. I'm still somewhat new to Talend so I'm sure I'm overlooking something silly.

Anyhow, any pointers/suggestions would be much appreciated.