From: | Holger Jakobs <holger(at)jakobs(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: how to call a stored function on conflict |
Date: | 2019-10-08 19:29:03 |
Message-ID: | 725f0501-c6db-f343-aff6-30d70040ed07@jakobs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi Pepe,
the SQLSTATE is not an integer or bigint, so it cannot be stored in a
variable declared as bigint.
See https://www.postgresql.org/docs/current/errcodes-appendix.html
Regards,
Holger
Am 08.10.19 um 20:56 schrieb Pepe TD Vo:
> Hello experts,
>
> I have a store function on conflict to update and insert features
> information from one table to another table
>
> My code is:
>
> create or replace FUNCTION ecisdrdm.pr_mig_stg_application_cdim (v_Ret
> OUT bigint )
> as $$
> declare
> v_ErrorCode bigint;
> v_ErrorMsg varchar(512);
> v_Module varchar(32) =
> 'pr_mig_stg_application_cdim';
>
> begin
>
> ----
> -- MERGING: STG_APPLICATION_CDIM into APPLICATION_CDIM
> ----
>
> INSERT INTO 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
> );
> RETURN;
>
> ----
> -- Set the return code to 0
> ----
>
> v_Ret := SQLSTATE;
>
> ----
> -- Exception error handler
> ----
> exception
> when others then
> v_ErrorCode := SQLSTATE;
> v_ErrorMsg := SQLERRM;
> v_Ret := v_ErrorCode;
>
> ----
> -- Commit the record into the ErrorLog
> ----
> PERFORM pr_write_error_log( v_os_user, v_host, v_module, v_ErrorCode,
> v_ErrorMsg );
>
> ----
> -- Intentionally leaving the "commit" to application
> ----
> end;
> $$ language plpgsql;
>
> and my other function for pr_write_error_log is
>
> CREATE OR REPLACE FUNCTION ecisdrdm.pr_write_error_log (
> v_os_user character varying,
> v_host character varying,
> v_module character varying,
> v_errorcode integer,
> v_errormsg character varying)
> RETURNS void
> LANGUAGE 'plpgsql'
>
> COST 100
> VOLATILE
> AS $BODY$
>
> BEGIN
> START TRANSACTION;
> INSERT INTO ecisdrdm.errorlog(tstamp, os_user, host, module,
> errorcode, errormsg)
> VALUES
> (CURRENT_TIMESTAMP, CURRENT_USER, inet_server_addr(), v_module,
> v_ErrorCode, v_ErrorMsg );
>
> /* commit; */
> end;
>
> $BODY$;
>
> ALTER FUNCTION ecisdrdm.pr_write_error_log (character varying,
> character varying, character varying, integer, character varying)
> OWNER TO "PSmasteruser";
>
>
> they all created successfully.
>
> when I execute the function of pr_mig_stg_application_cdim as "select
> ecisdrdm.pr_mig_stg_application_cdim(); "
>
> ERROR: invalid input syntax for integer: "42P01" CONTEXT: PL/pgSQL
> function ecisdrdm.pr_mig_stg_application_cdim() line 41 at assignment
> SQL state: 22P02
>
> may I know how to execute the stored function? What did wrong on my
> queries?
>
> thank you for your help.
>
> **
> *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
--
Holger Jakobs, Bergisch Gladbach
instant messaging: xmpp:holger(at)jakobs(dot)com
+49 178 9759012 <tel:+491789759012> oder +49 2202 817157
<tel:+492202817157>
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2019-10-08 19:29:46 | Re: how to call a stored function on conflict |
Previous Message | Pepe TD Vo | 2019-10-08 18:56:29 | how to call a stored function on conflict |