· 7 years ago · Feb 06, 2019, 01:34 PM
1CREATE OR REPLACE FUNCTION
2for_input.codes_handling(table_name text, uid text)
3 returns text
4 language plpgsql
5 returns null on null input
6 security definer
7 volatile
8 as
9$$
10DECLARE
11 r record;
12 cnt int;
13BEGIN
14
15 execute('create temp table if not exists tmp2 ( like for_input.reserve_' || table_name || ' including all ) on commit drop ;');
16 insert into tmp2 (select
17 uid_main,
18 nl_mob,
19 nu_tob,
20 code_tob,
21 unit_tob,
22 quantity_tob,
23 doc_type_mob
24 unit_tob
25 from buh.mob_n_tob
26 where uid_main=$2 ) ;
27
28 for r in
29 select code from tmp2
30 loop
31 with pre_select as (
32 select code
33 from buh.nom
34 where article = (select article from buh.nom where code=r.code) and code != r.code
35 )
36 select into cnt count(*) from pre_select ;
37 if cnt = 1 then
38 with pre_select as (
39 select code
40 from buh.nom
41 where article = (select article from buh.nom where code=r.code) and code != r.code
42 )
43 update tmp2 set code_2 = (select * from pre_select) where code=r.code;
44 end if ;
45 if cnt = 2 then
46 with pre_select as (
47 select code
48 from buh.nom
49 where article = (select article from buh.nom where code=r.code) and code != r.code
50 )
51 update tmp2 set code_3 = (select * from pre_select order by code desc limit 1) where code=r.code;
52
53 with pre_select as (
54 select code
55 from buh.nom
56 where article = (select article from buh.nom where code=r.code) and code != r.code
57 )
58 update tmp2 set code_2 = (select * from pre_select order by code asc limit 1) where code=r.code;
59 end if ;
60 end loop ;
61
62 execute('insert into for_input.reserve_' || table_name || ' select uid,
63 nl,
64 nu,
65 code,
66 unit,
67 quantity,
68 doc_type,
69 i,
70 code_2,
71 code_3,
72 article,
73 code_upr_nom,
74 dead_end
75 from
76 tmp2 ;');
77 return null ;
78
79END ;
80$$ ;