· 10 years ago · Aug 27, 2016, 11:42 PM
1create or replace function "dbSys".create_CannotChangeRefered(
2 in tblvw99 text -- Full qualified view name , "dbLegal".tbpinvvw4
3 ,in fnCheckPost text
4 ,in userid int
5 ,in accid int
6)
7returns void as $$
8declare
9 cur_post refcursor; vw record;
10 par record; postdata record; tb record; rec record;
11 pcname text; tgname text;fnname text; sql text;
12 ok boolean;
13begin
14 --* Get table persystance
15 select
16 aa.sch
17 ,lower(aa.nam) nam
18 ,cutstr(lower(aa.nam),'vw') nam_noac
19 ,b.sch sch_pers -- w/o quoat
20 ,b.nam nam_pers
21 from "dbSys".getTableSchName( tblvw99 ) aa
22 left join "dbSys".tbviewregistration a on (a.sch=aa.sch and a.nam = cutstr(lower(aa.nam),'vw') )
23 left join "dbSys".tbtblregis b on ( b.id = a.tblregis_id )
24 into tb;
25
26 -- raise notice '1494, tb.sch_pers %, nam_pers %' , tb.sch_pers ,tb.nam_pers;
27 --* Only have persistence, and have field to check for any refering
28 if (tb.sch_pers is not null) and (fnCheckPost is not null ) then
29 open cur_post for execute format(
30 'select * from "dbSys".tbpostdataregis where tartn = %L
31 and postdatatype = %L
32 '
33 , format( '%I.%s',tb.sch_pers,tb.nam_pers)
34 ,'Sum'
35 );
36
37 loop
38 fetch cur_post into postdata; if not found then exit; end if;
39 --- raise notice '79, postdata, sch %,nam %',postdata.sch, postdata.nam;
40 if (postdata.id is not null ) and (postdata.lock_edit) then
41
42 --* Get parent
43 select
44 a.fk_table_schema sch
45 , a.fk_table_name nam,
46 quote_ident(a.fk_table_schema)||'.'|| a.fk_table_name tbl -- parent full qualified table name
47 from "dbSys".getForeignKey( format('%s.%s',postdata.sch,postdata.nam), 'pid' ) a
48 into par;
49
50 --* Trigger to abandon chainging approve, if having posting and it's not master-detail posting
51
52 ok := case
53 when (par.sch is null) then true
54 when (par.nam is null) then true
55 else
56 case
57 when diff(postdata.tartn, format('%I.%s',par.sch,par.nam) ) then true
58 else false
59 end
60 end ;
61
62 --raise notice '1525,postdata.nam %, par.shc %, par.nam %, postdata.tartn=%, ok=%',postdata.nam,par.sch, par.nam,postdata.tartn , ok ;
63 -- postdata.nam tbpinvitem, par.shc dbFinance, par.nam tbpinv, postdata.tartn="dbLegal".tbpinv, ok=t
64
65 if ok then
66 --if true then
67 select a.sch ,lower(a.nam) nam
68 --,tblvw99 tbwkfrec_name
69 ,format('%I.%s',tb.sch_pers,tb.nam_pers) tbwkfrec_name
70 from "dbSys".getTableSchName( postdata.tartn ) a into rec;
71 -- select tbwkfrec_name from "dbSysWKF".tbworkflowregis where sch = quote_ident(rec.sch) and nam= rec.nam into rec ;
72
73 if rec.tbwkfrec_name is not null then
74 -- if rec.sch is not null then
75 select a.sch ,lower(a.nam) nam ,rec.tbwkfrec_name from "dbSys".getTableSchName( rec.tbwkfrec_name ) a into rec;
76
77 pcname := 'CannotChangeRefered';
78 tgName := format('%s_%s_%s_%s_%s', rec.nam, pcname,fnCheckPost,postdata.tarfn ,postdata.nam );
79 -- raise notice '624, tgname=%, rec.tbwkfrec_name=%, tblvw99=%',tgname, postdata.tartn, tblvw99 ;
80 execute format( 'drop trigger if exists %s on %s cascade', tgname, rec.tbwkfrec_name );
81 --* Posting table may not does exist, check it
82 if "dbSys".haveTable(format('%s.%s',postdata.sch,postdata.nam)) then
83
84 --*
85 if "dbSys".haveColumn( postdata.tartn, postdata.tarfn ) then
86 --* Trigger : target table
87
88 -- select a.sch ,lower(a.nam) nam from "dbSys".getTableSchName( postdata.tartn ) a into rec;
89
90
91 if ok then
92 fnname := format('%I.%s','dbSys' ,pcname );
93 sql := format(
94 ' -- ** fnview.sql **--
95 create trigger %s -- creat trigger
96 after update on %s -- after ..on table
97 for each row execute procedure %s(
98 %L -- 1
99 ,%L -- 2
100 ,%L
101 ,%L
102 ,%L -- 5
103 )',tgname -- create trigger
104 ,rec.tbwkfrec_name -- after .. on table
105 , fnname -- procedure
106 ,'' -- 1
107 ,format( '%s.%s',postdata.sch, postdata.nam ) -- 2
108 ,rec.tbwkfrec_name
109 ,postdata.tarfn
110 ,fnCheckPost -- 5
111 );
112 ---raise notice '127, sql=%',sql;
113 execute sql;
114 end if;
115 else
116 raise exception 'No column "%" in "%"',postdata.tarfn, postdata.tartn ;
117 end if;
118 end if;
119 end if;
120 end if;
121 end if;
122 end loop;
123 close cur_post;
124 end if;
125
126 /*************************
127 Recreate view for target. To make target aware of this posting source
128 Wut, 2015-07-21
129
130 select * from "dbSys".tbpostdataregis where sch = '"dbLegal"' and nam = 'tbcasecollitem'
131
132 ***************************/
133
134 -- raise notice '1677, tb.sch_pers=%,tb.nam_pers=%, tb.sch=%, tb.nam %', tb.sch_pers, tb.nam_pers, tb.sch , tb.nam ;
135 --* Only for having persistant
136 if tb.sch_pers is not null then
137 --* Get parent
138 select
139 a.fk_table_schema sch
140 , a.fk_table_name nam,
141 quote_ident(a.fk_table_schema)||'.'|| a.fk_table_name tbl -- parent full qualified table name
142 from "dbSys".getForeignKey( format('"%s".%s',tb.sch_pers, tb.nam_pers ), 'pid' ) a
143 into par;
144 --*
145 sql := format(
146 'select a.*, c.sch sch_vw, c.nam nam_vw
147 from "dbSys".tbpostdataregis a
148 left join "dbSys".getTableSchName( a.tartn ) aa on ( format(%L,aa.sch,aa.nam) = a.tartn) -- 1
149 left join "dbSys".tbtblregis b on ( b.sch = aa.sch and b.nam = aa.nam )
150 left join "dbSys".tbviewregistration c on ( c.tblregis_id = b.id )
151 where a.sch = %L and a.nam = %L and c.nam is not null ' -- 2
152 ,'%I.%s' -- 1
153 ,format('%I',tb.sch_pers),tb.nam_pers -- 2
154 );
155
156 -- raise notice '1995, par.sch %,par.nam %, full name=%, sql=%', par.sch, par.nam, format('"%s".%s',tb.sch_pers, tb.nam_pers ),sql;
157 open cur_post for execute sql;
158 loop
159 fetch cur_post into postdata; if not found then exit; end if;
160 --raise notice '1999, tartn %, tarfn %, par.sch %, par.nam %', postdata.tartn, postdata.tarfn, par.sch , par.nam ;
161 ok := case
162 when (par.sch is null) then true
163 when (par.nam is null) then true
164 else
165 case
166 when diff(postdata.tartn, format('%I.%s',par.sch,par.nam) ) then true
167 else false
168 end
169 end ;
170
171 if ok then
172 -- raise notice '2011, tartn %, tarfn %, par.sch %, par.nam %,rec.sch_vw %,rec.name_vw %' , postdata.tartn, postdata.tarfn, par.sch , par.nam,postdata.sch_vw, postdata.nam_vw ;
173 /*
174 sql := format(
175 'drop view if exists "%s".%s%s'
176 ,postdata.sch_vw, postdata.nam_vw, accid
177
178 );
179 raise notice '1728, sql = %', sql;
180 execute sql;
181 */
182 sql := format(
183 -- 'select "dbSys".tvCreateView ( %s, %s, %L )'
184 'select "dbSys".tvCreateView ( %s, %s, %L , dropit:=true )'
185
186 ,userid, accid, postdata.tartn
187
188 );
189 --raise notice '1736, sql=%',sql;
190 execute sql;
191 end if;
192 end loop;
193 close cur_post;
194 end if;
195
196end $$ language plpgsql;