· 9 years ago · Nov 14, 2016, 06:54 AM
1/****************************************************************
2 View : Doc image file
3
4 Wutirkai Pornchai
5 2016-02-13
6*****************************************************************/
7/******************************************
8View Procedures
9*******************************************/
10 -- * Data selected for underlying user
11drop function if exists "dbContact".tbImageFileVW_UserData ( in UserID int , in AccID int ) cascade;
12create or replace FUNCTION "dbContact".tbImageFileVW_UserData ( in UserID int , in AccID int )
13returns table (
14 id int
15 ,Title text
16 ,FileName text
17 ,pid int
18 ,idgrp int
19 ,dis smallint
20 ,OrderNo smallint
21 ,upd timestamp_wtz
22 ,FileSize num
23 ,profile boolean
24 ,token text
25 ,thumbnail text
26 ,uid int
27 ,acc int
28 ,filetype text
29)
30 AS $$
31select
32 a.id
33 ,a.title
34 ,a.filename
35 ,a.pid
36 ,a.idgrp
37 ,a.dis
38 ,a.orderno
39 ,a.upd
40 ,a.filesize
41 ,a.profile
42 ,a.token
43 ,a.thumbnail
44 ,a.uid
45 ,a.acc
46 ,a.filetype
47from
48 "dbContact".tbImageFile a
49
50where
51 -- a.UID in (select UID from "dbUserAcc".tbaccuser where PID = AccID )
52 a.acc = accid
53
54$$ LANGUAGE sql;
55
56
57-- * How to update underlying tables whose data is projected to this view
58drop function if exists "dbContact".tbImageFileVW_UpdData() cascade;
59create or replace function "dbContact".tbImageFileVW_UpdData()
60returns trigger as $$
61declare sql text = null;
62 TABLE_SCHEMA text = tg_argv[0];
63 TABLE_NAME text = tg_argv[1];
64begin
65 raise notice '65 TABLE_SCHEMA %', TABLE_SCHEMA;
66 if (TG_OP = 'INSERT') then
67
68 if new.upd is null then
69 new.upd := now();
70 end if;
71 execute format( '
72 insert into "%s".%s (
73 filename
74 ,title
75 ,upd
76 ,filesize
77 ,pid
78 ,profile
79 ,token
80 ,thumbnail
81 ,uid
82 ,acc
83 ,filetype
84
85 ) values (
86 %s -- new.filename
87 ,%s -- new.title
88 ,%L -- new.upd
89 ,%s -- new.filesize
90 ,%s -- new.pid
91 ,%s -- new.profile
92 ,%s -- new.token
93 ,%s -- NEW.thumbnail
94 ,%s -- new.uid
95 ,%s -- new.acc
96 ,%s -- new.filetype
97 )
98 returning id, uid, acc
99
100 ' , TABLE_SCHEMA, TABLE_NAME
101 , fmval( new.filename )
102 , fmval( new.title )
103 , new.upd
104 , fmval( new.filesize )
105 , fmval( new.pid )
106 , fmval( new.profile )
107 , fmval( new.token )
108 , fmval( NEW.thumbnail )
109
110 , fmval( new.uid )
111 , fmval( new.acc )
112 , fmval(new.filetype )
113
114
115 ) into new.id, new.uid, new.acc ;
116
117 return NEW;
118 elseif (TG_OP = 'UPDATE') then
119 -- Create sql for field that have change
120 sql := sqlSetValue( OLD.filename, NEW.filename, sql, 'filename' );
121 sql := sqlSetValue( OLD.upd, NEW.upd, sql, 'upd' );
122 sql := sqlSetValue( OLD.filesize, NEW.filesize, sql, 'filesize' );
123 sql := sqlSetValue( OLD.title, NEW.title, sql, 'title' );
124 sql := sqlSetValue( OLD.profile, NEW.profile, sql, 'profile' );
125 sql := sqlSetValue( OLD.token, NEW.token, sql, 'token' );
126 sql := sqlSetValue( OLD.thumbnail, NEW.thumbnail, sql, 'thumbnail' );
127 sql := sqlSetValue( OLD.pid, NEW.pid, sql, 'pid' );
128 sql := sqlSetValue( OLD.uid, NEW.uid, sql, 'uid' );
129 sql := sqlSetValue( OLD.acc, NEW.acc, sql, 'acc' );
130 sql := sqlSetValue( OLD.filetype, NEW.filetype, sql, 'filetype' );
131 -- * Run sql
132 if sql is not null then
133 sql := format( 'update "%s".%s ' ,TABLE_SCHEMA, TABLE_NAME ) || sql || ' where id = ' ;
134 execute sql || new.id ;
135 end if;
136
137 return NEW;
138 elseif TG_OP = 'DELETE' then
139
140 --delete from "dbContact".tbimagefile where id = OLD.id;
141 perform "dbSys".grpdelete( old.id, format( '"%s".%s' ,TABLE_SCHEMA, TABLE_NAME ) );
142 return OLD;
143 end if;
144end ; $$ LANGUAGE plpgsql;
145
146
147/*****************************
148 View registration
149******************************/
150select "dbSys".viewRegistration( '"dbContact".tbImageFileVW'
151 -- , pers := '"dbLegal".tbPInv'
152 --, _delete_system_data := true
153 --,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null
154
155 --,_fnCheckPost := 'sumamt' -- Field to check cannot be changed when refered by posting data source
156
157 ,_OrderNo_Updatable:=true
158);