From: | Holger Jakobs <holger(at)jakobs(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: can't call function to delete the table |
Date: | 2019-10-14 17:01:38 |
Message-ID: | 9498a9ae-2025-1d63-2f0a-6ab1baaa1520@jakobs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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.
> 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 | Wells Oliver | 2019-10-14 19:23:34 | pg_upgradecluster with --link from 9.6 to 12 |
Previous Message | Pepe TD Vo | 2019-10-14 16:27:31 | can't call function to delete the table |