· 9 years ago · Nov 14, 2016, 05:50 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 if (TG_OP = 'INSERT') then
66
67 if new.upd is null then
68 new.upd := now();
69 end if;
70 execute format( '
71 insert into "dbContact".tbimagefile (
72 filename
73 ,title
74 ,upd
75 ,filesize
76 ,pid
77 ,profile
78 ,token
79 ,thumbnail
80 ,uid
81 ,acc
82 ,filetype
83
84 ) values (
85 %s -- new.filename
86 ,%s -- new.title
87 ,%L -- new.upd
88 ,%s -- new.filesize
89 ,%s -- new.pid
90 ,%s -- new.profile
91 ,%s -- new.token
92 ,%s -- NEW.thumbnail
93 ,%s -- new.uid
94 ,%s -- new.acc
95 ,%s -- new.filetype
96 )
97 returning id, uid, acc
98
99 '
100 , fmval( new.filename )
101 , fmval( new.title )
102 , new.upd
103 , fmval( new.filesize )
104 , fmval( new.pid )
105 , fmval( new.profile )
106 , fmval( new.token )
107 , fmval( NEW.thumbnail )
108
109 , fmval( new.uid )
110 , fmval( new.acc )
111 , fmval(new.filetype )
112
113
114 ) into new.id, new.uid, new.acc ;
115
116 return NEW;
117 elseif (TG_OP = 'UPDATE') then
118 -- Create sql for field that have change
119 sql := sqlSetValue( OLD.filename, NEW.filename, sql, 'filename' );
120 sql := sqlSetValue( OLD.upd, NEW.upd, sql, 'upd' );
121 sql := sqlSetValue( OLD.filesize, NEW.filesize, sql, 'filesize' );
122 sql := sqlSetValue( OLD.title, NEW.title, sql, 'title' );
123 sql := sqlSetValue( OLD.profile, NEW.profile, sql, 'profile' );
124 sql := sqlSetValue( OLD.token, NEW.token, sql, 'token' );
125 sql := sqlSetValue( OLD.thumbnail, NEW.thumbnail, sql, 'thumbnail' );
126 sql := sqlSetValue( OLD.pid, NEW.pid, sql, 'pid' );
127 sql := sqlSetValue( OLD.uid, NEW.uid, sql, 'uid' );
128 sql := sqlSetValue( OLD.acc, NEW.acc, sql, 'acc' );
129 sql := sqlSetValue( OLD.filetype, NEW.filetype, sql, 'filetype' );
130 -- * Run sql
131 if sql is not null then
132 sql := format( 'update "%s".%s ' ,TABLE_SCHEMA, TABLE_NAME ) || sql || ' where id = ' ;
133 execute sql || new.id ;
134 end if;
135
136 return NEW;
137 elseif TG_OP = 'DELETE' then
138
139 --delete from "dbContact".tbimagefile where id = OLD.id;
140 perform "dbSys".grpdelete( old.id, format( '"%s".%s' ,TABLE_SCHEMA, TABLE_NAME ) );
141 return OLD;
142 end if;
143end ; $$ LANGUAGE plpgsql;
144
145
146/*****************************
147 View registration
148******************************/
149select "dbSys".viewRegistration( '"dbContact".tbImageFileVW'
150 -- , pers := '"dbLegal".tbPInv'
151 --, _delete_system_data := true
152 --,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null
153
154 --,_fnCheckPost := 'sumamt' -- Field to check cannot be changed when refered by posting data source
155
156 ,_OrderNo_Updatable:=true
157);