From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Igor Neyman Sent: Friday, June 02, 2017 9:45 AM To: PAWAN SHARMA <er.pawanshr0...@gmail.com>; pgsql-general@postgresql.org Subject: Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.
Attention: This email was sent from someone outside of Perceptron. Always exercise caution when opening attachments or clicking links from unknown senders or when receiving unexpected emails. From: pgsql-general-ow...@postgresql.org<mailto:pgsql-general-ow...@postgresql.org> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of PAWAN SHARMA Sent: Friday, June 02, 2017 4:17 AM To: pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org> Subject: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool. Attention: This email was sent from someone outside of Perceptron. Always exercise caution when opening attachments or clicking links from unknown senders or when receiving unexpected emails. Hi All, I am migrating Oracle database into PostgreSQL using Ora2PG tool. So, I am facing one issue with trigger after generating script output of Oracle database. Oracle : CREATE OR REPLACE TRIGGER UPDATE_JOB_HISTORY AFTER UPDATE OF JOB_ID, DEPARTMENT_ID ON EMPLOYEES FOR EACH ROW BEGIN add_job_history(:old.employee_id, :old.hire_date, sysdate, :old.job_id, :old.department_id); END; / The script generated by Ora2PG tool. DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE; CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger AS $BODY$ BEGIN add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP, OLD.job_id, OLD.department_id); RETURN NEW; END $BODY$ LANGUAGE 'plpgsql'; CREATE TRIGGER update_job_history AFTER UPDATE ON employees FOR EACH ROW EXECUTE PROCEDURE trigger_fct_update_job_history(); when I try to run the above-generated script it will show below error. ERROR: syntax error at or near "add_job_history" LINE 4: add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMES... ^ NOTICE: relation "employees" does not exist, skipping Please Suggest or help to resolve it. -Pawan When you call a function inside PlSQL code and don’t care about returned value, then you do: PERFORM function_name(…). Otherwise you do: SELECT function_name(…) INTO your_variable; So: DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE; CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger AS $BODY$ BEGIN PERFORM add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP, OLD.job_id, OLD.department_id); RETURN NEW; END $BODY$ LANGUAGE 'plpgsql'; Regards, Igor Neyman P.S. Also you are missing semicolon (END;): DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE; CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger AS $BODY$ BEGIN PERFORM add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP, OLD.job_id, OLD.department_id); RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql';
