can't call function to delete the table

Поиск
Список
Период
Сортировка
От Pepe TD Vo
Тема can't call function to delete the table
Дата
Msg-id 292566289.866009.1571070451686@mail.yahoo.com
обсуждение исходный текст
Ответы Re: can't call function to delete the table  (Holger Jakobs <holger@jakobs.com>)
Re: can't call function to delete the table  (Luca Ferrari <fluca1978@gmail.com>)
Список pgsql-admin
Дерево обсуждения
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.
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

В списке pgsql-admin по дате отправления:

Предыдущее
От: Fernando Hevia
Дата:
Сообщение: Re: Strange behavior over WAN
Следующее
От: Holger Jakobs
Дата:
Сообщение: Re: can't call function to delete the table