Re: how to call a stored function on conflict

[Holger Jakobs <holger@xxxxxxxxxx>]

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@xxxxxxxxxx
+49 178 9759012 oder +49 2202 817157