On 2 June 2017 at 04:16, PAWAN SHARMA <er.pawanshr0...@gmail.com> wrote: > 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';
The examples here https://www.postgresql.org/docs/9.5/static/sql-createfunction.html have a semi colon after 'END', is that the syntax error? > 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 Since this is a NOTICE maybe it's a red herring? the results of a CREATE IF NOT EXISTS or similar? -- Neil Anderson n...@postgrescompare.com https://www.postgrescompare.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
