· 9 years ago · Oct 15, 2016, 11:54 AM
1/****************************************************************
2 tbContTypeVW.sql
3
4 A collection of procedures related to View. Please follow this cnonvestional here.
5
6 2 functions as mandatory for view :-
7 - Prepare data for VIEW. Function name must be formated "Schema".tablenameVWM_UserData( ... )
8 - Update data fro VIEW. Function name must be formated "Schema".tablenameVWM_UpdData()
9 Remark: replace "Schema".tablename when implement .
10
11 Wutirkai Pornchai
12 19-12-2013
13*****************************************************************/
14/******************************************
15View Procedures
16*******************************************/
17 -- * Data selected for underlying user
18drop function if exists "dbContact".tbContTypeVW_UserData ( in UserID int , in AccID int ) cascade;
19create or replace FUNCTION "dbContact".tbContTypeVW_UserData ( in UserID int , in AccID int )
20returns table
21( id int
22 ,ContType varchar
23 ,Abbr text
24 ,Kind text
25 ,grp varchar
26 ,SystemType text
27 -- * Basic data fields in group and order table
28 ,fqty smallint
29 --, sqty smallint
30 , dis smallint
31 , idgrp int
32 , idprev int
33 , seq int
34 , path int[]
35 ,orderno smallint
36 -- * User and Acc
37 ,UID int, ACC int
38 ,grp_abbr text
39
40
41)
42 AS $$
43 select
44 a.id
45 ,a.ContType
46 ,a.abbr
47 ,a.Kind
48 , b.ContType Grp
49 ,a.SystemType
50 ,a.fqty
51 --,a.sqty
52 , a.dis
53 ,a.idgrp
54 , a.idprev
55 , a.seq
56 , z.path
57 ,a.orderno
58 , a.UID, a.ACC
59 ,case
60 when a.abbr is not null then concat_ws(' ',b.conttype, a.abbr )
61 end
62 from
63 "dbContact".tbContType a
64 left join "dbContact".tbContType b on (b.id = a.idgrp )
65 --right join "dbSys".grpGetData( UserID,AccID, '"dbContact".tbContType' ) z on ( z.id = a.id )
66 left join "dbSys".grpGetData( UserID,AccID, '"dbContact".tbContType',path_only:=true ) z on ( z.id = a.id )
67
68 where a.acc= accid
69 order by z.path;
70
71$$ LANGUAGE sql;
72
73-- * How to update underlying tables whose data is projected to this view
74drop function if exists "dbContact".tbContTypeVW_UpdData() cascade;
75create or replace function "dbContact".tbContTypeVW_UpdData()
76returns trigger as $$
77declare Grp varchar; GrpID int; Change boolean; sql text; lastid int;
78begin
79 if (TG_OP = 'INSERT') then
80 if new.systemtype is null then
81 new.systemType := 'Default';
82 end if;
83 -- raise notice 'Inser/Upd:%, %, sysemt type=%', 'dbContact', tg_name, new.systemType;
84 Grp := NEW.Grp;
85 if Grp is not null then
86 -- * Get correpodance id
87 select id into new.idgrp from "dbContact".tbContType where ACC = NEW.ACC and ContType = Grp;
88 end if;
89
90 -- * Insert data to underlying table
91 insert into "dbContact".tbContType
92 (ContType , Kind , idgrp, systemtype, UID ,ACC, Abbr )
93 values
94 ( NEW.ContType, NEW.Kind, new.idgrp,new.systemtype, NEW.UID, NEW.ACC, new.abbr )
95 returning id into lastid;
96
97 -- * Move to its group, if need
98 /*
99 if NEW.grp is not null then
100 perform "dbSys".grpmove_id_to_childofid(lastid, grpid,'"dbContact".tbContType' );
101 end if; */
102 new.id := lastid;
103
104 return NEW;
105
106 elseif (TG_OP = 'UPDATE') then
107 -- raise notice 'Inser/Upd:%, %', 'dbContact', tg_name;
108 if diff( NEW.Grp , OLD.grp ) then
109 if new.grp is not null then
110 select id from "dbContact".tbContType where ACC = NEW.ACC and ContType = new.grp into new.idgrp;
111 else
112 new.idgrp := null;
113 end if;
114 end if;
115
116 -- * Create sql for updating -----
117 sql := null;
118 sql := sqlSetValue( OLD.ContType, NEW.ContType, sql, 'ContType' );
119 sql := sqlSetValue( OLD.Kind, NEW.Kind, sql, 'Kind' );
120 sql := sqlSetValue( OLD.idgrp, NEW.idgrp, sql, 'idgrp' );
121 sql := sqlSetValue( OLD.systemtype, NEW.systemtype, sql, 'systemtype' );
122
123 sql := sqlSetValue( OLD.abbr, NEW.abbr, sql, 'abbr' );
124
125
126 -- raise notice 'sql = %', sql;
127 -- * Run sql, if any change
128 if sql is not null then
129 sql := 'update "dbContact".tbContType ' || sql || ' where id = ' || new.id;
130 execute sql ;
131 end if;
132 return NEW;
133 elseif TG_OP = 'DELETE' then
134 -- raise notice 'del:%, %', 'dbContact', tg_name;
135 perform "dbSys".grpDelete( OLD.id, '"dbContact".tbContType' );
136 return OLD;
137 end if;
138end ; $$ LANGUAGE plpgsql;
139
140
141/*****************************
142 View registration
143******************************/
144select "dbSys".viewRegistration( '"dbContact".tbContTypeVW'
145 , _delete_system_data := false
146 --, _delete_system_data := true
147 --,_init_data := false, _init_data_uid := 1 ,_init_data_acc := null
148 ,_init_data := true, _init_data_uid := varint('userid'),_init_data_acc := varint('accid') , _init_sysorg:=true
149 ,_OrderNo_Updatable :=true
150);
151
152
153select "dbSys".tvCreateView ( 47, 12, '"dbContact".tbContType', dropit:=true);