partitioned table
Pepe TD Vo <[email protected]> Thu, 9 Jan 2020 17:06:43 +0000 (UTC)
| Newsgroups | gmane.comp.db.postgresql.admin,gmane.comp.db.postgresql.general |
|---|---|
| Message-ID | <[email protected]> |
------=_Part_5959266_2103167348.1578589603206
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
I have table and partitioned for year year like this:
CREATE TABLE ecisdrdm.bnft_curr_fact (bnft_fact_id numeric(38), bene_cntry=
_of_brth_id=C2=A0 numeric(38), bene_cntry_of_rsdc_id numeric(38), bene_cnt=
ry_of_ctznshp_id numeric(38), frm_id numeric(38), svc_ctr_id numeric(38)=
, actn_dt_in_id numeric(38), actn_tm_in_id numeric(38), src_sys_id numer=
ic(38), bnft_hist_actn_id numeric(38), bene_id numeric(38), bene_end_dt_i=
d numeric(38), petnr_app_id numeric(38), atty_id numeric(38), uscis_emp_=
id numeric(38), application_id numeric(38) default -1000000, rmtr_id num=
eric(38), prpr_id numeric(38), mig_filename varchar(80), mig_insert_dt t=
imestamp, mig_modified_dt timestamp) partition by range (actn_dt_in_id)TAB=
LESPACE ecisdrdm_data;
CREATE INDEX bnftn_fact_frmid_bmx1 ON ecisdrdm.bnft_curr_fact (frm_id)TABLE=
SPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_attyid_bmx1 ON ecisdrdm.bnft_curr_fact (atty_id)TAB=
LESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_applicatiid_bti1 ON ecisdrdm.bnft_curr_fact (applic=
atiON_id)TABLESPACE ecisdrdm_index;
CREATE INDEX src_sys_id_actn_dt_bmx1 ON ecisdrdm.bnft_curr_fact (src_sys_id=
, actn_dt_in_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_bnftfactid_bti1 ON ecisdrdm.bnft_curr_fact (bnft_fa=
ct_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_actndtinid_bmx1 ON ecisdrdm.bnft_curr_fact (actn_dt=
_in_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_coposit3_bmx1 ON ecisdrdm.bnft_curr_fact (svc_ctr_i=
d, uscis_emp_id)TABLESPACE ecisdrdm_index;
CREATE INDEX src_sys_id_actn_dt_saa ON ecisdrdm.bnft_curr_fact (src_sys_id,=
actn_dt_in_id, applicatiON_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_beneid_bmx1 ON ecisdrdm.bnft_curr_fact (bene_id)TAB=
LESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_petnrappid_bti1 ON ecisdrdm.bnft_curr_fact (petnr_a=
pp_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_uscisempid_bmx1 ON ecisdrdm.bnft_curr_fact (uscis_e=
mp_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_bnfhisactid_bmx1 ON ecisdrdm.bnft_curr_fact (bnft_h=
ist_actn_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_src_sys_id_bmx1 ON ecisdrdm.bnft_curr_fact (src_sys=
_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_benenddtid_bmx1 ON ecisdrdm.bnft_curr_fact (bene_en=
d_dt_id)TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_prprid_bmx1 ON ecisdrdm.bnft_curr_fact (prpr_id)TAB=
LESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_svcctrid_bmx1 ON ecisdrdm.bnft_curr_fact (svc_ctr_i=
d)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_benctrysdcid_bmx1 ON ecisdrdm.bnft_curr_fact (bene_=
cntry_of_rsdc_id)
TABLESPACE ecisdrdm_index;
CREATE INDEX bnftn_fact_benctrybrtid_bmx1 ON ecisdrdm.bnft_curr_fact (bene_=
cntry_of_brth_id)
TABLESPACE ecisdrdm_index;
as same as stg_bnft_curr_fact table, it's partitioned too.when I manually m=
ocking the data into both tables are fine and when I run the procedure, I g=
et errorcode: 42P10 MSG: thereis no unique or exclusion constraint matching=
on the CONFLICT specification
the procedure is=C2=A0
CREATE OR REPLACE FUNCTION ecisdrdm.pr_mig_stg_bnft_curr_fact( OUT v_ret te=
xt)=C2=A0 =C2=A0 RETURNS text=C2=A0 =C2=A0 LANGUAGE 'plpgsql'
=C2=A0 =C2=A0 COST 100=C2=A0 =C2=A0 VOLATILE=C2=A0AS $BODY$=C2=A0DECLARE=C2=
=A0 v_module=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0text =3D 'pr_mig_stg_b=
nft_curr_fact '; host text =3D inet_server_addr(); errorcode=C2=A0 =C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0text; errormsg =C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0text; errormsg_detail=C2=A0 text;=C2=A0 =
=C2=A0 errormsg_hint text; BEGIN
------ MERGING: STG_BNFT_CURR_FACT into BNFT_CURR_FACT----
INSERT INTO ecisdrdm.bnft_curr_fact AS prod (bnft_fact_id, bene_cntry_of_br=
th_id, bene_cntry_of_rsdc_id,=C2=A0 bene_cntry_of_ctznshp_id, frm_id, svc_c=
tr_id, actn_dt_in_id, actn_tm_in_id, src_sys_id,=C2=A0 bnft_hist_actn_id, b=
ene_id, bene_end_dt_id, petnr_app_id, atty_id, uscis_emp_id, application_id=
,=C2=A0 rmtr_id, prpr_id, mig_filename)SELECT stg.bnft_fact_id, stg.bene_cn=
try_of_brth_id, stg.bene_cntry_of_rsdc_id,=C2=A0 stg.bene_cntry_of_ctznshp_=
id, stg.frm_id, stg.svc_ctr_id, stg.actn_dt_in_id, stg.actn_tm_in_id, stg.s=
rc_sys_id,=C2=A0 stg.bnft_hist_actn_id, stg.bene_id, stg.bene_end_dt_id, st=
g.petnr_app_id, stg.atty_id, stg.uscis_emp_id, stg.application_id,=C2=A0 st=
g.rmtr_id, stg.prpr_id, stg.mig_filenameFROM ecisdrdm.stg_bnft_curr_fact st=
gON CONFLICT ("bnft_fact_id") DO UPDATE=C2=A0SET (bnft_fact_id, bene_cntry_=
of_brth_id, bene_cntry_of_rsdc_id,=C2=A0 bene_cntry_of_ctznshp_id, frm_id, =
svc_ctr_id, actn_dt_in_id, actn_tm_in_id, src_sys_id,=C2=A0 bnft_hist_actn_=
id, bene_id, bene_end_dt_id, petnr_app_id, atty_id, uscis_emp_id, applicati=
on_id,=C2=A0 rmtr_id, prpr_id, mig_filename, mig_modified_dt)=3D=C2=A0(SELE=
CT stg.bnft_fact_id, stg.bene_cntry_of_brth_id, stg.bene_cntry_of_rsdc_id,=
=C2=A0 stg.bene_cntry_of_ctznshp_id, stg.frm_id, stg.svc_ctr_id, stg.actn_d=
t_in_id, stg.actn_tm_in_id, stg.src_sys_id,=C2=A0 stg.bnft_hist_actn_id, st=
g.bene_id, stg.bene_end_dt_id, stg.petnr_app_id, stg.atty_id, stg.uscis_emp=
_id, stg.application_id,=C2=A0 stg.rmtr_id, stg.prpr_id, stg.mig_filename, =
current_timestampFROM ecisdrdm.stg_bnft_curr_fact stgWHERE prod.application=
_id =3D stg.application_id);
=C2=A0 =C2=A0----=C2=A0 =C2=A0-- Set return to "Success" for pr_merge_stagi=
ng_tables function=C2=A0 =C2=A0---- v_ret :=3D 'Success';=C2=A0=C2=A0
EXCEPTION WHEN OTHERS THEN=C2=A0 GET STACKED DIAGNOSTICS=C2=A0 errormsg =
=3D MESSAGE_TEXT,=C2=A0 =C2=A0 =C2=A0 =C2=A0 errormsg_detail =3D PG_EXCEPTI=
ON_DETAIL,=C2=A0 =C2=A0 =C2=A0 =C2=A0 errormsg_hint =3D PG_EXCEPTION_HINT;=
=C2=A0=C2=A0 errorcode :=3D SQLSTATE;=C2=A0 =C2=A0 =C2=A0 =C2=A0 v_ret :=3D=
concat('ERROR - FUNC: ' || v_module || ' ERRORCODE: ', errorcode, ' MSG: '=
|| errormsg || ' ', errormsg_detail || ' ', errormsg_hint);
-- NOTE: Only writes to errorlog table if function is called directly -- I=
f called through the pr_merge_staging_tables function the exception is rais=
ed and caught there instead PERFORM ecisdrdm.pr_write_error_log( CURRENT_US=
ER, host, v_module, errorcode, v_ret );
end;$BODY$;
when I select the table from pgadmin tool, it's not opened and popped up wi=
th a blank window with title "index out or range" I do have index on it.
regards,
Bach-Nga
No one in this world is pure and perfect.=C2=A0 If you avoid people for the=
ir 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 t=
o 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
------=_Part_5959266_2103167348.1578589603206
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
<html><head></head><body><div class=3D"ydpc8555dafyahoo-style-wrap" style=
=3D"font-family: verdana, helvetica, sans-serif; font-size: 16px;"><div dir=
=3D"ltr" data-setdir=3D"false" style=3D"font-family: verdana, helvetica, sa=
ns-serif; font-size: 16px;">I have table and partitioned for year year like=
this:</div><div dir=3D"ltr" data-setdir=3D"false" style=3D"font-family: ve=
rdana, helvetica, sans-serif; font-size: 16px;"><br></div><div dir=3D"ltr" =
data-setdir=3D"false" style=3D""><div style=3D"font-family: verdana, helvet=
ica, sans-serif; font-size: 16px;"><div>CREATE TABLE ecisdrdm.bnft_curr_fac=
t</div><div><span style=3D"white-space: pre-wrap;">=09</span>(bnft_fact_id =
<span style=3D"white-space: pre-wrap;">=09=09=09 </span>numeric(38),</div>=
<div><span style=3D"white-space: pre-wrap;">=09</span>bene_cntry_of_brth_id=
<span style=3D"white-space: pre-wrap;"> </span>numeric(38),</d=
iv><div><span style=3D"white-space: pre-wrap;">=09</span>bene_cntry_of_rsdc=
_id <span style=3D"white-space: pre-wrap;">=09 </span>numeric(38),</div><d=
iv><span style=3D"white-space: pre-wrap;">=09</span>bene_cntry_of_ctznshp_i=
d <span style=3D"white-space: pre-wrap;">=09 </span>numeric(38),</div><div=
><span style=3D"white-space: pre-wrap;">=09</span>frm_id <span style=3D"whi=
te-space: pre-wrap;">=09=09=09=09 </span>numeric(38),</div><div><span styl=
e=3D"white-space: pre-wrap;">=09</span>svc_ctr_id <span style=3D"white-spac=
e: pre-wrap;">=09=09=09 </span>numeric(38),</div><div><span style=3D"white=
-space: pre-wrap;">=09</span>actn_dt_in_id <span style=3D"white-space: pre-=
wrap;">=09=09=09 </span>numeric(38),</div><div><span style=3D"white-space:=
pre-wrap;">=09</span>actn_tm_in_id <span style=3D"white-space: pre-wrap;">=
=09=09=09 </span>numeric(38),</div><div><span style=3D"white-space: pre-wr=
ap;">=09</span>src_sys_id <span style=3D"white-space: pre-wrap;">=09=09=09 =
</span>numeric(38),</div><div><span style=3D"white-space: pre-wrap;">=09</=
span>bnft_hist_actn_id <span style=3D"white-space: pre-wrap;">=09=09 </spa=
n>numeric(38),</div><div><span style=3D"white-space: pre-wrap;">=09</span>b=
ene_id <span style=3D"white-space: pre-wrap;">=09=09=09=09 </span>numeric(=
38),</div><div><span style=3D"white-space: pre-wrap;">=09</span>bene_end_dt=
_id <span style=3D"white-space: pre-wrap;">=09=09 </span>numeric(38),</div=
><div><span style=3D"white-space: pre-wrap;">=09</span>petnr_app_id <span s=
tyle=3D"white-space: pre-wrap;">=09=09=09 </span>numeric(38),</div><div><s=
pan style=3D"white-space: pre-wrap;">=09</span>atty_id <span style=3D"white=
-space: pre-wrap;">=09=09=09=09 </span>numeric(38),</div><div><span style=
=3D"white-space: pre-wrap;">=09</span>uscis_emp_id <span style=3D"white-spa=
ce: pre-wrap;">=09=09=09 </span>numeric(38),</div><div><span style=3D"whit=
e-space: pre-wrap;">=09</span>application_id <span style=3D"white-space: pr=
e-wrap;">=09=09=09 </span>numeric(38) default -1000000,</div><div><span st=
yle=3D"white-space: pre-wrap;">=09</span>rmtr_id <span style=3D"white-space=
: pre-wrap;">=09=09=09=09 </span>numeric(38),</div><div><span style=3D"whi=
te-space: pre-wrap;">=09</span>prpr_id <span style=3D"white-space: pre-wrap=
;">=09=09=09=09 </span>numeric(38),</div><div><span style=3D"white-space: =
pre-wrap;">=09</span>mig_filename <span style=3D"white-space: pre-wrap;">=
=09=09=09 </span>varchar(80),</div><div><span style=3D"white-space: pre-wr=
ap;">=09</span>mig_insert_dt <span style=3D"white-space: pre-wrap;">=09=09=
=09 </span>timestamp,</div><div><span style=3D"white-space: pre-wrap;">=09=
</span>mig_modified_dt <span style=3D"white-space: pre-wrap;">=09=09 </spa=
n>timestamp</div><div>) partition by range (actn_dt_in_id)</div><div>TABLES=
PACE ecisdrdm_data;</div><div><br></div><div><br></div><div>CREATE INDEX bn=
ftn_fact_frmid_bmx1 ON ecisdrdm.bnft_curr_fact (frm_id)</div><div>TABLESPAC=
E ecisdrdm_index;</div><div><br></div><div>CREATE INDEX bnftn_fact_attyid_b=
mx1 ON ecisdrdm.bnft_curr_fact (atty_id)</div><div>TABLESPACE ecisdrdm_inde=
x;</div><div><br></div><div>CREATE INDEX bnftn_fact_applicatiid_bti1 ON eci=
sdrdm.bnft_curr_fact (applicatiON_id)</div><div>TABLESPACE ecisdrdm_index;<=
/div><div><br></div><div>CREATE INDEX src_sys_id_actn_dt_bmx1 ON ecisdrdm.b=
nft_curr_fact (src_sys_id, actn_dt_in_id)</div><div>TABLESPACE ecisdrdm_ind=
ex;</div><div><br></div><div>CREATE INDEX bnftn_fact_bnftfactid_bti1 ON eci=
sdrdm.bnft_curr_fact (bnft_fact_id)</div><div>TABLESPACE ecisdrdm_index;</d=
iv><div><br></div><div>CREATE INDEX bnftn_fact_actndtinid_bmx1 ON ecisdrdm.=
bnft_curr_fact (actn_dt_in_id)</div><div>TABLESPACE ecisdrdm_index;</div><d=
iv><br></div><div>CREATE INDEX bnftn_fact_coposit3_bmx1 ON ecisdrdm.bnft_cu=
rr_fact (svc_ctr_id, uscis_emp_id)</div><div>TABLESPACE ecisdrdm_index;</di=
v><div><br></div><div>CREATE INDEX src_sys_id_actn_dt_saa ON ecisdrdm.bnft_=
curr_fact (src_sys_id, actn_dt_in_id, applicatiON_id)</div><div>TABLESPACE =
ecisdrdm_index;</div><div><br></div><div>CREATE INDEX bnftn_fact_beneid_bmx=
1 ON ecisdrdm.bnft_curr_fact (bene_id)</div><div>TABLESPACE ecisdrdm_index;=
</div><div><br></div><div>CREATE INDEX bnftn_fact_petnrappid_bti1 ON ecisdr=
dm.bnft_curr_fact (petnr_app_id)</div><div>TABLESPACE ecisdrdm_index;</div>=
<div><br></div><div>CREATE INDEX bnftn_fact_uscisempid_bmx1 ON ecisdrdm.bnf=
t_curr_fact (uscis_emp_id)</div><div>TABLESPACE ecisdrdm_index;</div><div><=
br></div><div>CREATE INDEX bnftn_fact_bnfhisactid_bmx1 ON ecisdrdm.bnft_cur=
r_fact (bnft_hist_actn_id)</div><div>TABLESPACE ecisdrdm_index;</div><div><=
br></div><div>CREATE INDEX bnftn_fact_src_sys_id_bmx1 ON ecisdrdm.bnft_curr=
_fact (src_sys_id)</div><div>TABLESPACE ecisdrdm_index;</div><div><br></div=
><div>CREATE INDEX bnftn_fact_benenddtid_bmx1 ON ecisdrdm.bnft_curr_fact (b=
ene_end_dt_id)</div><div>TABLESPACE ecisdrdm_index;</div><div><br></div><di=
v>CREATE INDEX bnftn_fact_prprid_bmx1 ON ecisdrdm.bnft_curr_fact (prpr_id)<=
/div><div>TABLESPACE ecisdrdm_index;</div><div><br></div><div>CREATE INDEX =
bnftn_fact_svcctrid_bmx1 ON ecisdrdm.bnft_curr_fact (svc_ctr_id)<br></div><=
div>TABLESPACE ecisdrdm_index;</div><div><br></div><div>CREATE INDEX bnftn_=
fact_benctrysdcid_bmx1 ON ecisdrdm.bnft_curr_fact (bene_cntry_of_rsdc_id)<b=
r></div><div>TABLESPACE ecisdrdm_index;</div><div><br></div><div>CREATE IND=
EX bnftn_fact_benctrybrtid_bmx1 ON ecisdrdm.bnft_curr_fact (bene_cntry_of_b=
rth_id)<br></div><div>TABLESPACE ecisdrdm_index;</div><div><br></div></div>=
<div style=3D"font-family: verdana, helvetica, sans-serif; font-size: 16px;=
"><div dir=3D"ltr" data-setdir=3D"false">as same as stg_bnft_curr_fact tabl=
e, it's partitioned too.</div></div><div dir=3D"ltr" data-setdir=3D"false" =
style=3D"font-family: verdana, helvetica, sans-serif; font-size: 16px;">whe=
n I manually mocking the data into both tables are fine and when I run the =
procedure, I get errorcode: 42P10 MSG: there
is no unique or exclusion constraint matching on the CONFLICT specification=
</div><div dir=3D"ltr" data-setdir=3D"false" style=3D"font-family: verdana,=
helvetica, sans-serif; font-size: 16px;"><br></div><div dir=3D"ltr" data-s=
etdir=3D"false" style=3D"font-family: verdana, helvetica, sans-serif; font-=
size: 16px;">the procedure is </div><div dir=3D"ltr" data-setdir=3D"fa=
lse" style=3D"font-family: verdana, helvetica, sans-serif; font-size: 16px;=
"><br></div><div dir=3D"ltr" data-setdir=3D"false" style=3D"font-family: ve=
rdana, helvetica, sans-serif; font-size: 16px;"><div><div>CREATE OR REPLACE=
FUNCTION ecisdrdm.pr_mig_stg_bnft_curr_fact(</div><div><span style=3D"whit=
e-space: pre-wrap;">=09</span>OUT v_ret text)</div><div> RETUR=
NS text</div><div> LANGUAGE 'plpgsql'</div><div><br></div><div=
> COST 100</div><div> VOLATILE </div><div>AS=
$BODY$ </div><div>DECLARE </div><div><span style=3D"white-space:=
pre-wrap;">=09</span>v_module text=
=3D 'pr_mig_stg_bnft_curr_fact ';</div><div><span style=3D"white-space: pr=
e-wrap;">=09</span>host<span style=3D"white-space: pre-wrap;">=09=09=09 </s=
pan>text =3D inet_server_addr();</div><div><span style=3D"white-space: pre-=
wrap;">=09</span>errorcode =
text;</div><div><span style=3D"white-space: pre-wrap;">=09</spa=
n>errormsg <span style=3D"white-space: pre-wrap;">=09=09 &=
nbsp; </span>text;</div><div><span style=3D"white-space: p=
re-wrap;">=09</span>errormsg_detail <span style=3D"white-space: pre-wr=
ap;"> </span>text;</div><div> errormsg_hint<span style=3D"whit=
e-space: pre-wrap;">=09 </span>text;</div><div><span style=3D"white-space: =
pre-wrap;">=09</span></div><div>BEGIN</div><div><br></div><div>----</div><d=
iv>-- MERGING: STG_BNFT_CURR_FACT into BNFT_CURR_FACT</div><div>----</div><=
div><br></div><div>INSERT INTO ecisdrdm.bnft_curr_fact AS prod (bnft_fact_i=
d, bene_cntry_of_brth_id, bene_cntry_of_rsdc_id, </div><div><span styl=
e=3D"white-space: pre-wrap;">=09</span>bene_cntry_of_ctznshp_id, frm_id, sv=
c_ctr_id, actn_dt_in_id, actn_tm_in_id, src_sys_id, </div><div><span s=
tyle=3D"white-space: pre-wrap;">=09</span>bnft_hist_actn_id, bene_id, bene_=
end_dt_id, petnr_app_id, atty_id, uscis_emp_id, application_id, </div>=
<div><span style=3D"white-space: pre-wrap;">=09</span>rmtr_id, prpr_id, mig=
_filename)</div><div>SELECT stg.bnft_fact_id, stg.bene_cntry_of_brth_id, st=
g.bene_cntry_of_rsdc_id, </div><div><span style=3D"white-space: pre-wr=
ap;">=09</span>stg.bene_cntry_of_ctznshp_id, stg.frm_id, stg.svc_ctr_id, st=
g.actn_dt_in_id, stg.actn_tm_in_id, stg.src_sys_id, </div><div><span s=
tyle=3D"white-space: pre-wrap;">=09</span>stg.bnft_hist_actn_id, stg.bene_i=
d, stg.bene_end_dt_id, stg.petnr_app_id, stg.atty_id, stg.uscis_emp_id, stg=
.application_id, </div><div><span style=3D"white-space: pre-wrap;">=09=
</span>stg.rmtr_id, stg.prpr_id, stg.mig_filename</div><div>FROM ecisdrdm.s=
tg_bnft_curr_fact stg</div><div>ON CONFLICT ("bnft_fact_id") DO UPDATE =
;</div><div>SET (bnft_fact_id, bene_cntry_of_brth_id, bene_cntry_of_rsdc_id=
, </div><div><span style=3D"white-space: pre-wrap;">=09</span>bene_cnt=
ry_of_ctznshp_id, frm_id, svc_ctr_id, actn_dt_in_id, actn_tm_in_id, src_sys=
_id, </div><div><span style=3D"white-space: pre-wrap;">=09</span>bnft_=
hist_actn_id, bene_id, bene_end_dt_id, petnr_app_id, atty_id, uscis_emp_id,=
application_id, </div><div><span style=3D"white-space: pre-wrap;">=09=
</span>rmtr_id, prpr_id, mig_filename, mig_modified_dt)</div><div>=3D =
</div><div>(SELECT stg.bnft_fact_id, stg.bene_cntry_of_brth_id, stg.bene_cn=
try_of_rsdc_id, </div><div><span style=3D"white-space: pre-wrap;">=09<=
/span>stg.bene_cntry_of_ctznshp_id, stg.frm_id, stg.svc_ctr_id, stg.actn_dt=
_in_id, stg.actn_tm_in_id, stg.src_sys_id, </div><div><span style=3D"w=
hite-space: pre-wrap;">=09</span>stg.bnft_hist_actn_id, stg.bene_id, stg.be=
ne_end_dt_id, stg.petnr_app_id, stg.atty_id, stg.uscis_emp_id, stg.applicat=
ion_id, </div><div><span style=3D"white-space: pre-wrap;">=09</span>st=
g.rmtr_id, stg.prpr_id, stg.mig_filename, current_timestamp</div><div>FROM =
ecisdrdm.stg_bnft_curr_fact stg</div><div>WHERE prod.application_id =3D stg=
.application_id</div><div>);</div><div><br></div><div> ----</di=
v><div> -- Set return to "Success" for pr_merge_staging_tables =
function</div><div> ----</div><div><span style=3D"white-space: =
pre-wrap;">=09</span>v_ret :=3D 'Success'; </div><div><br></div>=
<div>EXCEPTION</div><div><span style=3D"white-space: pre-wrap;">=09</span>W=
HEN OTHERS THEN</div><div> <span style=3D"white-space: pre-wrap;">=09=
</span>GET STACKED DIAGNOSTICS </div><div><span style=3D"white-space: =
pre-wrap;">=09=09</span>errormsg =3D MESSAGE_TEXT,</div><div> =
errormsg_detail =3D PG_EXCEPTION_DETAIL,</div><div> &nb=
sp; errormsg_hint =3D PG_EXCEPTION_HINT; </div><di=
v><span style=3D"white-space: pre-wrap;">=09=09</span>errorcode :=3D SQLSTA=
TE;</div><div> v_ret :=3D concat('ERROR - FUNC: =
' || v_module || ' ERRORCODE: ', errorcode, ' MSG: ' || errormsg || ' ', er=
rormsg_detail || ' ', errormsg_hint);</div><div><br></div><div><span style=
=3D"white-space: pre-wrap;">=09</span>-- NOTE: Only writes to errorlog tabl=
e if function is called directly</div><div><span style=3D"white-space: pre-=
wrap;">=09</span>-- If called through the pr_merge_staging_tables function =
the exception is raised and caught there instead</div><div><span style=3D"w=
hite-space: pre-wrap;">=09</span>PERFORM ecisdrdm.pr_write_error_log( CURRE=
NT_USER, host, v_module, errorcode, v_ret );</div><div><br></div><div>end;<=
/div><div>$BODY$;</div><div><br></div></div><br></div><div dir=3D"ltr" data=
-setdir=3D"false" style=3D"font-family: verdana, helvetica, sans-serif; fon=
t-size: 16px;"><span><span style=3D"color: rgb(0, 0, 0); font-family: Verda=
na, Geneva, Helvetica, Arial, sans-serif; font-size: 13.44px;">when I selec=
t the table from pgadmin tool, it's not opened and popped up with a blank w=
indow with title "index out or range" I do have index on it.</span></span><=
/div><div dir=3D"ltr" data-setdir=3D"false" style=3D"font-family: verdana, =
helvetica, sans-serif; font-size: 16px;"><span><span style=3D"color: rgb(0,=
0, 0); font-family: Verdana, Geneva, Helvetica, Arial, sans-serif; font-si=
ze: 13.44px;"><br></span></span></div><div dir=3D"ltr" data-setdir=3D"false=
" style=3D""><font face=3D"Verdana, Geneva, Helvetica, Arial, sans-serif"><=
span style=3D"font-size: 13.44px;">regards,</span></font></div><span></span=
><br></div><div style=3D"font-family: verdana, helvetica, sans-serif; font-=
size: 16px;"><br></div><div class=3D"ydpc8555dafsignature" style=3D"font-fa=
mily: verdana, helvetica, sans-serif; font-size: 16px;"><div id=3D"ydpc8555=
dafyui_3_13_0_rc_1_1_1381789421038_7868" class=3D"ydpc8555dafyiv3811552299m=
s__id13889"><font color=3D"#0000ff"><b><span class=3D"ydpc8555dafyiv3811552=
299sg"><font color=3D"#8000ff" style=3D"background-color: inherit;"></font>=
</span></b></font><div id=3D"ydpc8555dafyui_3_13_0_rc_1_1_1381789421038_787=
4" class=3D"ydpc8555dafyiv3811552299ms__id13893"><font id=3D"ydpc8555dafyui=
_3_13_0_rc_1_1_1381789421038_7873" color=3D"#0000ff"><font color=3D"#8000ff=
"></font><span id=3D"ydpc8555dafyui_3_13_0_rc_1_1_1381789421038_7871" class=
=3D"ydpc8555dafyiv3811552299sg"><font id=3D"ydpc8555dafyui_3_13_0_rc_1_1_13=
81789421038_7870" color=3D"#8000ff" style=3D"background-color: inherit;"><b=
id=3D"ydpc8555dafyui_3_13_0_rc_1_16_1381789421038_18">Bach-Nga<br clear=3D=
"none"><br clear=3D"none"></b>No one in this world is pure and perfect.&nbs=
p; If you avoid people for their mistakes you will be alone. So judge less,=
love and forgive more.</font></span></font></div><div id=3D"ydpc8555dafyui=
_3_13_0_rc_1_1_1381789421038_7874" class=3D"ydpc8555dafyiv3811552299ms__id1=
3893"><font color=3D"#0000ff"><span class=3D"ydpc8555dafyiv3811552299sg"><f=
ont color=3D"#8000ff" style=3D"background-color: inherit;">To call him a do=
g hardly seems to do him justice though in as much as he had four legs, a t=
ail, and barked, I admit he was, to all outward appearances. But to those w=
ho knew him well, he was a perfect gentleman (Hermione Gingold)</font></spa=
n></font></div><br clear=3D"none"><span style=3D"color:rgb(128, 0, 255);" i=
d=3D"ydpc8555dafyui_3_13_0_rc_1_1_1381789421038_7867">**Live simply **Love =
generously **Care deeply **Speak kindly.</span></div><div id=3D"ydpc8555daf=
yui_3_13_0_rc_1_1_1381789421038_7866" class=3D"ydpc8555dafyiv3811552299ms__=
id13894"><span id=3D"ydpc8555dafyui_3_13_0_rc_1_1_1381789421038_7865" class=
=3D"ydpc8555dafyiv3811552299sg"><font id=3D"ydpc8555dafyui_3_13_0_rc_1_1_13=
81789421038_7864" color=3D"#8000ff" style=3D"background-color: inherit;">**=
* Genuinely rich *** Faithful talent *** Sharing success<br></font></span><=
/div></div></div></body></html>
------=_Part_5959266_2103167348.1578589603206--