· 8 years ago · Mar 06, 2018, 03:54 PM
1/****************************************************************
2 tbDebtCalcVW.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
9
10 Wutirkai Pornchai
11
12*****************************************************************/
13/******************************************
14View Procedures
15*******************************************/
16 -- * Data selected for underlying user
17drop function if exists "dbLegal".tbDebtCalcVW_UserData ( in UserID int , in AccID int
18 ,in _id int
19) cascade;
20create or replace FUNCTION "dbLegal".tbDebtCalcVW_UserData ( in UserID int , in AccID int
21 ,in _id int = null
22
23)
24returns table (
25 ID int -- not null default nextval('"dbLegal".tbDebtCalc_Seq') primary key
26
27 ,UPD timestamp --default now() -- To support which is lastest
28 ,DateStop date -- null
29 ,Calc boolean -- default false -- Refresh calculation using DateStop
30
31 ,UID int -- not null references "dbUserAcc".tbUser(ID) -- unsigned unsigned null comment 'User Name/ID',
32 ,ACC int -- not null references "dbUserAcc".tbAcc( ID)
33
34 ,rploan_calc boolean
35 ,rploancalcid int
36 ,rploan_create boolean
37
38 ,assettypeid int
39) AS $$
40declare sql text; foundACC int;
41begin
42
43 return query
44
45 select
46 a.ID -- int -- not null default nextval('"dbLegal".tbDebtCalc_Seq') primary key
47 ,a.UPD -- timestamp --default now() -- To support which is lastest
48 ,a.DateStop ::date -- date -- null
49 ,a.Calc -- boolean -- default false -- Refresh calculation using DateStop
50 ,a.UID -- int -- not null references "dbUserAcc".tbUser(ID) -- unsigned unsigned null comment 'User Name/ID',
51 ,a.ACC -- int -- not null references "dbUserAcc".tbAcc( ID)
52
53 ,a.rploan_calc
54 ,a.rploancalcid
55 ,a.rploan_create
56 ,a.assettypeid
57 from
58 "dbLegal".tbdebtcalc a
59 where
60 -- a.UID in (select a1.UID from "dbUserAcc".tbaccuser a1 where a1.PID = AccID )
61
62 case
63 when _id is null then a.acc=accid
64 else a.id = _id
65 end
66 ;
67
68end;
69$$ LANGUAGE plpgsql ;
70
71-- * How to update underlying tables whose data is projected to this view
72drop function if exists "dbLegal".tbDebtCalcVW_UpdData() cascade;
73create or replace function "dbLegal".tbDebtCalcVW_UpdData()
74returns trigger as $$
75declare RecID int; sql text;
76begin
77 if (TG_OP = 'INSERT') then
78 if new.calc is null then
79 new.calc := false;
80 end if;
81 if new.upd is null then
82 new.upd := now();
83 end if;
84 if new.rploan_create is null then
85 new.rploan_create:=true;
86 end if;
87 if new.rploan_calc is null then
88 new.rploan_calc:=false;
89 end if;
90 insert into "dbLegal".tbdebtcalc (
91 UPD --timestamp --default now() -- To support which is lastest
92 ,DateStop -- date -- null
93 ,Calc -- boolean -- default false -- Refresh calculation using DateStop
94
95 ,UID --int -- not null references "dbUserAcc".tbUser(ID) -- unsigned unsigned null comment 'User Name/ID',
96 ,ACC --int
97 ,rploan_calc
98 ,rploancalcid
99 ,rploan_create
100
101 ) select
102 new.UPD --timestamp --default now() -- To support which is lastest
103 ,new.DateStop ::timestamp_wtz-- date -- null
104 ,new.Calc -- boolean -- default false -- Refresh calculation using DateStop
105
106 ,new.UID --int -- not null references "dbUserAcc".tbUser(ID) -- unsigned unsigned null comment 'User Name/ID',
107 ,new.ACC --int
108
109 ,new.rploan_calc
110 ,new.rploancalcid
111 ,new.rploan_create
112
113 returning id ,calc into new.id ,new.calc, new.rploancalcid ;
114 return NEW;
115 elseif (TG_OP = 'UPDATE') then
116
117 -- Main
118 sql :=null;
119 sql := "dbSys".sqlSetValue( OLD.UPD, NEW.UPD, sql, 'UPD' );
120 sql := "dbSys".sqlSetValue( OLD.DateStop::timestamp_wtz, NEW.DateStop::timestamp_wtz, sql, 'DateStop' );
121 sql := "dbSys".sqlSetValue( OLD.Calc, NEW.Calc, sql, 'Calc' );
122 sql := "dbSys".sqlSetValue( OLD.UID, NEW.UID, sql, 'UID' );
123
124 sql := "dbSys".sqlSetValue( OLD.rploan_calc, NEW.rploan_calc, sql, 'rploan_calc' );
125 sql := "dbSys".sqlSetValue( OLD.rploancalcid, NEW.rploancalcid, sql, 'rploancalcid' );
126
127
128 sql := "dbSys".sqlSetValue( OLD.rploan_create, NEW.rploan_create, sql, 'rploan_create' );
129
130
131
132 if sql is not null then
133 sql := 'update "dbLegal".tbDebtCalc ' || sql || ' where id = '|| new.id
134
135 || 'returning rploan_calc, rploancalcid '
136 ;
137 execute sql into new.rploan_calc ,new.rploancalcid ;
138 end if;
139
140 return NEW;
141 elseif TG_OP = 'DELETE' then
142 delete from "dbLegal".tbDebtCalc where id = old.id ;
143 return OLD;
144 end if;
145end ; $$ LANGUAGE plpgsql;
146
147
148
149
150/*****************************
151 View registration
152******************************/
153select "dbSys".viewRegistration( '"dbLegal".tbDebtCalcVW'
154 --, _delete_system_data := true
155 -- ,_init_data := true, _init_data_uid := 1 ,_init_data_acc := 4 , _init_sysorg := true
156 --,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null , _init_sysorg := true
157
158);