how to call a stored function on conflict

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

how to call a stored function on conflict

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
Reply | Threaded
Open this post in threaded view
|

Re: how to call a stored function on conflict

Holger Jakobs-2

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: <a href="xmpp:holger@jakobs.com">xmpp:holger@...
<a href="tel:+491789759012">+49 178 9759012 oder <a href="tel:+492202817157">+49 2202 817157

Reply | Threaded
Open this post in threaded view
|

Re: how to call a stored function on conflict

David G Johnston
In reply to this post by Pepe TD Vo
On Tue, Oct 8, 2019 at 11:56 AM Pepe TD Vo <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: how to call a stored function on conflict

Pepe TD Vo
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 <[hidden email]> wrote:


On Tue, Oct 8, 2019 at 11:56 AM Pepe TD Vo <[hidden email]> 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.


1570630244436blob.jpg (16K) Download Attachment
1570630785668blob.jpg (14K) Download Attachment
1570630921766blob.jpg (138K) Download Attachment
1570631002967blob.jpg (83K) Download Attachment
1570631073959blob.jpg (10K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: how to call a stored function on conflict

Luca Ferrari-2
On Wed, Oct 9, 2019 at 4:26 PM Pepe TD Vo <[hidden email]> wrote:
> would you please tell me what the issue here?

May I suggest to log the running queries and see what is effectively
executing your function?

Luca


Reply | Threaded
Open this post in threaded view
|

Re: how to call a stored function on conflict

Pepe TD Vo
Thank you, I finally made it work yesterday afternoon.

thank you so much for everything.

v/r,

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 Thursday, October 10, 2019, 09:39:45 AM EDT, Luca Ferrari <[hidden email]> wrote:


On Wed, Oct 9, 2019 at 4:26 PM Pepe TD Vo <[hidden email]> wrote:

> would you please tell me what the issue here?


May I suggest to log the running queries and see what is effectively
executing your function?

Luca

Reply | Threaded
Open this post in threaded view
|

Re: how to call a stored function on conflict

Deepak Pahuja .
Hi,
How did you resolve it. Kindly share.

Thanks
Deepak


From: Pepe TD Vo <[hidden email]>
Sent: Thursday, October 10, 2019 7:41:53 PM
To: Luca Ferrari <[hidden email]>
Cc: David G. Johnston <[hidden email]>; Pgsql-admin <[hidden email]>
Subject: Re: how to call a stored function on conflict
 
Thank you, I finally made it work yesterday afternoon.

thank you so much for everything.

v/r,

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 Thursday, October 10, 2019, 09:39:45 AM EDT, Luca Ferrari <[hidden email]> wrote:


On Wed, Oct 9, 2019 at 4:26 PM Pepe TD Vo <[hidden email]> wrote:

> would you please tell me what the issue here?


May I suggest to log the running queries and see what is effectively
executing your function?

Luca

Reply | Threaded
Open this post in threaded view
|

Re: how to call a stored function on conflict

Pepe TD Vo
And someone from Europe advised me work around with my errorlog table and stored function.
"on conflict" I played with the query first and make sure it work before create a function.  And the function created with output parameter.

I moved my cubicle and the network log in into AWS is having an issue and I can't pull the script out.   Wait until I have access to AWS, I can send you a copy.

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 Friday, October 11, 2019, 12:27:05 AM EDT, Deepak Pahuja . <[hidden email]> wrote:


Hi,
How did you resolve it. Kindly share.

Thanks
Deepak


From: Pepe TD Vo <[hidden email]>
Sent: Thursday, October 10, 2019 7:41:53 PM
To: Luca Ferrari <[hidden email]>
Cc: David G. Johnston <[hidden email]>; Pgsql-admin <[hidden email]>
Subject: Re: how to call a stored function on conflict
 
Thank you, I finally made it work yesterday afternoon.

thank you so much for everything.

v/r,

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 Thursday, October 10, 2019, 09:39:45 AM EDT, Luca Ferrari <[hidden email]> wrote:


On Wed, Oct 9, 2019 at 4:26 PM Pepe TD Vo <[hidden email]> wrote:

> would you please tell me what the issue here?


May I suggest to log the running queries and see what is effectively
executing your function?

Luca