· 8 years ago · Dec 23, 2017, 03:52 PM
1alter table "dbContact".tbaddr add column latitude numeric , add column longitude numeric null ;
2
3
4/****************************************************************
5 tbAddrVW.sql
6
7 A collection of procedures related to View. Please follow this cnonvestional here.
8
9 2 functions as mandatory for view :-
10 - Prepare data for VIEW. Function name must be formated "Schema".tablenameVWM_UserData( ... )
11 - Update data fro VIEW. Function name must be formated "Schema".tablenameVWM_UpdData()
12 Remark: replace "Schema".tablename when implement .
13
14 Wutirkai Pornchai
15 02-12-2013
16*****************************************************************/
17/******************************************
18View Procedures
19*******************************************/
20 -- * Data selected for underlying user
21drop function if exists "dbContact".tbAddrVW_UserData ( in UserID int , in AccID int ,in _id int ) cascade;
22create or replace FUNCTION "dbContact".tbAddrVW_UserData ( in UserID int , in AccID int
23 ,in _id int = null
24)
25returns table (
26 id int
27 ,AddrNo text
28 ,Description text
29 ,adMail text
30 ,adNo text
31 ,adBlock text
32 ,adVillage text
33 ,adBuilding text
34 ,adFloor text
35 ,adSubStreet text
36 ,adStreet text
37 ,adCounty text
38 ,adDistrict text
39 ,adProvince text
40 ,PostCode text
41 ,PostCodeID int
42 ,Country text
43 ,UID int ,ACC int
44
45 ,adNearBy text
46 ,adAutoPrefix boolean
47 ,adPrefixFull boolean
48
49 ,latitude numeric
50 ,longitude numeric
51)
52 AS $$
53select
54 a.id
55 ,a.AddrNo
56 ,a.Description
57 ,a.adMail
58 ,a.adNo
59 ,a.adBlock
60 ,a.adVillage
61 ,a.adBuilding
62 ,a.adFloor
63 ,a.adSubStreet
64 ,a.adStreet
65 ,a.adCounty
66 ,a.adDistrict
67 ,a.adProvince
68 ,b.PostCode
69 ,a.PostCodeID
70 ,a.Country
71 ,a.UID ,a.ACC
72 ,a.adnearby
73 ,a.adAutoPrefix
74 ,a.adPrefixFull
75 ,a.latitude
76 ,a.longitude
77from
78 "dbContact".tbAddr a
79 left join "dbContact".tbpostcode b on (b.id = a.PostCodeID )
80where
81 --a.UID in (select UID from "dbUserAcc".tbaccuser where PID = AccID )
82 case
83 when _id is null then a.UID in (select UID from "dbUserAcc".tbaccuser where PID = AccID )
84 else true
85 end
86 and case
87 when _id is null then true
88 else a.id = _id
89 end
90
91$$ LANGUAGE sql;
92
93
94-- * How to update underlying tables whose data is projected to this view
95drop function if exists "dbContact".tbAddrVW_UpdData() cascade;
96create or replace function "dbContact".tbAddrVW_UpdData()
97returns trigger as $$
98declare Grp varchar; GrpID int; Change boolean;
99 _PostCodeID int = null ;
100 sql text = null;
101begin
102 if (TG_OP = 'INSERT') then
103
104 -- Get post code ID
105 if diff( null::text, new.PostCode ) then
106 if NEW.PostCode is not null then
107 select id from "dbContact".tbPostCode where postcode = NEW.PostCode into new.postCodeID;
108 end if;
109 end if;
110 /*
111 if NEW.PostCode is not null then
112 select id into _PostCodeID from "dbContact".tbPostCode where postcode = NEW.PostCode;
113 end if; */
114 if new.adAutoPrefix is null then
115
116 new.adAutoPrefix :=true;
117 end if;
118
119 if new.adPrefixFull then
120 new.adPrefixFull :=false ;
121 end if;
122 -- *
123 insert into "dbContact".tbaddr (
124 AddrNo
125 ,Description
126 ,adMail
127 ,adMailFormat
128 , adAutoPrefix
129 ,adNo
130 ,adBlock
131 ,adVillage
132 ,adBuilding
133 ,adFloor
134 ,adSubStreet
135 ,adStreet
136 ,adCounty
137 ,adDistrict
138 ,adProvince
139 ,PostCodeID
140 ,Country
141 ,UID ,ACC
142 ,adNearBy
143 ,adPrefixFull
144 ,latitude
145 ,longitude
146 ) values (
147 NEW.AddrNo
148 ,NEW.Description
149 ,NEW.adMail
150 ,true -- adMailFormat = true , To support html, no #13, use /n instead , Wut, 16-12-2013
151 --,true -- Enable auto prefix
152 ,new.adAutoPrefix
153 ,NEW.adNo
154 ,NEW.adBlock
155 ,NEW.adVillage
156 ,NEW.adBuilding
157 ,NEW.adFloor
158 ,NEW.adSubStreet
159 ,NEW.adStreet
160 ,NEW.adCounty
161 ,NEW.adDistrict
162 ,NEW.adProvince
163 -- ,_PostCodeID
164 ,new.PostCodeID
165 ,NEW.Country
166 ,NEW.UID ,NEW.ACC
167 ,new.adNearBy
168 ,new.adPrefixFull
169 ,new.latitude
170 ,new. longitude
171 )
172
173 -- unique (acc,adno,adblock,postcodeid )
174 on conflict (acc,adno,adblock,postcodeid ) do update set
175
176 adVillage=NEW.adVillage
177 ,adBuilding=NEW.adBuilding
178 ,adFloor=NEW.adFloor
179 ,adSubStreet=NEW.adSubStreet
180 ,adStreet=NEW.adStreet
181 ,adCounty=NEW.adCounty
182 ,adDistrict=NEW.adDistrict
183 ,adProvince=NEW.adProvince
184 ,Country=NEW.Country
185 ,adNearBy = new.adNearBy
186 ,adAutoPrefix = new.adAutoPrefix
187
188 returning id into new.id;
189
190 -- raise notice '171 new.admail"%"', new.admail;
191
192 if new.id is null then
193 select id from "dbContact".tbaddr where acc=new.acc and adno=new.adno and adblock=new.adblock and postcodeid=new.postcodeid into new.id;
194 end if;
195
196 return NEW;
197 elseif (TG_OP = 'UPDATE') then
198 -- Get post code ID
199 if diff( OLD.PostCode, new.PostCode ) then
200 if NEW.PostCode is not null then
201 select id from "dbContact".tbPostCode where postcode = NEW.PostCode into new.PostCodeID;
202 end if;
203 end if;
204
205 /*
206 if NEW.PostCode is not null then
207 select id into _PostCodeID from "dbContact".tbPostCode where postcode = NEW.PostCode;
208 end if; */
209
210 sql := sqlSetValue( OLD.AddrNo, NEW.AddrNo, sql, 'AddrNo' );
211 sql := sqlSetValue( OLD.Description, NEW.Description, sql, 'Description' );
212 sql := sqlSetValue( OLD.adMail, NEW.adMail, sql, 'adMail' );
213
214 sql := sqlSetValue( OLD.adNo, NEW.adNo, sql, 'adNo' );
215 sql := sqlSetValue( OLD.adBlock, NEW.adBlock, sql, 'adBlock' );
216 sql := sqlSetValue( OLD.adVillage, NEW.adVillage, sql, 'adVillage' );
217
218 sql := sqlSetValue( OLD.adBuilding, NEW.adBuilding, sql, 'adBuilding' );
219 sql := sqlSetValue( OLD.adFloor, NEW.adFloor, sql, 'adFloor' );
220 sql := sqlSetValue( OLD.adSubStreet, NEW.adSubStreet, sql, 'adSubStreet' );
221
222 sql := sqlSetValue( OLD.adStreet, NEW.adStreet, sql, 'adStreet' );
223 sql := sqlSetValue( OLD.adCounty, NEW.adCounty, sql, 'adCounty' );
224 sql := sqlSetValue( OLD.adDistrict, NEW.adDistrict, sql, 'adDistrict' );
225 sql := sqlSetValue( OLD.adProvince, NEW.adProvince, sql, 'adProvince' );
226
227 sql := sqlSetValue( OLD.PostCodeID, new.PostCodeID , sql, 'PostCodeID' );
228 sql := sqlSetValue( OLD.adPrefixFull, new.adPrefixFull , sql, 'adPrefixFull' );
229
230
231
232
233 /*
234 if _PostCodeID is not null then
235 sql := sqlSetValue( OLD.PostCodeID, _PostCodeID , sql, 'PostCodeID' );
236 end if;
237 */
238
239
240 sql := sqlSetValue( OLD.Country, NEW.Country, sql, 'Country' );
241 sql := sqlSetValue( OLD.UID, NEW.UID, sql, 'UID' );
242 sql := sqlSetValue( OLD.ACC, NEW.ACC, sql, 'ACC' );
243
244 sql := sqlSetValue( OLD.adNearBy, NEW.adNearBy, sql, 'adNearBy' );
245
246 sql := sqlSetValue( OLD.adAutoPrefix, NEW.adAutoPrefix, sql, 'adAutoPrefix' );
247 sql := sqlSetValue( OLD.latitude, NEW.latitude, sql, 'latitude' );
248 sql := sqlSetValue( OLD.longitude, NEW.longitude, sql, 'longitude' );
249
250
251 -- sql := sqlSetValue( null::int , NEW.acc , sql, 'acc' );
252
253
254 -- * Run sql
255 if sql is not null then
256 sql := 'update "dbContact".tbAddr ' || sql || ' where id = ' ;
257 execute sql || new.id ;
258 end if;
259
260 return NEW;
261 elseif TG_OP = 'DELETE' then
262
263 delete from "dbContact".tbAddr where id = OLD.id;
264
265 return OLD;
266 end if;
267end ; $$ LANGUAGE plpgsql;
268
269
270
271 /*****************************
272 View registration
273 ******************************/
274 select "dbSys".viewRegistration( '"dbContact".tbaddrvw'
275 -- , pers := '"dbPM".tbTask'
276 -- , _delete_system_data := true
277 -- ,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null
278 -- ,_fnCheckPost := <fn> -- Field to check cannot be changed when refered by posting data source
279
280 ,_matview :=true
281 -- ,_mvAccID_share := 4
282 --,_mvOrderBy := 'firstname, lastname'
283 --,_mvStrict := true -- Check data is in scope of view when insert
284 /*
285 ,_mvForeDS := '"dbContact".tbpostcodevw'
286 ,_mvForeFK := 'PostCodeID' -- Foreign key refer to master datasource
287 ,_mvForePK := 'id' -- PK of master datasource, default 'id'
288 */
289 );
290 --* create view
291 select * from "dbSys".tvCreateView ( 62, 4, '"dbContact".tbaddr', dropit:=true); -- return "dbContact".tbContactVW4