· 8 years ago · Apr 25, 2018, 05:12 AM
1-- FUNCTION: public.sp_0_proj_dmlkps_dml_3518056860_11998224485(bigint, bigint, bigint, bigint, bigint, character varying, integer, bigint, integer, bigint, bigint, boolean, character varying, character varying, bigint, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying)
2
3-- DROP FUNCTION public.sp_0_proj_dmlkps_dml_3518056860_11998224485(bigint, bigint, bigint, bigint, bigint, character varying, integer, bigint, integer, bigint, bigint, boolean, character varying, character varying, bigint, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying);
4
5CREATE OR REPLACE FUNCTION public.sp_0_proj_dmlkps_dml_3518056860_11998224485(
6 _parentid bigint,
7 _parentrt bigint,
8 ___recid bigint,
9 ___parentid bigint,
10 ___rt bigint,
11 ___recids character varying,
12 _rowcount integer,
13 _startwith bigint,
14 _rtfactor integer,
15 _rtfactor_addon bigint,
16 _user bigint,
17 __newprojections boolean,
18 _projcounter character varying,
19 t_projections_desc character varying,
20 _dyn0 bigint,
21 p_0 character varying,
22 p_0_desc character varying,
23 p_1 character varying,
24 p_1_desc character varying,
25 p_2 character varying,
26 p_2_desc character varying,
27 p_3 character varying,
28 p_3_desc character varying)
29 RETURNS SETOF refcursor
30 LANGUAGE 'plpgsql'
31 COST 100
32 VOLATILE
33 ROWS 1000
34AS $BODY$
35 -- The original procedure name was
36 DECLARE rowcount int;
37 DECLARE _failed_transaction_result varchar DEFAULT '';
38 DECLARE a_2_val varchar[];
39 DECLARE __original_startwith bigint;
40 DECLARE __final_rowcount int;
41 DECLARE __result_limit int;
42 DECLARE _tcparentid bigint;
43 DECLARE _tcrecid bigint;
44 DECLARE _tcrt bigint;
45 DECLARE __projscript varchar;
46 DECLARE __projspcall varchar;
47 DECLARE __proj_sp_update_code varchar;
48 DECLARE __t_tables_results_cursor refcursor;
49 DECLARE __sumOfParamTablesValues int;
50 DECLARE __root_startwith varchar;
51 DECLARE _tables_exist int;
52 DECLARE _tables_exist_before_common int;
53 DECLARE _tables_forms_reset boolean;
54 DECLARE _check_every_common_table boolean;
55 DECLARE __begin_datetime timestamp;
56 DECLARE __tmp_curs refcursor;
57 DECLARE __tmpwating varchar;
58 DECLARE __tmpstate varchar;
59 DECLARE __tmppid bigint;
60 DECLARE __tmpqrytip varchar;
61 DECLARE __tmpclient_addr varchar;
62 DECLARE __tmpdatname varchar;
63 DECLARE __tmpapplication_name varchar;
64 DECLARE __tmpquery varchar;
65 DECLARE __tmpval varchar;
66 BEGIN
67
68 __begin_datetime := getdate();
69 _tables_exist := 0;
70 _tables_forms_reset := 'false';
71 _check_every_common_table := 'false';
72 IF ((_tables_exist = 1) OR (_tables_forms_reset = 'true')) THEN
73 _check_every_common_table := 'true';
74 _tables_forms_reset := 'false';
75 END IF;
76 a_2_val := array(select val from projections_values where spid = pg_backend_pid() and param = p_2_desc);
77 IF (_startWith = 0) THEN
78 __root_startwith := CHR(25500);
79 ELSE
80 __root_startwith := (select lower(COALESCE(rpad(cast(dmordrd0_1.val as timestamp), 23), '0000-00-00T00:00:00.000') + cast(dml1.dm2 as varchar)) from (select _startWith dm2) dml1 left join dm on dml1.dm2=dm.recid
81 left join dmlkps lkpordrd0_0 on lkpordrd0_0.recid=dml1.dm2 and lkpordrd0_0.ft=7150
82 left join dmdates dmordrd0_1 on dmordrd0_1.recid=lkpordrd0_0.val and dmordrd0_1.ft=3009 ) ;
83 END IF;
84 __result_limit := 10+1;
85 INSERT INTO t_projections_in_sp(name,spid) select 'proj_dmlkps_dml_3518056860',pg_backend_pid() ;
86 __sumOfParamTablesValues := 0 + (SELECT COUNT(*) FROM projections_values where spid = pg_backend_pid() and param = p_2_desc);
87 IF ((__sumOfParamTablesValues < 2) AND ( (EXISTS(SELECT name FROM plcprj_projs WHERE (lck = 0) and (name = 'proj_dmlkps_dml_3518056860'))) OR ( (EXISTS(SELECT name FROM plcprj_projs WHERE (lck > 0) and (name = 'proj_dmlkps_dml_3518056860'))) AND NOT EXISTS (SELECT name FROM plcprj_lck WHERE (lck > 0) and (rtfactor = _rtfactor) and (name = 'proj_dmlkps_dml_3518056860') ) AND NOT EXISTS (SELECT * FROM plcprj_rtfactor_lck WHERE rtfactor = _rtfactor) ) )) THEN
88
89 insert into test_table(test_val) values('the projection original name is : proj_dmlkps_dmlkps_fte7150_dmdates_ft_e_3009_fte7149_bm3_dm_recid_dmlkps_fte_7150_fte_3009_bm8_dmdates_val_val_cmp_prfltbetween _bm3_dm_recid_dmlkps_fte_7150_dmlkps_fte_7104_fte_7113_bm8_dmlkps_val_val_in_ lkp_571202_5741_7149_0_20 3518056860 sp_0_proj_dmlkps_dml_3518056860_11998224485');
90 __original_startwith := _startwith;
91 __final_rowcount := 0;
92 WHILE (__result_limit > 0) LOOP
93 DELETE FROM t_number_helper WHERE spid = pg_backend_pid() ;
94 INSERT INTO t_number_helper (parentid, recid, rt, mstmp, lck, lead, spid)
95 SELECT dml1.dm1, dml1.dm2, dm.rt, dm.mstmp, dm.lck ,t_projections_desc /*X2*/ ,pg_backend_pid()
96 FROM proj_dmlkps_dml_3518056860 dml1
97 LEFT JOIN dm on dm.recid=dml1.dm2
98 left join dmlkps lkpordrd0_0 on lkpordrd0_0.recid=dml1.dm2 and lkpordrd0_0.ft=7150
99 left join dmdates dmordrd0_1 on dmordrd0_1.recid=lkpordrd0_0.val and dmordrd0_1.ft=3009
100 WHERE (rtfactor = _rtfactor)
101
102
103 AND (--valEqualities - between
104 dml1.val ~>=~ rpad(p_2, 100)+p_0 AND
105 dml1.val ~<=~ rpad(p_2, 100)+p_1)
106
107 AND ( --bm2
108 dm.recid NOT IN (SELECT t_flt_0.recid FROM dmlkps t_flt_0 where t_flt_0.recid=dm.recid and t_flt_0.ft=7045 -- bm2.1
109 AND t_flt_0.val = (select recid from dmstrs where ft=4090 and val = cast(_rtfactor as varchar)+'_link_essence_autofilling' LIMIT (1)))) -- bm10
110
111
112 AND dml1.dm1 = ___recid
113 AND dml1.dm2 <> _startwith
114
115 AND lower(COALESCE(rpad(cast(dmordrd0_1.val as timestamp), 23), '0000-00-00T00:00:00.000') + COALESCE(cast(dml1.dm2 as varchar), '')) ~<~ (__root_startwith)
116 GROUP BY dml1.dm1, dml1.dm2, dm.rt, dm.mstmp, dm.lck ,/*!?!*/ dmordrd0_1.val
117 order by lower(COALESCE(rpad(cast(dmordrd0_1.val as timestamp), 23), '0000-00-00T00:00:00.000') + COALESCE(cast(dml1.dm2 as varchar), '')) using ~>~ LIMIT (__result_limit);
118 GET DIAGNOSTICS rowcount = ROW_COUNT;
119 IF (rowcount < __result_limit) THEN
120 __result_limit := 0;
121 END IF;
122 DELETE FROM t_number_helper AS tout WHERE spid = pg_backend_pid() AND
123 EXISTS (
124 SELECT tin.recid FROM t_number_helper AS tin WHERE tin.spid = pg_backend_pid() AND tout.parentid = tin.parentid AND tout.recid = tin.recid AND tout.idtime > tin.idtime
125 );
126
127
128 INSERT INTO t1 (parentid, recid, rt, mstmp, lck, lead, spid) SELECT parentid, recid, rt, mstmp, lck, lead, spid FROM t_number_helper ORDER BY idtime ASC;
129 GET DIAGNOSTICS rowcount = ROW_COUNT;
130 __result_limit := __result_limit - rowcount;
131 __final_rowcount := __final_rowcount + rowcount;
132 _startwith := (SELECT recid FROM t_number_helper WHERE spid = pg_backend_pid() AND idtime IN (SELECT MAX(idtime) FROM t_number_helper WHERE (recid >= 10001)) LIMIT (1));
133 END LOOP;
134 rowcount := __final_rowcount;
135 _startwith := __original_startwith;
136 ELSE
137 IF ((__sumOfParamTablesValues < 2) AND ((SELECT COUNT(*) FROM plcprj_projs WHERE (lck > 0) and (name = 'proj_dmlkps_dml_3518056860')) = 0)) THEN
138 IF ('proj_dmlkps_dml_3518056860' not in (select name from plcprj_todo)) THEN
139 __projscript := '
140 CREATE TABLE proj_dmlkps_dml_3518056860 (val varchar(4000), dm1 bigint, dm2 bigint, rtfactor int, tflt724recid bigint, tflt725recid bigint, tflt829recid bigint, tflt830recid bigint, tflt831recid bigint);
141
142 INSERT INTO proj_dmlkps_dml_3518056860(val, dm1, dm2, rtfactor, tflt724recid, tflt725recid, tflt829recid, tflt830recid, tflt831recid)
143 SELECT lower(COALESCE(rpad(t_flt_8_31.val , 100),''''))+COALESCE(rpad(t_flt_7_25.val , 100) , ''''), dm1, dm2, (dm.rt/10000000000000) rtfactor, t_flt_7_24.recid, t_flt_7_25.recid, t_flt_8_29.recid, t_flt_8_30.recid, t_flt_8_31.recid
144 FROM (SELECT dmlkps.val dm1, dmlkps.recid dm2, dmlkps.ft rt2 FROM dmlkps) dml1
145 JOIN dm ON dm.recid=dml1.dm2
146 left join dmlkps lkpordrd0_0 on lkpordrd0_0.recid=dml1.dm2 and lkpordrd0_0.ft=7150
147 left join dmdates dmordrd0_1 on dmordrd0_1.recid=lkpordrd0_0.val and dmordrd0_1.ft=3009
148 --bm3
149 JOIN dm xx6 ON dm.recid = xx6.recid /* help PG optimizer choose a good plan */
150 join dmlkps t_flt_7_24 on t_flt_7_24.recid=xx6.recid and t_flt_7_24.ft=7150
151 -- uid: bm8 3009
152 join dmdates t_flt_7_25 on t_flt_7_25.recid=t_flt_7_24.val and t_flt_7_25.ft =3009 -- bm8.1
153 -- bm8.2
154
155 --bm3
156 /* bm20 */
157 join dmlkps t_flt_8_29 on t_flt_8_29.recid=/* bm22 */ dm.recid and t_flt_8_29.ft=7150
158 join dmlkps t_flt_8_30 on t_flt_8_30.recid=t_flt_8_29.val and t_flt_8_30.ft=7104
159 -- uid: bm8 7113
160 join dmlkps t_flt_8_31 on t_flt_8_31.recid=t_flt_8_30.val /* bm21 */ and t_flt_8_31.ft =7113 -- bm8.1
161 -- bm8.2
162
163 WHERE (dm.rt / 10000000000000 = CAST((dm.rt/10000000000000) AS int)) AND dml1.rt2=7149;
164
165
166 CREATE INDEX ix90_proj_dmlkps_dml_3518056860__rtfactor_tflt724recid
167 ON proj_dmlkps_dml_3518056860
168 USING btree
169 (rtfactor, tflt724recid)
170 WITH (FILLFACTOR=90);
171
172 CREATE INDEX ix90_proj_dmlkps_dml_3518056860__rtfactor_tflt725recid
173 ON proj_dmlkps_dml_3518056860
174 USING btree
175 (rtfactor, tflt725recid)
176 WITH (FILLFACTOR=90);
177
178 CREATE INDEX ix90_proj_dmlkps_dml_3518056860__rtfactor_tflt829recid
179 ON proj_dmlkps_dml_3518056860
180 USING btree
181 (rtfactor, tflt829recid)
182 WITH (FILLFACTOR=90);
183
184 CREATE INDEX ix90_proj_dmlkps_dml_3518056860__rtfactor_tflt830recid
185 ON proj_dmlkps_dml_3518056860
186 USING btree
187 (rtfactor, tflt830recid)
188 WITH (FILLFACTOR=90);
189
190 CREATE INDEX ix90_proj_dmlkps_dml_3518056860__rtfactor_tflt831recid
191 ON proj_dmlkps_dml_3518056860
192 USING btree
193 (rtfactor, tflt831recid)
194 WITH (FILLFACTOR=90);
195
196 CREATE INDEX ix90_proj_dmlkps_dml_3518056860__rtfactor_dm1_dm2_valvpo
197 ON proj_dmlkps_dml_3518056860
198 USING btree
199 (rtfactor, dm1, dm2, val varchar_pattern_ops)
200 WITH (FILLFACTOR=90);
201
202 CREATE INDEX ixc90_proj_dmlkps_dml_3518056860__rtfactor_dm1_valvpo_dm2
203 ON proj_dmlkps_dml_3518056860
204 USING btree
205 (rtfactor, dm1, val varchar_pattern_ops, dm2)
206 WITH (FILLFACTOR=90);
207
208 CREATE INDEX ix90_proj_dmlkps_dml_3518056860__rtfactor_dm2
209 ON proj_dmlkps_dml_3518056860
210 USING btree
211 (rtfactor, dm2)
212 WITH (FILLFACTOR=90); ';
213 __projspcall := '
214 BEGIN
215 IF ((_mode = 5) OR (_mode = 8) OR ((_mode = 6) AND (''''proj_dmlkps_dml_3518056860'''' IN(SELECT name FROM _proj_to_be_modified where spid = pg_backend_pid() ))) OR ((_ft NOT IN (-12, -11)) AND ((_ft <> 9) AND ((1 = 0) OR ( ((((_ft = 0) OR (_ft = 100) OR (((_ft%10000)/1000)=1) /*additional ft filtering*/ OR (_ft IN (-4))) OR (_ft = 7150) OR (_ft = 3009) OR (_ft = 7149)) AND (_ft NOT IN (-11, -12, 8))) OR (((_ft = 0) OR (_ft = 100) OR (((_ft%10000)/1000)=1) /*additional ft filtering*/ OR (_ft IN (-4))) OR (_ft = 7150)) OR (((_ft = 0) OR (_ft = 100) OR (((_ft%10000)/1000)=1) /*additional ft filtering*/ OR (_ft IN (-4))) OR (_ft = 3009)) OR (((_ft = 0) OR (_ft = 100) OR (((_ft%10000)/1000)=1) /*additional ft filtering*/ OR (_ft IN (-4))) OR (_ft = 7150)) OR (((_ft = 0) OR (_ft = 100) OR (((_ft%10000)/1000)=1) /*additional ft filtering*/ OR (_ft IN (-4))) OR (_ft = 7104)) OR (((_ft = 0) OR (_ft = 100) OR (((_ft%10000)/1000)=1) /*additional ft filtering*/ OR (_ft IN (-4))) OR (_ft = 7113))))) AND (((_mode = 1) OR (_mode = 3)) OR (((_mode = 2) OR (_mode = 4)) AND (''''proj_dmlkps_dml_3518056860'''' IN(SELECT name FROM _proj_to_be_modified where spid = pg_backend_pid() )))))) THEN
216 IF ((_agg_from_to_do = ''''true'''') OR (_mode = 3)) THEN
217 IF (_mode = 3) THEN
218 insert into plcbug (val) values (''''before lazy locking of '''' + ''''proj_dmlkps_dml_3518056860'''');
219 INSERT INTO t_lazy_locks (prjname, rtfactor) values (''''proj_dmlkps_dml_3518056860'''',_rtfactor);
220 insert into plcbug (val) values (''''after lazy locking of '''' + ''''proj_dmlkps_dml_3518056860'''');
221 END IF;
222 ELSE
223 IF (NOT EXISTS(SELECT recid FROM _proj_already_updated_for_recid WHERE (recid = _recid) AND ((_ft NOT IN (-12, -11)) OR (parentid = _parentid)) AND (name = ''''proj_dmlkps_dml_3518056860'''') AND (spid = pg_backend_pid()) )) THEN
224 insert into plcbug (val) values (''''before entering the updater of '''' + ''''proj_dmlkps_dml_3518056860'''');
225
226 PERFORM sp_proj_dmlkps_dml_3518056860( _rtfactor, _act, _parentid, _recid, _rt, _updt, _ft, _mode);
227 insert into plcbug (val) values (''''after exiting the updater of '''' + ''''proj_dmlkps_dml_3518056860'''');
228 IF (NOT ((_ft%1000 = 0) and ((_ft%10000)/1000)=1)) THEN
229 INSERT INTO _proj_already_updated_for_recid (name, recid, parentid,spid) VALUES (''''proj_dmlkps_dml_3518056860'''', _recid, _parentid,pg_backend_pid() );
230 END IF;
231 END IF;
232 IF (_mode = 4) THEN
233 insert into plcbug (val) values (''''before lazy unlocking of '''' + ''''proj_dmlkps_dml_3518056860'''');
234 INSERT INTO t_lazy_locks (prjname, rtfactor) values (''''proj_dmlkps_dml_3518056860'''',_rtfactor);
235 insert into plcbug (val) values (''''after lazy unlocking of '''' + ''''proj_dmlkps_dml_3518056860'''');
236 END IF;
237 END IF;
238 END IF;
239 EXCEPTION
240 WHEN raise_exception THEN
241 RAISE EXCEPTION ''''%'''', _failed_transaction_result;
242 WHEN others THEN
243 --CURSORS_FILLERS_REBOOT--
244 GET STACKED DIAGNOSTICS
245 v_state = RETURNED_SQLSTATE,
246 v_msg = MESSAGE_TEXT,
247 v_detail = PG_EXCEPTION_DETAIL,
248 v_hint = PG_EXCEPTION_HINT,
249 v_context = PG_EXCEPTION_CONTEXT;
250 INSERT INTO plcbug (val) VALUES (''''RETURNED_SQLSTATE: ''''+v_state+'''' ,MESSAGE_TEXT: ''''+v_msg+'''' ,PG_EXCEPTION_DETAIL: ''''+v_detail+'''' ,PG_EXCEPTION_HINT: ''''+v_hint+'''' ,PG_EXCEPTION_CONTEX: ''''+v_context);
251 raise notice ''''Transaction was rolled back'''';
252 END; ';
253 __proj_sp_update_code := '
254 CREATE OR REPLACE FUNCTION sp_proj_dmlkps_dml_3518056860(_rtfactor int, _act smallint, _parentid bigint, _recid bigint, _rt bigint, _updt smallint, _ft int, _mode int) RETURNS SETOF refcursor AS '+'$'+'BODY'+'$'+' -- The original procedure name was
255 DECLARE rowcount int;
256 DECLARE __all_alright boolean DEFAULT ''true'';
257 DECLARE _tables_exist int;
258 DECLARE _tables_exist_before_common int;
259 DECLARE _tables_forms_reset boolean;
260 DECLARE _check_every_common_table boolean;
261 DECLARE __begin_datetime timestamp;
262 DECLARE __tmp_curs refcursor;
263 DECLARE __tmpwating varchar;
264 DECLARE __tmpstate varchar;
265 DECLARE __tmppid bigint;
266 DECLARE __tmpqrytip varchar;
267 DECLARE __tmpclient_addr varchar;
268 DECLARE __tmpdatname varchar;
269 DECLARE __tmpapplication_name varchar;
270 DECLARE __tmpquery varchar;
271 DECLARE __tmpval varchar;
272 BEGIN
273
274 __begin_datetime := getdate();
275 _tables_exist := 0;
276 _tables_forms_reset := ''false'';
277 _check_every_common_table := ''false'';
278 IF ((_tables_exist = 1) OR (_tables_forms_reset = ''true'')) THEN
279 _check_every_common_table := ''true'';
280 _tables_forms_reset := ''false'';
281 END IF;
282 insert into plcbug(val) values (''proj_dmlkps_dml_3518056860 - ''+''updt = ''+cast(_updt as varchar(100))+'' ft = ''+cast(_ft as varchar(100))+'' act = ''+cast(_act as varchar(100)));
283 insert into plcbug(val) values (''proj_dmlkps_dml_3518056860'');
284 IF ((_act = 0) OR (_act = 1)) THEN
285 PERFORM pg_advisory_lock((''x''||substr(md5(cast(_rtfactor as varchar)+''proj_dmlkps_dml_3518056860''),1,16))::bit(64)::bigint);
286 __begin_datetime := getdate();
287 insert into plcbug(val) values (''proj_dmlkps_dml_3518056860 - ''+''before delete'');
288 DELETE FROM proj_dmlkps_dml_3518056860
289 WHERE (_updt IN (-2, -1, 0, 1)) AND (rtfactor = _rtfactor) AND (
290 ((_updt <> -1) AND (
291 ((_ft = 0) AND ((dm1 = _recid) OR (dm2 = _recid)))
292 OR ((_ft = 8) AND ((dm1 = _parentid) AND (dm2 = _recid))) -- deleting a connection from dmlinks
293 OR ((_updt = -2) AND (dm2 = _recid)) -- deleting a row that had the string PlcUtils.__updBulk_DELETE_Value in valstr
294 OR (((_updt=0) AND (((_ft%10000)/1000)=1) AND NOT (_ft%1000=0)) AND (dm2 = _recid)) -- the f101 value in dm is modified(we first delete all rows and than insert them again)
295 OR (((_updt=0) AND (_ft >= 2000)) AND (dm2 = _recid)) -- the case of an update with regular ft value
296 OR (((_updt = 1) OR ((_ft%1000=0) and ((_ft%10000)/1000)=1)) AND (dm2 = _recid)) -- ((ft%10000)/1000)=1 is when the rt value was changed so we want to delete all the relations with the previous rt
297 OR ((_updt=0) AND (_ft IN (-11, -12)) AND ((dm1 = _parentid) AND (dm2 = _recid))) -- new connection in dmlinks due to changes Eugene did, we need to make sure that the row does not appear in the projection before.
298 ))
299 OR (((_ft = 0) or (((_ft%10000)/1000)=1) or ((_ft = 7150))) /* delete-recod or modification of ft specific */ AND (_recid = tflt724recid))
300 OR (((_ft = 0) or (((_ft%10000)/1000)=1) or ((_ft = 3009))) /* delete-recod or modification of ft specific */ AND (_recid = tflt725recid))
301 OR (((_ft = 0) or (((_ft%10000)/1000)=1) or ((_ft = 7150))) /* delete-recod or modification of ft specific */ AND (_recid = tflt829recid))
302 OR (((_ft = 0) or (((_ft%10000)/1000)=1) or ((_ft = 7104))) /* delete-recod or modification of ft specific */ AND (_recid = tflt830recid))
303 OR (((_ft = 0) or (((_ft%10000)/1000)=1) or ((_ft = 7113))) /* delete-recod or modification of ft specific */ AND (_recid = tflt831recid)));
304 GET DIAGNOSTICS rowcount = ROW_COUNT;
305 insert into plcbug(val) values (''proj_dmlkps_dml_3518056860 - ''+''after delete, number of rows affected are: ''+cast(rowcount as varchar(5)));
306 insert into plcprj_projs_info(name,projstart,projend,lck,rtfactor,act,parentid,recid,rt,updt,ft,del,pid,affected,mode) select ''proj_dmlkps_dml_3518056860'',__begin_datetime,getdate(),(select lck from plcprj_projs where name = ''proj_dmlkps_dml_3518056860''),_rtfactor,_act,_parentid,_recid,_rt,_updt,_ft,1,pg_backend_pid(),cast(rowcount as varchar(5)),_mode;
307 END IF;
308 IF (_act = 1) THEN
309 __begin_datetime := getdate();
310 insert into plcbug(val) values (''proj_dmlkps_dml_3518056860 - ''+''before update first insert'');
311 IF ((
312 (_ft = 0)
313 OR (((((_ft = 7150)) or (((_ft%10000)/1000)=1)) OR (1=0)) )
314 OR (((((_ft = 3009)) or (((_ft%10000)/1000)=1)) OR (1=0)) )
315 OR (((((_ft = 7150)) or (((_ft%10000)/1000)=1)) OR (1=0)) )
316 OR (((((_ft = 7104)) or (((_ft%10000)/1000)=1)) OR (1=0)) )
317 OR (((((_ft = 7113)) or (((_ft%10000)/1000)=1)) OR (1=0)) ) /*skipcheck*/)) THEN
318INSERT INTO proj_dmlkps_dml_3518056860(val, dm1, dm2, rtfactor, tflt724recid, tflt725recid, tflt829recid, tflt830recid, tflt831recid)
319SELECT * FROM (
320SELECT lower(COALESCE(rpad(t_flt_8_31.val , 100),''''))+COALESCE(rpad(t_flt_7_25.val , 100) , ''''), dm1, dm2, (dm.rt/10000000000000) rtfactor, t_flt_7_24.recid, t_flt_7_25.recid, t_flt_8_29.recid, t_flt_8_30.recid, t_flt_8_31.recid
321 FROM (SELECT dmlkps.val dm1, dmlkps.recid dm2, dmlkps.ft rt2 FROM dmlkps) dml1
322 JOIN dm ON dm.recid=dml1.dm2
323 left join dmlkps lkpordrd0_0 on lkpordrd0_0.recid=dml1.dm2 and lkpordrd0_0.ft=7150
324 left join dmdates dmordrd0_1 on dmordrd0_1.recid=lkpordrd0_0.val and dmordrd0_1.ft=3009
325 --bm3
326 JOIN dm xx6 ON dm.recid = xx6.recid /* help PG optimizer choose a good plan */
327 join dmlkps t_flt_7_24 on t_flt_7_24.recid=xx6.recid and t_flt_7_24.ft=7150
328 -- uid: bm8 3009
329 join dmdates t_flt_7_25 on t_flt_7_25.recid=t_flt_7_24.val and t_flt_7_25.ft =3009 -- bm8.1
330 -- bm8.2
331
332 --bm3
333 /* bm20 */
334 join dmlkps t_flt_8_29 on t_flt_8_29.recid=/* bm22 */ dm.recid and t_flt_8_29.ft=7150
335 join dmlkps t_flt_8_30 on t_flt_8_30.recid=t_flt_8_29.val and t_flt_8_30.ft=7104
336 -- uid: bm8 7113
337 join dmlkps t_flt_8_31 on t_flt_8_31.recid=t_flt_8_30.val /* bm21 */ and t_flt_8_31.ft =7113 -- bm8.1
338 -- bm8.2
339
340 WHERE (dm.rt / 10000000000000 = _rtfactor) AND dml1.rt2=7149 AND ((1=1) AND (
341 ((_ft = 0) AND ((dml1.dm1 = _recid) OR (dml1.dm2 = _recid)))
342 OR (((((_ft = 7150)) or (((_ft%10000)/1000)=1)) OR (1=0)) AND (_recid = t_flt_7_24.recid) AND (dml1.dm2 <> _recid))))
343UNION
344SELECT lower(COALESCE(rpad(t_flt_8_31.val , 100),''''))+COALESCE(rpad(t_flt_7_25.val , 100) , ''''), dm1, dm2, (dm.rt/10000000000000) rtfactor, t_flt_7_24.recid, t_flt_7_25.recid, t_flt_8_29.recid, t_flt_8_30.recid, t_flt_8_31.recid
345 FROM (SELECT dmlkps.val dm1, dmlkps.recid dm2, dmlkps.ft rt2 FROM dmlkps) dml1
346 JOIN dm ON dm.recid=dml1.dm2
347 left join dmlkps lkpordrd0_0 on lkpordrd0_0.recid=dml1.dm2 and lkpordrd0_0.ft=7150
348 left join dmdates dmordrd0_1 on dmordrd0_1.recid=lkpordrd0_0.val and dmordrd0_1.ft=3009
349 --bm3
350 JOIN dm xx6 ON dm.recid = xx6.recid /* help PG optimizer choose a good plan */
351 join dmlkps t_flt_7_24 on t_flt_7_24.recid=xx6.recid and t_flt_7_24.ft=7150
352 -- uid: bm8 3009
353 join dmdates t_flt_7_25 on t_flt_7_25.recid=t_flt_7_24.val and t_flt_7_25.ft =3009 -- bm8.1
354 -- bm8.2
355
356 --bm3
357 /* bm20 */
358 join dmlkps t_flt_8_29 on t_flt_8_29.recid=/* bm22 */ dm.recid and t_flt_8_29.ft=7150
359 join dmlkps t_flt_8_30 on t_flt_8_30.recid=t_flt_8_29.val and t_flt_8_30.ft=7104
360 -- uid: bm8 7113
361 join dmlkps t_flt_8_31 on t_flt_8_31.recid=t_flt_8_30.val /* bm21 */ and t_flt_8_31.ft =7113 -- bm8.1
362 -- bm8.2
363
364 WHERE (dm.rt / 10000000000000 = _rtfactor) AND dml1.rt2=7149 AND ((1=1) AND (
365 ((_ft = 0) AND ((dml1.dm1 = _recid) OR (dml1.dm2 = _recid)))
366 OR (((((_ft = 3009)) or (((_ft%10000)/1000)=1)) OR (1=0)) AND (_recid = t_flt_7_25.recid) AND (dml1.dm2 <> _recid))))
367UNION
368SELECT lower(COALESCE(rpad(t_flt_8_31.val , 100),''''))+COALESCE(rpad(t_flt_7_25.val , 100) , ''''), dm1, dm2, (dm.rt/10000000000000) rtfactor, t_flt_7_24.recid, t_flt_7_25.recid, t_flt_8_29.recid, t_flt_8_30.recid, t_flt_8_31.recid
369 FROM (SELECT dmlkps.val dm1, dmlkps.recid dm2, dmlkps.ft rt2 FROM dmlkps) dml1
370 JOIN dm ON dm.recid=dml1.dm2
371 left join dmlkps lkpordrd0_0 on lkpordrd0_0.recid=dml1.dm2 and lkpordrd0_0.ft=7150
372 left join dmdates dmordrd0_1 on dmordrd0_1.recid=lkpordrd0_0.val and dmordrd0_1.ft=3009
373 --bm3
374 JOIN dm xx6 ON dm.recid = xx6.recid /* help PG optimizer choose a good plan */
375 join dmlkps t_flt_7_24 on t_flt_7_24.recid=xx6.recid and t_flt_7_24.ft=7150
376 -- uid: bm8 3009
377 join dmdates t_flt_7_25 on t_flt_7_25.recid=t_flt_7_24.val and t_flt_7_25.ft =3009 -- bm8.1
378 -- bm8.2
379
380 --bm3
381 /* bm20 */
382 join dmlkps t_flt_8_29 on t_flt_8_29.recid=/* bm22 */ dm.recid and t_flt_8_29.ft=7150
383 join dmlkps t_flt_8_30 on t_flt_8_30.recid=t_flt_8_29.val and t_flt_8_30.ft=7104
384 -- uid: bm8 7113
385 join dmlkps t_flt_8_31 on t_flt_8_31.recid=t_flt_8_30.val /* bm21 */ and t_flt_8_31.ft =7113 -- bm8.1
386 -- bm8.2
387
388 WHERE (dm.rt / 10000000000000 = _rtfactor) AND dml1.rt2=7149 AND ((1=1) AND (
389 ((_ft = 0) AND ((dml1.dm1 = _recid) OR (dml1.dm2 = _recid)))
390 OR (((((_ft = 7150)) or (((_ft%10000)/1000)=1)) OR (1=0)) AND (_recid = t_flt_8_29.recid) AND (dml1.dm2 <> _recid))))
391UNION
392SELECT lower(COALESCE(rpad(t_flt_8_31.val , 100),''''))+COALESCE(rpad(t_flt_7_25.val , 100) , ''''), dm1, dm2, (dm.rt/10000000000000) rtfactor, t_flt_7_24.recid, t_flt_7_25.recid, t_flt_8_29.recid, t_flt_8_30.recid, t_flt_8_31.recid
393 FROM (SELECT dmlkps.val dm1, dmlkps.recid dm2, dmlkps.ft rt2 FROM dmlkps) dml1
394 JOIN dm ON dm.recid=dml1.dm2
395 left join dmlkps lkpordrd0_0 on lkpordrd0_0.recid=dml1.dm2 and lkpordrd0_0.ft=7150
396 left join dmdates dmordrd0_1 on dmordrd0_1.recid=lkpordrd0_0.val and dmordrd0_1.ft=3009
397 --bm3
398 JOIN dm xx6 ON dm.recid = xx6.recid /* help PG optimizer choose a good plan */
399 join dmlkps t_flt_7_24 on t_flt_7_24.recid=xx6.recid and t_flt_7_24.ft=7150
400 -- uid: bm8 3009
401 join dmdates t_flt_7_25 on t_flt_7_25.recid=t_flt_7_24.val and t_flt_7_25.ft =3009 -- bm8.1
402 -- bm8.2
403
404 --bm3
405 /* bm20 */
406 join dmlkps t_flt_8_29 on t_flt_8_29.recid=/* bm22 */ dm.recid and t_flt_8_29.ft=7150
407 join dmlkps t_flt_8_30 on t_flt_8_30.recid=t_flt_8_29.val and t_flt_8_30.ft=7104
408 -- uid: bm8 7113
409 join dmlkps t_flt_8_31 on t_flt_8_31.recid=t_flt_8_30.val /* bm21 */ and t_flt_8_31.ft =7113 -- bm8.1
410 -- bm8.2
411
412 WHERE (dm.rt / 10000000000000 = _rtfactor) AND dml1.rt2=7149 AND ((1=1) AND (
413 ((_ft = 0) AND ((dml1.dm1 = _recid) OR (dml1.dm2 = _recid)))
414 OR (((((_ft = 7104)) or (((_ft%10000)/1000)=1)) OR (1=0)) AND (_recid = t_flt_8_30.recid) AND (dml1.dm2 <> _recid))))
415UNION
416SELECT lower(COALESCE(rpad(t_flt_8_31.val , 100),''''))+COALESCE(rpad(t_flt_7_25.val , 100) , ''''), dm1, dm2, (dm.rt/10000000000000) rtfactor, t_flt_7_24.recid, t_flt_7_25.recid, t_flt_8_29.recid, t_flt_8_30.recid, t_flt_8_31.recid
417 FROM (SELECT dmlkps.val dm1, dmlkps.recid dm2, dmlkps.ft rt2 FROM dmlkps) dml1
418 JOIN dm ON dm.recid=dml1.dm2
419 left join dmlkps lkpordrd0_0 on lkpordrd0_0.recid=dml1.dm2 and lkpordrd0_0.ft=7150
420 left join dmdates dmordrd0_1 on dmordrd0_1.recid=lkpordrd0_0.val and dmordrd0_1.ft=3009
421 --bm3
422 JOIN dm xx6 ON dm.recid = xx6.recid /* help PG optimizer choose a good plan */
423 join dmlkps t_flt_7_24 on t_flt_7_24.recid=xx6.recid and t_flt_7_24.ft=7150
424 -- uid: bm8 3009
425 join dmdates t_flt_7_25 on t_flt_7_25.recid=t_flt_7_24.val and t_flt_7_25.ft =3009 -- bm8.1
426 -- bm8.2
427
428 --bm3
429 /* bm20 */
430 join dmlkps t_flt_8_29 on t_flt_8_29.recid=/* bm22 */ dm.recid and t_flt_8_29.ft=7150
431 join dmlkps t_flt_8_30 on t_flt_8_30.recid=t_flt_8_29.val and t_flt_8_30.ft=7104
432 -- uid: bm8 7113
433 join dmlkps t_flt_8_31 on t_flt_8_31.recid=t_flt_8_30.val /* bm21 */ and t_flt_8_31.ft =7113 -- bm8.1
434 -- bm8.2
435
436 WHERE (dm.rt / 10000000000000 = _rtfactor) AND dml1.rt2=7149 AND ((1=1) AND (
437 ((_ft = 0) AND ((dml1.dm1 = _recid) OR (dml1.dm2 = _recid)))
438 OR (((((_ft = 7113)) or (((_ft%10000)/1000)=1)) OR (1=0)) AND (_recid = t_flt_8_31.recid) AND (dml1.dm2 <> _recid))))
439) as t LIMIT (1);
440 GET DIAGNOSTICS rowcount = ROW_COUNT;
441 insert into plcbug(val) values (''proj_dmlkps_dml_3518056860 - ''+''after update first insert, number of rows affected are: ''+cast(rowcount as varchar(5)));
442 ELSE
443 insert into plcbug(val) values (''proj_dmlkps_dml_3518056860 - ''+''after update first insert, skipped '');
444 END IF;
445 insert into plcbug(val) values (''proj_dmlkps_dml_3518056860 - ''+''before update second insert'');
446 IF ((
447 (_ft <> 0) /*skipcheck*/)) THEN
448 INSERT INTO proj_dmlkps_dml_3518056860(val, dm1, dm2, rtfactor, tflt724recid, tflt725recid, tflt829recid, tflt830recid, tflt831recid)
449 SELECT lower(COALESCE(rpad(t_flt_8_31.val , 100),''''))+COALESCE(rpad(t_flt_7_25.val , 100) , ''''), dm1, dm2, (dm.rt/10000000000000) rtfactor, t_flt_7_24.recid, t_flt_7_25.recid, t_flt_8_29.recid, t_flt_8_30.recid, t_flt_8_31.recid
450 FROM (SELECT dmlkps.val dm1, dmlkps.recid dm2, dmlkps.ft rt2 FROM dmlkps) dml1
451 JOIN dm ON dm.recid=dml1.dm2
452 left join dmlkps lkpordrd0_0 on lkpordrd0_0.recid=dml1.dm2 and lkpordrd0_0.ft=7150
453 left join dmdates dmordrd0_1 on dmordrd0_1.recid=lkpordrd0_0.val and dmordrd0_1.ft=3009
454 --bm3
455 JOIN dm xx6 ON dm.recid = xx6.recid /* help PG optimizer choose a good plan */
456 join dmlkps t_flt_7_24 on t_flt_7_24.recid=xx6.recid and t_flt_7_24.ft=7150
457 -- uid: bm8 3009
458 join dmdates t_flt_7_25 on t_flt_7_25.recid=t_flt_7_24.val and t_flt_7_25.ft =3009 -- bm8.1
459 -- bm8.2
460
461 --bm3
462 /* bm20 */
463 join dmlkps t_flt_8_29 on t_flt_8_29.recid=/* bm22 */ dm.recid and t_flt_8_29.ft=7150
464 join dmlkps t_flt_8_30 on t_flt_8_30.recid=t_flt_8_29.val and t_flt_8_30.ft=7104
465 -- uid: bm8 7113
466 join dmlkps t_flt_8_31 on t_flt_8_31.recid=t_flt_8_30.val /* bm21 */ and t_flt_8_31.ft =7113 -- bm8.1
467 -- bm8.2
468
469 WHERE (dm.rt / 10000000000000 = _rtfactor) AND dml1.rt2=7149 AND ((1=1) AND (
470 ((_ft <> 0) AND (dml1.dm2 = _recid))));
471 GET DIAGNOSTICS rowcount = ROW_COUNT;
472 insert into plcbug(val) values (''proj_dmlkps_dml_3518056860 - ''+''after update second insert, number of rows affected are: ''+cast(rowcount as varchar(5)));
473 ELSE
474 insert into plcbug(val) values (''proj_dmlkps_dml_3518056860 - ''+''after update second insert, skipped '');
475 END IF;
476 insert into plcprj_projs_info(name,projstart,projend,lck,rtfactor,act,parentid,recid,rt,updt,ft,del,pid,affected,mode) select ''proj_dmlkps_dml_3518056860'',__begin_datetime,getdate(),(select lck from plcprj_projs where name = ''proj_dmlkps_dml_3518056860''),_rtfactor,_act,_parentid,_recid,_rt,_updt,_ft,0,pg_backend_pid(),cast(rowcount as varchar(5)),_mode;
477 END IF;
478 IF (_act = 5) THEN
479 __begin_datetime := getdate();
480 DELETE FROM proj_dmlkps_dml_3518056860 WHERE rtfactor = _rtfactor;
481
482 SET ENABLE_NESTLOOP TO FALSE;
483
484 INSERT INTO proj_dmlkps_dml_3518056860(val, dm1, dm2, rtfactor, tflt724recid, tflt725recid, tflt829recid, tflt830recid, tflt831recid)
485 SELECT lower(COALESCE(rpad(t_flt_8_31.val , 100),''''))+COALESCE(rpad(t_flt_7_25.val , 100) , ''''), dm1, dm2, (dm.rt/10000000000000) rtfactor, t_flt_7_24.recid, t_flt_7_25.recid, t_flt_8_29.recid, t_flt_8_30.recid, t_flt_8_31.recid
486 FROM (SELECT dmlkps.val dm1, dmlkps.recid dm2, dmlkps.ft rt2 FROM dmlkps) dml1
487 JOIN dm ON dm.recid=dml1.dm2
488 left join dmlkps lkpordrd0_0 on lkpordrd0_0.recid=dml1.dm2 and lkpordrd0_0.ft=7150
489 left join dmdates dmordrd0_1 on dmordrd0_1.recid=lkpordrd0_0.val and dmordrd0_1.ft=3009
490 --bm3
491 JOIN dm xx6 ON dm.recid = xx6.recid /* help PG optimizer choose a good plan */
492 join dmlkps t_flt_7_24 on t_flt_7_24.recid=xx6.recid and t_flt_7_24.ft=7150
493 -- uid: bm8 3009
494 join dmdates t_flt_7_25 on t_flt_7_25.recid=t_flt_7_24.val and t_flt_7_25.ft =3009 -- bm8.1
495 -- bm8.2
496
497 --bm3
498 /* bm20 */
499 join dmlkps t_flt_8_29 on t_flt_8_29.recid=/* bm22 */ dm.recid and t_flt_8_29.ft=7150
500 join dmlkps t_flt_8_30 on t_flt_8_30.recid=t_flt_8_29.val and t_flt_8_30.ft=7104
501 -- uid: bm8 7113
502 join dmlkps t_flt_8_31 on t_flt_8_31.recid=t_flt_8_30.val /* bm21 */ and t_flt_8_31.ft =7113 -- bm8.1
503 -- bm8.2
504
505 WHERE (dm.rt / 10000000000000 = _rtfactor) AND dml1.rt2=7149
506 AND dm.rt BETWEEN (_rtfactor * 10000000000000) AND ((_rtfactor + 1) * 10000000000000 - 1);
507 SET ENABLE_NESTLOOP TO TRUE;
508 insert into plcprj_projs_info(name,projstart,projend,lck,rtfactor,act,parentid,recid,rt,updt,ft,del,pid,affected,mode) select ''proj_dmlkps_dml_3518056860'',__begin_datetime,getdate(),(select lck from plcprj_projs where name = ''proj_dmlkps_dml_3518056860''),_rtfactor,_act,_parentid,_recid,_rt,_updt,_ft,0,pg_backend_pid(),cast(rowcount as varchar(5)),_mode;
509 END IF;
510 IF (_act = 8) THEN
511 __begin_datetime := getdate();
512 DELETE FROM proj_dmlkps_dml_3518056860 WHERE rtfactor = _rtfactor;
513 END IF;
514 IF (_act = 7) THEN
515 CREATE TABLE g_proj_dmlkps_dml_3518056860 (val varchar(4000), dm1 bigint, dm2 bigint, rtfactor int, tflt724recid bigint, tflt725recid bigint, tflt829recid bigint, tflt830recid bigint, tflt831recid bigint);
516
517 INSERT INTO g_proj_dmlkps_dml_3518056860(val, dm1, dm2, rtfactor, tflt724recid, tflt725recid, tflt829recid, tflt830recid, tflt831recid)
518 SELECT lower(COALESCE(rpad(t_flt_8_31.val , 100),''''))+COALESCE(rpad(t_flt_7_25.val , 100) , '''')+ lower(COALESCE(rpad(cast(dmordrd0_1.val as timestamp), 23), ''0000-00-00T00:00:00.000'') + COALESCE(cast(dml1.dm2 as varchar), '''')) , dm1, dm2, (dm.rt/10000000000000) rtfactor, t_flt_7_24.recid, t_flt_7_25.recid, t_flt_8_29.recid, t_flt_8_30.recid, t_flt_8_31.recid
519 FROM (SELECT dmlkps.val dm1, dmlkps.recid dm2, dmlkps.ft rt2 FROM dmlkps) dml1
520 JOIN dm ON dm.recid=dml1.dm2
521 left join dmlkps lkpordrd0_0 on lkpordrd0_0.recid=dml1.dm2 and lkpordrd0_0.ft=7150
522 left join dmdates dmordrd0_1 on dmordrd0_1.recid=lkpordrd0_0.val and dmordrd0_1.ft=3009
523 --bm3
524 JOIN dm xx6 ON dm.recid = xx6.recid /* help PG optimizer choose a good plan */
525 join dmlkps t_flt_7_24 on t_flt_7_24.recid=xx6.recid and t_flt_7_24.ft=7150
526 -- uid: bm8 3009
527 join dmdates t_flt_7_25 on t_flt_7_25.recid=t_flt_7_24.val and t_flt_7_25.ft =3009 -- bm8.1
528 -- bm8.2
529
530 --bm3
531 /* bm20 */
532 join dmlkps t_flt_8_29 on t_flt_8_29.recid=/* bm22 */ dm.recid and t_flt_8_29.ft=7150
533 join dmlkps t_flt_8_30 on t_flt_8_30.recid=t_flt_8_29.val and t_flt_8_30.ft=7104
534 -- uid: bm8 7113
535 join dmlkps t_flt_8_31 on t_flt_8_31.recid=t_flt_8_30.val /* bm21 */ and t_flt_8_31.ft =7113 -- bm8.1
536 -- bm8.2
537
538 WHERE (dm.rt / 10000000000000 = CAST((dm.rt/10000000000000) AS int)) AND dml1.rt2=7149;
539
540 CREATE INDEX ixc90_g_proj_dmlkps_dml_3518056860__rtfactor_dm1_dm2_valvpo
541 ON g_proj_dmlkps_dml_3518056860
542 USING btree
543 (rtfactor, dm1, dm2, val varchar_pattern_ops)
544 WITH (FILLFACTOR=90);
545 IF ((__all_alright = ''true'') AND ((SELECT COUNT(*) FROM proj_dmlkps_dml_3518056860) - (SELECT COUNT(*) FROM g_proj_dmlkps_dml_3518056860)) <> 0) THEN
546 __all_alright := ''false'';
547 END IF;
548 IF ((__all_alright = ''true'') AND EXISTS (
549 SELECT * FROM proj_dmlkps_dml_3518056860 bad WHERE NOT EXISTS (SELECT * FROM g_proj_dmlkps_dml_3518056860 good WHERE (bad.rtfactor = good.rtfactor) AND (bad.dm1 = good.dm1) AND (bad.dm2 = good.dm2) AND ((bad.val = good.val) OR ((bad.val is null) AND (good.val is null))))
550 )) THEN
551 __all_alright := ''false'';
552 END IF;
553 IF ((__all_alright = ''true'') AND EXISTS (
554 SELECT * FROM g_proj_dmlkps_dml_3518056860 good WHERE NOT EXISTS (SELECT * FROM proj_dmlkps_dml_3518056860 bad WHERE (bad.rtfactor = good.rtfactor) AND (bad.dm1 = good.dm1) AND (bad.dm2 = good.dm2) AND ((bad.val = good.val) OR ((bad.val is null) AND (good.val is null))))
555 )) THEN
556 __all_alright := ''false'';
557 END IF;
558 IF (__all_alright = ''false'') THEN
559 UPDATE plcprj_projs SET lck = (plcprj_projs.lck + 1) WHERE name = ''proj_dmlkps_dml_3518056860'';
560
561 PERFORM sp_drop_all_table_indexes( ''proj_dmlkps_dml_3518056860'');
562
563 PERFORM sp_drop_all_table_indexes( ''g_proj_dmlkps_dml_3518056860'');
564 DROP TABLE IF EXISTS b_proj_dmlkps_dml_3518056860;
565 ALTER TABLE proj_dmlkps_dml_3518056860 RENAME TO b_proj_dmlkps_dml_3518056860;
566
567 CREATE INDEX ixc90_b_proj_dmlkps_dml_3518056860__rtfactor_dm1_dm2_valvpo
568 ON b_proj_dmlkps_dml_3518056860
569 USING btree
570 (rtfactor, dm1, dm2, val varchar_pattern_ops)
571 WITH (FILLFACTOR=90);
572 ALTER TABLE g_proj_dmlkps_dml_3518056860 RENAME TO proj_dmlkps_dml_3518056860;
573
574 CREATE INDEX ix90_proj_dmlkps_dml_3518056860__rtfactor_tflt724recid
575 ON proj_dmlkps_dml_3518056860
576 USING btree
577 (rtfactor, tflt724recid)
578 WITH (FILLFACTOR=90);
579
580 CREATE INDEX ix90_proj_dmlkps_dml_3518056860__rtfactor_tflt725recid
581 ON proj_dmlkps_dml_3518056860
582 USING btree
583 (rtfactor, tflt725recid)
584 WITH (FILLFACTOR=90);
585
586 CREATE INDEX ix90_proj_dmlkps_dml_3518056860__rtfactor_tflt829recid
587 ON proj_dmlkps_dml_3518056860
588 USING btree
589 (rtfactor, tflt829recid)
590 WITH (FILLFACTOR=90);
591
592 CREATE INDEX ix90_proj_dmlkps_dml_3518056860__rtfactor_tflt830recid
593 ON proj_dmlkps_dml_3518056860
594 USING btree
595 (rtfactor, tflt830recid)
596 WITH (FILLFACTOR=90);
597
598 CREATE INDEX ix90_proj_dmlkps_dml_3518056860__rtfactor_tflt831recid
599 ON proj_dmlkps_dml_3518056860
600 USING btree
601 (rtfactor, tflt831recid)
602 WITH (FILLFACTOR=90);
603
604 CREATE INDEX ix90_proj_dmlkps_dml_3518056860__rtfactor_dm1_dm2_valvpo
605 ON proj_dmlkps_dml_3518056860
606 USING btree
607 (rtfactor, dm1, dm2, val varchar_pattern_ops)
608 WITH (FILLFACTOR=90);
609
610 CREATE INDEX ixc90_proj_dmlkps_dml_3518056860__rtfactor_dm1_valvpo_dm2
611 ON proj_dmlkps_dml_3518056860
612 USING btree
613 (rtfactor, dm1, val varchar_pattern_ops, dm2)
614 WITH (FILLFACTOR=90);
615
616 CREATE INDEX ix90_proj_dmlkps_dml_3518056860__rtfactor_dm2
617 ON proj_dmlkps_dml_3518056860
618 USING btree
619 (rtfactor, dm2)
620 WITH (FILLFACTOR=90);
621 UPDATE plcprj_projs SET lck = (plcprj_projs.lck - 1) WHERE name = ''proj_dmlkps_dml_3518056860'';
622 ELSE
623 DROP TABLE g_proj_dmlkps_dml_3518056860;
624 END IF;
625 INSERT INTO plcprj_after_check (prjname, success) VALUES (''proj_dmlkps_dml_3518056860'', __all_alright);
626 END IF;
627 END;
628 '+'$'+'BODY'+'$'+'
629 LANGUAGE plpgsql VOLATILE; ';
630
631 insert into test_table(test_val) values('before inxerting the projection into table plcprj_todo pName: proj_dmlkps_dml_3518056860');
632 BEGIN
633 INSERT INTO plcprj_todo (name, projscript, lck, projspcall, spupdatecode) values ('proj_dmlkps_dml_3518056860' , __projscript , 0, __projspcall, __proj_sp_update_code);
634
635 insert into test_table(test_val) values('after inxerting the projection into table plcprj_todo');
636 EXCEPTION
637 WHEN raise_exception THEN
638 RAISE EXCEPTION '%', _failed_transaction_result;
639 WHEN others THEN
640 --CURSORS_FILLERS_REBOOT--
641 raise notice 'Transaction was rolled back';
642 END;
643 END IF;
644 __newprojections := 1;
645 END IF;
646 IF ((__newprojections)) THEN
647
648 insert into test_table(test_val) values('proj todo: proj_dmlkps_dml_3518056860');
649 ELSIF ((__sumOfParamTablesValues < 2)) THEN
650
651 insert into test_table(test_val) values('proj locked: proj_dmlkps_dml_3518056860');
652 ELSE
653
654 insert into test_table(test_val) values('proj not fit: proj_dmlkps_dml_3518056860');
655 END IF;
656
657
658 INSERT INTO t1 (parentid, recid, rt, mstmp, lck, lead, spid)
659 SELECT dml1.dm1, dml1.dm2, dm.rt, dm.mstmp, dm.lck ,t_projections_desc /*X1*/,pg_backend_pid()
660 FROM (SELECT dmlkps.val dm1, dmlkps.recid dm2, dmlkps.ft rt2 FROM dmlkps) dml1
661 JOIN dm ON dm.recid=dml1.dm2
662 left join dmlkps lkpordrd0_0 on lkpordrd0_0.recid=dml1.dm2 and lkpordrd0_0.ft=7150
663 left join dmdates dmordrd0_1 on dmordrd0_1.recid=lkpordrd0_0.val and dmordrd0_1.ft=3009
664 WHERE (1=1)
665
666 AND ( --bm2
667 dm.recid NOT IN (SELECT t_flt_0.recid FROM dmlkps t_flt_0 where t_flt_0.recid=dm.recid and t_flt_0.ft=7045 -- bm2.1
668 AND t_flt_0.val = (select recid from dmstrs where ft=4090 and val = cast(_rtfactor as varchar)+'_link_essence_autofilling' LIMIT (1))))
669 -- bm10
670
671
672 AND ( --bm3
673 dm.recid IN (SELECT xx6.recid FROM dm xx6
674 join dmlkps t_flt_7_24 on t_flt_7_24.recid=xx6.recid and t_flt_7_24.ft=7150
675 -- uid: bm8 3009
676 join dmdates t_flt_7_25 on t_flt_7_25.recid=t_flt_7_24.val and t_flt_7_25.ft =3009 -- bm8.1
677 -- bm8.2
678 AND t_flt_7_25.val between p_0 and p_1
679
680))
681
682 AND ( --bm3
683 dm.recid IN (SELECT /* bm22 */ dm.recid FROM dm xx7
684 join dmlkps t_flt_8_29 on t_flt_8_29.recid=/* bm22 */ dm.recid and t_flt_8_29.ft=7150
685 join dmlkps t_flt_8_30 on t_flt_8_30.recid=t_flt_8_29.val and t_flt_8_30.ft=7104
686 -- uid: bm8 7113
687 join dmlkps t_flt_8_31 on t_flt_8_31.recid=t_flt_8_30.val /* bm21 */ and t_flt_8_31.ft =7113 -- bm8.1
688 -- bm8.2
689 AND t_flt_8_31.val in (select UNNEST(a_2_val))))
690
691 AND dml1.rt2=7149
692 AND dml1.dm1 in (select UNNEST(array(select ___recid))) -- wierd performance issue after 5 executions pg 9.6: bad when dogin dml1.dm1 in( __recid), dml1.dm1 = ( __recid) , dml1.dm1 = (select __recid)
693 order by lower(COALESCE(rpad(cast(dmordrd0_1.val as timestamp), 23), '0000-00-00T00:00:00.000') + COALESCE(cast(dml1.dm2 as varchar), '')) using ~>~ LIMIT (__result_limit);
694 GET DIAGNOSTICS rowcount = ROW_COUNT;
695 END IF;
696 _rowcount := rowcount + _rowcount;
697 insert into test_table(test_val) values('number of rows added to the t'+t_projections_desc+' table : '+ cast(rowcount as varchar));
698 insert into test_table(test_val) values(''+'___recid='+cast(___recid as varchar)+' '+''+p_2_desc+'='+cast(p_2 as varchar)+' ');
699 insert into test_table(test_val) values('inxert number: ' + _projcounter);
700 OPEN __t_tables_results_cursor FOR SELECT parentid, recid, rt FROM t1 where lead = t_projections_desc and spid = pg_backend_pid() ORDER BY idtime desc LIMIT (rowcount);
701 FETCH __t_tables_results_cursor INTO _tcparentid, _tcrecid, _tcrt;
702 WHILE (FOUND) LOOP
703
704 insert into test_table(test_val) values('parentid val: '+ cast(_tcparentid as varchar)+ ' recid val: '+ cast(_tcrecid as varchar)+ ' rt val: '+ cast(_tcrt as varchar));
705 FETCH __t_tables_results_cursor INTO _tcparentid, _tcrecid, _tcrt;
706 END LOOP;
707 CLOSE __t_tables_results_cursor;
708
709 delete from t_projections_res where spid = pg_backend_pid() ;
710 insert into t_projections_res(prj_startwith,prj_rowcount,prj_newprojections,spid) select _startwith,_rowcount,__newprojections,pg_backend_pid() ;
711 END;
712
713$BODY$;
714
715ALTER FUNCTION public.sp_0_proj_dmlkps_dml_3518056860_11998224485(bigint, bigint, bigint, bigint, bigint, character varying, integer, bigint, integer, bigint, bigint, boolean, character varying, character varying, bigint, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying)
716 OWNER TO postgres;