-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathfnproductlistselect.sql
140 lines (112 loc) · 5.57 KB
/
fnproductlistselect.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
-- FUNCTION: public.fnproductlistselect(text, text, text)
-- DROP FUNCTION public.fnproductlistselect(text, text, text);
--select * from fnproductlistselect (_colour:='(1,2)')
--select * from fnproductlistselect (_size:='(1)',_colour:='(1,2)',_prodcategid := 4)
--select * from fnproductlistselect (_prodcategid := 4)
--select * from fnproductlistselect ()
--select * from fnproductlistselect (_subcategid := '(2,3)')
CREATE OR REPLACE FUNCTION public.fnproductlistselect(
_colour text DEFAULT NULL::text,
_size text DEFAULT NULL::text,
_price text DEFAULT NULL::text
, _prodcategid INTEGER DEFAULT NULL
, _subcategid text default null
, _prodname text default null)
RETURNS TABLE(prodid integer, prodcategory character varying, prodsubcategory character varying, prodname character varying, proddesc text
, inrprice numeric, usdprice numeric, colour character varying, size text, qty integer
, prodimgpath TEXT)
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
DECLARE
_sql TEXT := 'SELECT b.prod_id, c.prod_category,a.prod_subcateg_name, b.prod_name,b.prod_desc, pd.prod_inr_price,
pd.prod_usd_price, d.colour_value,string_agg(e.size_value::varchar,'','' order by pd.prod_size) size,pd.prod_qty,f.prod_img_path FROM product_sub_category a
INNER JOIN product b ON a.prod_subcateg_id = b.prod_subcateg_id
INNER JOIN product_category c ON a.prod_category_id = c.prod_category_id
INNER JOIN product_details pd ON b.prod_id = pd.prod_id
LEFT JOIN ref_colour d ON d.colour_id = pd.prod_colour
LEFT JOIN ref_size e ON e.size_id = pd.prod_size
LEFT JOIN product_image f ON b.prod_id = f.prod_id ';
-- WHERE
-- f.prod_img_path = (select prod_img_path from product_image where prod_id= b.prod_id LIMIT 1)';
--WHERE 1=1 ';
_where TEXT;
BEGIN
_where = CONCAT_WS(' AND '
-- , CASE WHEN _colour IS NOT NULL THEN '('||_colour||')' END
, 'f.prod_img_path = (select prod_img_path from product_image where prod_id= b.prod_id LIMIT 1)'
-- , 'pd.pd_id = (select pd_id from product_details where prod_id = b.prod_id LIMIT 1)'
, CASE WHEN _colour IS NOT NULL THEN 'd.colour_id in ' ||_colour||' ' END
, CASE WHEN _size IS NOT NULL THEN
'e.size_id in ' || _size||' '
ELSE
'pd.pd_id = (select pd_id from product_details where prod_id = b.prod_id LIMIT 1)'
END
, CASE WHEN _prodcategid IS NOT NULL THEN 'c.prod_category_id ='|| _prodcategid END
, CASE WHEN _subcategid IS NOT NULL THEN 'a.prod_subcateg_id in '||_subcategid||' ' END
, CASE WHEN _price IS NOT NULL THEN _price END||' '
, CASE WHEN _prodname IS NOT NULL THEN 'lower(b.prod_name) like lower(''%'||_prodname||'%'') ' END||' ');
IF _where <> '' THEN
_sql :=_sql ||' where '|| _where ||' group by b.prod_id,c.prod_category,a.prod_subcateg_name, b.prod_name,b.prod_desc, pd.prod_inr_price,
pd.prod_usd_price, d.colour_value,pd.prod_qty,f.prod_img_path order by random()';
--_sql :=_sql || _where ||' order by b.prod_id';
ELSE
_sql := _sql ||' group by b.prod_id,c.prod_category,a.prod_subcateg_name, b.prod_name,b.prod_desc, pd.prod_inr_price,
pd.prod_usd_price, d.colour_value,pd.prod_qty,f.prod_img_path order by random()';
END IF;
RETURN QUERY
EXECUTE _sql
USING $1,$2,$3,$4,$5,$6;
END
$BODY$;
/* Commented on 20 Nov 20
CREATE OR REPLACE FUNCTION public.fnproductlistselect(
_colour text DEFAULT NULL::text,
_size text DEFAULT NULL::text,
_price text DEFAULT NULL::text
, _prodcategid INTEGER DEFAULT NULL
, _subcategid text default null)
RETURNS TABLE(prodid integer, prodcategory character varying, prodsubcategory character varying, prodname character varying, proddesc text
, inrprice numeric, usdprice numeric, colour character varying, size character varying, qty integer
, prodimgpath TEXT)
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
DECLARE
_sql TEXT := 'SELECT b.prod_id, c.prod_category,a.prod_subcateg_name, b.prod_name,b.prod_desc, pd.prod_inr_price,
pd.prod_usd_price, d.colour_value, e.size_value,pd.prod_qty,f.prod_img_path FROM product_sub_category a
INNER JOIN product b ON a.prod_subcateg_id = b.prod_subcateg_id
INNER JOIN product_category c ON a.prod_category_id = c.prod_category_id
INNER JOIN product_details pd ON b.prod_id = pd.prod_id
LEFT JOIN ref_colour d ON d.colour_id = pd.prod_colour
LEFT JOIN ref_size e ON e.size_id = pd.prod_size
LEFT JOIN product_image f ON b.prod_id = f.prod_id ';
-- WHERE
-- f.prod_img_path = (select prod_img_path from product_image where prod_id= b.prod_id LIMIT 1)';
--WHERE 1=1 ';
_where TEXT;
BEGIN
_where = CONCAT_WS(' AND '
-- , CASE WHEN _colour IS NOT NULL THEN '('||_colour||')' END
, 'f.prod_img_path = (select prod_img_path from product_image where prod_id= b.prod_id LIMIT 1)'
, CASE WHEN _colour IS NOT NULL THEN 'd.colour_id in ' ||_colour||' ' END
, CASE WHEN _size IS NOT NULL THEN 'e.size_id in ' || _size||' ' END
, CASE WHEN _prodcategid IS NOT NULL THEN 'c.prod_category_id ='|| _prodcategid END
, CASE WHEN _subcategid IS NOT NULL THEN 'a.prod_subcateg_id in '||_subcategid||' ' END
, CASE WHEN _price IS NOT NULL THEN _price END||' ');
IF _where <> '' THEN
_sql :=_sql ||' where '|| _where ||' order by b.prod_id';
--_sql :=_sql || _where ||' order by b.prod_id';
ELSE
_sql := _sql ||' order by b.prod_id';
END IF;
RETURN QUERY
EXECUTE _sql
USING $1,$2,$3,$4;
END
$BODY$;
*/