bgWcVBDc

1create or replace function podwyzka() 
2	returns void as
3$$
4begin
5
6  CREATE TEMP TABLE IF NOT EXISTS kosztyP AS
7        select idpudelka,sum(sztuk*koszt) as kosztP
8        from zawartosc natural join czekoladki group by idpudelka;
9
10  update czekoladki set koszt = koszt +
11   case 
12   when koszt<0.2 then 0.03 
13  	when koszt between 0.21 and 0.29 then 0.04 
14  	else 0.05
15  	 end ;
16
17
18  with current_cost as (select idpudelka,sum(sztuk*koszt) as kosztC
19        from zawartosc
20        natural join czekoladki
21        group by idpudelka)
22
23  update pudelka k set cena = cena+kosztC-kosztP
24        from kosztyP p
25        natural join current_cost
26        where p.idpudelka=k.idpudelka;
27
28  DROP TABLE kosztyP;
29end;
30$$
31language plpgsql;
32
33---------------------------------------------------------------
34
35create or replace function podwyzka() 
36	returns void as
37$$
38declare 
39roznica numeric(7,2);
40begin
41
42  CREATE TEMP TABLE IF NOT EXISTS kosztyP AS
43        select idpudelka,sum(sztuk*koszt) as kosztP
44        from zawartosc natural join czekoladki group by idpudelka;
45
46  update czekoladki set koszt = koszt +
47   case 
48   when koszt<0.2 then 0.03 
49  	when koszt between 0.21 and 0.29 then 0.04 
50  	else 0.05
51  	 end ;
52
53
54  CREATE TEMP TABLE IF NOT EXISTS  current_cost as select idpudelka,sum(sztuk*koszt) as kosztC
55        from zawartosc
56        natural join czekoladki
57        group by idpudelka;
58
59  select kosztC-kosztP into roznica from kosztyP natural join kosztC ;     
60
61update pudelka  set cena = cena+roznica;
62
63  DROP TABLE kosztyP;
64end;
65$$
66language plpgsql;