SHARE
TWEET

Untitled

a guest May 19th, 2017 41 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /****************************************************************
  2.  tbCaseAssetAmtVW.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.   02-05-014
  13.  
  14. *****************************************************************/
  15. /******************************************
  16. View Procedures
  17. *******************************************/
  18.  -- * Data selected for underlying user
  19. drop function if exists  "dbLegal".tbCaseAssetAmtVW_UserData ( in UserID int , in AccID int )  cascade;
  20. create  or replace FUNCTION "dbLegal".tbCaseAssetAmtVW_UserData ( in UserID int , in AccID int )
  21. returns table (
  22.     id int
  23.         ,Title text
  24.     ,TCustom text
  25.         ,Amount num  
  26.     ,is_total boolean
  27.         ,pid int
  28.     ,uid int
  29.     ,orderno smallint
  30. ) AS $$
  31.  
  32. begin
  33.    return query
  34.  
  35.         select
  36.       a.id
  37.  
  38.       ,b.title ::text
  39.       ,case when a.tcustom is null then b.tlabel  else  a.tcustom  end :: text
  40.  
  41.       --,case when a.tcustom is null then b.tlabel  else  a.tcustom  end :: text
  42.       --,b.tcustom ::text
  43.  
  44.       --,a.tcustom::text  title
  45.       --,b.tcustom ::text
  46.  
  47.       ,a.tvalue  amount  
  48.              ,case
  49.                     when b.title = 'Total' and b.tcustom is null then true
  50.                     else false
  51.                 end
  52.       ,a.pid
  53.             ,a.uid
  54.       ,b.orderno
  55.     from
  56.       "dbLegal".tbCaseAssetAmt a
  57.       left join "dbLegal".tbCaseAmt b on ( b.id = a.CaseAmtID      )
  58.        --right join "dbLegal".tbCaseAmt b on ( b.id = a.CaseAmtID      )
  59.  
  60.       -- right join "dbLegal".tbCaseAsset c on (c.id = a.pid )
  61.         where    
  62.       -- a.CaseAmtID is not null
  63.             -- and a.UID in (select a1.UID from "dbUserAcc".tbaccuser a1 where a1.PID =  AccID )
  64.       b.acc = accid
  65.       and not b.hide
  66.     order by
  67.       a.pid, b.orderno ;
  68.  
  69. end;
  70. $$ LANGUAGE plpgsql ;
  71.  
  72. -- * How to update underlying tables whose data is projected to this view
  73. drop function if exists "dbLegal".tbCaseAssetAmtVW_UpdData()  cascade;
  74. create or replace function "dbLegal".tbCaseAssetAmtVW_UpdData()
  75. returns trigger as $$
  76.  
  77. begin
  78.   if (TG_OP = 'INSERT') then
  79.  
  80.     return NEW;
  81.   elseif (TG_OP = 'UPDATE') then
  82.  
  83.     return NEW;
  84.   elseif TG_OP = 'DELETE' then
  85.     --  delete from "dbLegal".tbCaseAssetAmt where id = old.id ;
  86.     return OLD;
  87.   end if;
  88. end ; $$ LANGUAGE plpgsql;
  89.  
  90. /*****************************
  91.   View registration
  92. ******************************/
  93. select "dbSys".viewRegistration( '"dbLegal".tbCaseAssetAmtVW'
  94.   --, _delete_system_data := true
  95.   -- ,_init_data := true, _init_data_uid := 1 ,_init_data_acc := 4 , _init_sysorg := true
  96.   --,_init_data := true, _init_data_uid := 1 ,_init_data_acc := null , _init_sysorg := true
  97.  
  98. );
  99.  
  100.  
  101. select * from "dbSys".tvcreateview( varint('userid'), varint('accid'), '"dbLegal".tbCaseAssetAmt' );
RAW Paste Data
Challenge yourself this year...
Learn something new in 2017
Top