Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


create table options ( option_id       bigint generated always as identity 
                       , barcode       text 
                       , constraint options_pk 
                                    primary key (option_id)
                       , constraint options_bk
                                    unique (barcode)                                    
                       );  
insert into  options( barcode)  
  values ('AfsbBC95'), ('DEsfs97'), ('LO7954'), ('OO74'), ('4s9OO65+4'), (''), (null); 
7 rows affected

select * from options; 


option_id
barcode


1
AfsbBC95

2
DEsfs97

3
LO7954

4
OO74

5
4s9OO65+4

6


7
null


Select * from options where barcode like '%%' or barcode is null; 


option_id
barcode


1
AfsbBC95

2
DEsfs97

3
LO7954

4
OO74

5
4s9OO65+4

6


7
null


create table batch ( batch_id           bigint generated always as identity 
                   , option_id          bigint 
                   , product_id         bigint
                   , import_quantity    bigint  
                   , sold_quantity      bigint 
                   , remaining_quantity bigint 
                   , constraint batch_pk 
                                primary key (batch_id) 
                   , constraint batch2options_fk 
                                foreign key (option_id) 
                                references options(option_id)
                   ) ; 
insert into batch(option_id, product_id, import_quantity, sold_quantity, remaining_quantity)
  select floor(random()*7+1)::integer, 220, iq, sq, iq-sq 
    from ( select n, floor(random()*100+1)::bigint iq,  floor(random()*100+1)::bigint sq
             from generate_series(1,43) gs(n)
         ) qty;

select * from batch;
43 rows affected



batch_id
option_id
product_id
import_quantity
sold_quantity
remaining_quantity


1
2
220
29
74
-45

2
3
220
32
67
-35

3
4
220
15
69
-54

4
4
220
53
49
4

5
2
220
95
100
-5

6
1
220
77
20
57

7
1
220
86
20
66

8
3
220
73
98
-25

9
2
220
7
68
-61

10
5
220
70
34
36

11
3
220
75
66
9

12
6
220
78
36
42

13
1
220
42
64
-22

14
6
220
71
44
27

15
4
220
76
22
54

16
6
220
96
19
77

17
2
220
41
92
-51

18
7
220
98
95
3

19
3
220
42
100
-58

20
4
220
93
93
0

21
2
220
57
99
-42

22
7
220
36
88
-52

23
5
220
41
55
-14

24
6
220
29
32
-3

25
7
220
60
90
-30

26
3
220
95
39
56

27
7
220
78
65
13

28
6
220
88
92
-4

29
3
220
46
79
-33

30
7
220
87
78
9

31
6
220
86
3
83

32
2
220
35
94
-59

33
1
220
6
34
-28

34
1
220
94
16
78

35
4
220
84
62
22

36
3
220
88
81
7

37
1
220
87
48
39

38
2
220
32
59
-27

39
5
220
74
48
26

40
4
220
43
30
13

41
2
220
96
99
-3

42
5
220
64
78
-14

43
3
220
99
10
89


create or replace function getf(arg int)
    returns table (option_id      bigint 
                  ,importQuantity bigint 
                  ,sold           bigint 
                  ,remain         bigint
                  )
   language plpgsql
 as $$
 declare

    k_having_arg constant text[] = array['> 0','<= 0', 'is not null']; -- Replacement values for RVALUE below
    k_base_query constant text =
         'select b.option_id'
              ', SUM(b.import_quantity)::bigint '
              ', SUM(b.sold_quantity)::bigint  '
              ', SUM(b.remaining_quantity)::bigint ' 
          ' from batch b' 
         ' where b.product_id = 220'
         ' group by b.option_id ' 
        ' having sum(b.remaining_quantity) %s; ';  -- expressions RVALUE to be replaces 
     
   l_exec_query text; 
   
begin 
	if arg not between 1 and 3 then
	   raise exception 'Invalid Arg value (%) out of range, Must be 1, 2, or 3.', arg;  
	end if; 

    l_exec_query = format (k_base_query,k_having_arg[arg]);
    raise notice E'Running query\n%',l_exec_query;
    return query execute l_exec_query;
end;
$$;
select * from getf(1); 


option_id
importquantity
sold
remain


1
392
202
190

3
550
540
10

4
364
325
39

5
249
215
34

6
448
226
222


select * from getf(2); 


option_id
importquantity
sold
remain


2
392
685
-293

7
359
416
-57


select * from getf(3); 


option_id
importquantity
sold
remain


1
392
202
190

2
392
685
-293

3
550
540
10

4
364
325
39

5
249
215
34

6
448
226
222

7
359
416
-57


