U8hrch0b

1				drop function if exists  "dbContact".tbcontaddrVW_UserData ( in UserID int , in AccID int )  cascade; -- 1
2				create  or replace FUNCTION "dbContact".tbcontaddrVW_UserData ( in UserID int , in AccID int )        -- 2
3				returns table (
4					id int 
5			,title text
6      ,tcustom text 
7      ,tvalue integer
8      ,adMail text  
9      ,pid integer
10      ,uid integer     -- 2.1
11	) AS $x$
12				begin
13				 return query 
14				 select
15					 a.id
16					 ,a.title
17       ,a.tcustom ::text
18       ,a.tvalue
19       ,a2.admail  ::text
20       ,a.pid
21       ,a.uid       -- 3
22				 from
23					 "dbContact".tbcontaddr a     -- 4
24					 left join "dbContact".tpcontaddrtype a1 on (a1.field=a.title)
25       left join "dbContact".tbaddr a2 on (a2.id=a.tvalue)
26       left join "dbContact".tbcontact a3 on (a3.id=a.pid)
27       left join "dbUserAcc".tbuser a4 on (a4.id=a.uid)       -- 5
28					 where
29       a3.uid in (select u.uid from "dbUserAcc".tbaccuser u where u.pid = accid)       -- 6
30				 ;
31				end $x$ LANGUAGE plpgsql;  
32
33/***********************
34      Update data
35    ************************/
36    drop function if exists "dbContact".tbcontaddrVW_UpdData()  cascade; -- 1
37		create or replace function "dbContact".tbcontaddrVW_UpdData() -- 2
38		returns trigger as $x$
39    declare sql text=null;
40		begin
41			if (TG_OP =  'INSERT') then   -- 3
42			  insert into "dbContact".tbcontaddr( -- 3.1
43          title
44          ,tcustom
45          ,tvalue
46          ,pid
47          ,uid   -- 3.2
48        ) select 
49          new.title
50          ,new.tcustom
51          ,new.tvalue
52          ,new.pid
53          ,new.uid   -- 3.3
54        returning id into new.id;
55				return NEW;
56			elseif (TG_OP =  'UPDATE' ) then  -- 4
57        sql := "dbSys".sqlSetValue( OLD.title, NEW.title, sql,'title' );
58        sql := "dbSys".sqlSetValue( OLD.tcustom, NEW.tcustom, sql,'tcustom' );
59        sql := "dbSys".sqlSetValue( OLD.tvalue, NEW.tvalue, sql,'tvalue' );
60        sql := "dbSys".sqlSetValue( OLD.pid, NEW.pid, sql,'pid' );
61        sql := "dbSys".sqlSetValue( OLD.uid, NEW.uid, sql,'uid' );   -- 5.1
62        if sql is not null then 
63          sql:= format('update "dbContact".tbcontaddr %s where id = %s',sql, new.id);   -- 5.2
64          execute sql;
65        end if;
66				return NEW;
67 			elseif TG_OP = 'DELETE' then   -- 5
68         delete from "dbContact".tbcontaddr where id = old.id;   -- 6
69        -- perform "dbSys".grpdelete( old.id, '"dbContact".tbcontaddr' );  -- 6.1
70        return OLD;
71      end if;
72		end  $x$ LANGUAGE plpgsql;
73
74 
75select "dbSys".tvCreateView ( 5, 4, '"dbContact".tbcontaddr',dropit:=true );   
76select "dbSys".tvCreateView ( 47, 12, '"dbContact".tbAddr',dropit:=true );  -- return "dbContact".tbAddrVW4
77
78select * from "dbContact".tbcontaddrVW4;
79select * from "dbContact".tbAddrVW12;