From: Pepe TD Vo <pepevo@yahoo.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: how to call a stored function on conflict
Date: Wed, 9 Oct 2019 14:26:11 +0000 (UTC)
Message-ID: <1899900811.3794492.1570631172044@mail.yahoo.com> (raw)
In-Reply-To: <CAKFQuwZdNYvFnYgwSNx=9sHogpiBdEOE0AyLLzyX38oz6HpsMA@mail.gmail.com>
References: <1496660547.3525362.1570560989743.ref@mail.yahoo.com>
<1496660547.3525362.1570560989743@mail.yahoo.com>
<CAKFQuwZdNYvFnYgwSNx=9sHogpiBdEOE0AyLLzyX38oz6HpsMA@mail.gmail.com>
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.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 assignmentSQL 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.
Attachments:1570630244436blob.jpg (image/png, 11.8K) downloadview image1570630785668blob.jpg (image/png, 10.5K) downloadview image1570630921766blob.jpg (image/png, 101.3K) downloadview image1570631002967blob.jpg (image/png, 61.0K) downloadview image1570631073959blob.jpg (image/png, 7.4K) downloadview image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: pepevo@yahoo.com, david.g.johnston@gmail.com
Subject: Re: how to call a stored function on conflict
In-Reply-To: <1899900811.3794492.1570631172044@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox