Re: how to call a stored function on conflict
thank you for your input.
even I create v_Ret as character varying and it still complained the same error.
create or replace FUNCTION ecisdrdm.pr_mig_stg_application_cdim (v_Ret OUT character varying )
ERROR: invalid input syntax for integer: "42P01" CONTEXT: PL/pgSQL function ecisdrdm.pr_mig_stg_application_cdim() line 39 at assignment SQL state: 22P02
I worked around and create errorlog table with errorcode declare as varchar(20) and recreate a function pr_write_error_log (v_ret text). Again the function combined and select function (pr_write_error_log) fine
But the function to merge(update)/insert on conflict is still not work even when I run the select function (pr_mig_stg_application_cdim), not sure this is corrected answer? It's one row affected.
but two tables are not update/insert any data from one to another.
stg_application_cdim:
application_cdim:
my "on conflict" is:
INSERT INTO ecisdrdm.application_cdim AS prod (prod.application_id,
prod.receipt_number,prod.init_frm_id, prod.frm_typ_id,
prod.sbmtd_dt_id, prod.crtd_user_id, prod.init_src_sys_id,
prod.init_svc_ctr_id, prod.mig_filename)
SELECT stg.application_id, stg.receipt_number, stg.init_frm_id, stg.frm_typ_id, stg.sbmtd_dt_id,
stg.crtd_user_id, stg.init_src_sys_id, stg.init_svc_ctr_id,
stg.mig_filename
FROM ecisdrdm.stg_application stg ON CONFLICT (application_id) DO UPDATE
SET ( prod.init_frm_id, prod.frm_typ_id,prod. sbmtd_dt_id, prod.crtd_user_id,
prod.init_src_sys_id, prod.init_svc_ctr_id, prod.mig_filename, prod.mig_modified_dt )
=
(SELECT stg.init_frm_id, stg.frm_typ_id, stg.sbmtd_dt_id, stg.crtd_user_id,
stg.init_src_sys_id, stg.init_svc_ctr_id,
stg.mig_filename, current_timestamp
FROM ecisdrdm.stg_application_cdim stg
WHERE prod.receipt_number = stg.receipt_number );
would you please tell me what the issue here?
thank you so much.
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success
On Tuesday, October 8, 2019, 03:29:59 PM EDT, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Oct 8, 2019 at 11:56 AM Pepe TD Vo <pepevo@yahoo.com> wrote:
ERROR: invalid input syntax for integer: "42P01" CONTEXT: PL/pgSQL function ecisdrdm.pr_mig_stg_application_cdim() line 41 at assignment SQL state: 22P02
You made an assumption that the error code was an integer. As the error message is pointing out one possible value of the error code is "42P01" which is not an integer. You need to fix your code to match reality - that the error code is an alphanumic.
may I know how to execute the stored function?
The fact that the function provoked an error means that it was executed.....
David J.
Вложения
В списке pgsql-admin по дате отправления: