Hello,
Greenplum 4.1.2.4 (PG 8.2.3)
We are revising how we implement functions in order to better capture and handle fatal errors.
What we want to have happen,
1. is to have the fatal error captured,
2. logged to our processing table,
3. then have the function & psql exit with a non-zero return code, informing Informatica of the process failure.
I’m having several problems.
1. Under GP, any function called from psql is committed as a single transaction. I can handle the sql exception, and log the error to the table, and return a non-zero return code to psql.
2. I don’t know how to raise exception in the psql script. All I can imagine is to nest the function calls, having the outer function issue raise exception – but then the entire transaction is rolled back since the transactions are nested. so I loose the logging messages.
3. I’d like to have psql capture the return value and provide to a 2nd function which would then raise exeception. But don’t know and can’t tell from the doc if I can assign function output to psql metavariables.
4. Don’t know how to do anything with the psql metavariable ie if :last_return_code >= 16 then raise exception.
Suggestions would be appreciated.
Thanks
Doug Little