select * from getf(4); 
ERROR:  Invalid Arg value (4) out of range, Must be 1, 2, or 3.
CONTEXT:  PL/pgSQL function getf(integer) line 19 at RAISE

batch_id	option_id	product_id	import_quantity	sold_quantity	remaining_quantity
1	2	220	29	74	-45
2	3	220	32	67	-35
3	4	220	15	69	-54
4	4	220	53	49	4
5	2	220	95	100	-5
6	1	220	77	20	57
7	1	220	86	20	66
8	3	220	73	98	-25
9	2	220	7	68	-61
10	5	220	70	34	36
11	3	220	75	66	9
12	6	220	78	36	42
13	1	220	42	64	-22
14	6	220	71	44	27
15	4	220	76	22	54
16	6	220	96	19	77
17	2	220	41	92	-51
18	7	220	98	95	3
19	3	220	42	100	-58
20	4	220	93	93	0
21	2	220	57	99	-42
22	7	220	36	88	-52
23	5	220	41	55	-14
24	6	220	29	32	-3
25	7	220	60	90	-30
26	3	220	95	39	56
27	7	220	78	65	13
28	6	220	88	92	-4
29	3	220	46	79	-33
30	7	220	87	78	9
31	6	220	86	3	83
32	2	220	35	94	-59
33	1	220	6	34	-28
34	1	220	94	16	78
35	4	220	84	62	22
36	3	220	88	81	7
37	1	220	87	48	39
38	2	220	32	59	-27
39	5	220	74	48	26
40	4	220	43	30	13
41	2	220	96	99	-3
42	5	220	64	78	-14
43	3	220	99	10	89

option_id	importquantity	sold	remain
1	392	202	190
2	392	685	-293
3	550	540	10
4	364	325	39
5	249	215	34
6	448	226	222
7	359	416	-57

batch_id	option_id	product_id	import_quantity	sold_quantity	remaining_quantity
1	2	220	29	74	-45
2	3	220	32	67	-35
3	4	220	15	69	-54
4	4	220	53	49	4
5	2	220	95	100	-5
6	1	220	77	20	57
7	1	220	86	20	66
8	3	220	73	98	-25
9	2	220	7	68	-61
10	5	220	70	34	36
11	3	220	75	66	9
12	6	220	78	36	42
13	1	220	42	64	-22
14	6	220	71	44	27
15	4	220	76	22	54
16	6	220	96	19	77
17	2	220	41	92	-51
18	7	220	98	95	3
19	3	220	42	100	-58
20	4	220	93	93	0
21	2	220	57	99	-42
22	7	220	36	88	-52
23	5	220	41	55	-14
24	6	220	29	32	-3
25	7	220	60	90	-30
26	3	220	95	39	56
27	7	220	78	65	13
28	6	220	88	92	-4
29	3	220	46	79	-33
30	7	220	87	78	9
31	6	220	86	3	83
32	2	220	35	94	-59
33	1	220	6	34	-28
34	1	220	94	16	78
35	4	220	84	62	22
36	3	220	88	81	7
37	1	220	87	48	39
38	2	220	32	59	-27
39	5	220	74	48	26
40	4	220	43	30	13
41	2	220	96	99	-3
42	5	220	64	78	-14
43	3	220	99	10	89

batch_id	option_id	product_id	import_quantity	sold_quantity	remaining_quantity
1	2	220	29	74	-45
2	3	220	32	67	-35
3	4	220	15	69	-54
4	4	220	53	49	4
5	2	220	95	100	-5
6	1	220	77	20	57
7	1	220	86	20	66
8	3	220	73	98	-25
9	2	220	7	68	-61
10	5	220	70	34	36
11	3	220	75	66	9
12	6	220	78	36	42
13	1	220	42	64	-22
14	6	220	71	44	27
15	4	220	76	22	54
16	6	220	96	19	77
17	2	220	41	92	-51
18	7	220	98	95	3
19	3	220	42	100	-58
20	4	220	93	93	0
21	2	220	57	99	-42
22	7	220	36	88	-52
23	5	220	41	55	-14
24	6	220	29	32	-3
25	7	220	60	90	-30
26	3	220	95	39	56
27	7	220	78	65	13
28	6	220	88	92	-4
29	3	220	46	79	-33
30	7	220	87	78	9
31	6	220	86	3	83
32	2	220	35	94	-59
33	1	220	6	34	-28
34	1	220	94	16	78
35	4	220	84	62	22
36	3	220	88	81	7
37	1	220	87	48	39
38	2	220	32	59	-27
39	5	220	74	48	26
40	4	220	43	30	13
41	2	220	96	99	-3
42	5	220	64	78	-14
43	3	220	99	10	89