pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcan't call function to delete the table
4+ messages / 3 participants
[nested] [flat]
* can't call function to delete the table
@ 2019-10-14 16:27 Pepe TD Vo <pepevo@yahoo.com>
2019-10-14 17:01 ` Re: can't call function to delete the table Holger Jakobs <holger@jakobs.com>
2019-10-16 07:27 ` Re: can't call function to delete the table Luca Ferrari <fluca1978@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Pepe TD Vo @ 2019-10-14 16:27 UTC (permalink / raw)
To: 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_filenameFROM 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: can't call function to delete the table
2019-10-14 16:27 can't call function to delete the table Pepe TD Vo <pepevo@yahoo.com>
@ 2019-10-14 17:01 ` Holger Jakobs <holger@jakobs.com>
2019-10-17 13:28 ` Re: can't call function to delete the table Pepe TD Vo <pepevo@yahoo.com>
1 sibling, 1 reply; 4+ messages in thread
From: Holger Jakobs @ 2019-10-14 17:01 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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@jakobs.com
+49 178 9759012 <tel:+491789759012> oder +49 2202 817157
<tel:+492202817157>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: can't call function to delete the table
2019-10-14 16:27 can't call function to delete the table Pepe TD Vo <pepevo@yahoo.com>
2019-10-14 17:01 ` Re: can't call function to delete the table Holger Jakobs <holger@jakobs.com>
@ 2019-10-17 13:28 ` Pepe TD Vo <pepevo@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Pepe TD Vo @ 2019-10-17 13:28 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org, Holger Jakobs <holger@jakobs.com>
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.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@jakobs.com> 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. 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@jakobs.com
+49 178 9759012 oder +49 2202 817157
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: can't call function to delete the table
2019-10-14 16:27 can't call function to delete the table Pepe TD Vo <pepevo@yahoo.com>
@ 2019-10-16 07:27 ` Luca Ferrari <fluca1978@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Luca Ferrari @ 2019-10-16 07:27 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; +Cc: pgsql-admin
On Mon, Oct 14, 2019 at 6:27 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
> 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;
>
As others have already pointed out, you seems to return a trigger
tuple from a normal function, so first step is to remove the "RETURN
NEW". Then I would add a couple of RAISE statements because it sounds
me strange that the function is invoked without producing any error
nor results.
Last, I would change the DELETE with a TRUNCATE, assuming you are not
inserting the very same quantity of rows on the next step.
Luca
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-10-17 13:28 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-10-14 16:27 can't call function to delete the table Pepe TD Vo <pepevo@yahoo.com>
2019-10-14 17:01 ` Holger Jakobs <holger@jakobs.com>
2019-10-17 13:28 ` Pepe TD Vo <pepevo@yahoo.com>
2019-10-16 07:27 ` Luca Ferrari <fluca1978@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox