thank you for all input.
I made it work already.
many thanks again.
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.
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 17, 2019, 04:30:40 AM EDT, Holger Jakobs <holger@xxxxxxxxxx> wrote:
Hello Pepe,
RETURN NEW; is a statement only for functions which are declared with RETURNS TRIGGER and are used by triggers.
Even then no statement after the RETURN NEW; would ever be executed, so it remains unclear what v_Ret := SQLSTATE; i is supposed to do.
Regards,
Holger
Am 14.10.19 um 18:27 schrieb Pepe TD
Vo:
Hello
Experts,
Would
you please help me why I can't call the function to delete
table and insert data from another table?
Simple
deletion and insert from one to other are fine
DELETE FROM
bnft_hist_actn_ldim;
INSERT INTO
bnft_hist_actn_ldim
SELECT
stg.bnft_hist_actn_id, stg.bnft_hist_actn_src_cd,
stg.bnft_hist_actn_desc, stg.mig_filename
FROM
stg_bnft_hist_actn_ldim stg;
-----
but when I
put in the stored function, it's not working.
My stored
function script is:
CREATE OR REPLACE
FUNCTION pr_mig_stg_bnft_hist_actn_ldim(OUT
v_Ret integer)
AS $$
DECLARE
v_ErrorCode
varchar(32);
v_ErrorMsg
varchar(512);
v_Module
varchar(32) = 'pr_mig_stg_bnft_hist_actn_ldim';
BEGIN
DELETE FROM
bnft_hist_actn_ldim;
INSERT INTO
bnft_hist_actn_ldim
SELECT
stg.bnft_hist_actn_id,
stg.bnft_hist_actn_src_cd,
stg.bnft_hist_actn_desc,
stg.mig_filename
FROM stg_bnft_hist_actn_ldim
stg;
RETURN NEW;
v_Ret := SQLSTATE;
exception
when others then
v_ErrorCode
:= SQLSTATE;
v_ErrorMsg := SQLERRM;
v_Ret :=
v_ErrorCode;
PERFORM
pr_write_error_log ();
END;
$$
LANGUAGE 'plpgsql';
SELECT
pr_mig_bnft_hist_actn_ldim();
execute the store funtion no error
but two tables are the same and didn't delete and/or
insert any from one to other;
thank you for looking into it.
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.
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
