Hello,
I make function for big filter form with many input parameters, which can be empty or filled or with several parametrs(several categories/or nonr could be in p_category_list
parameter)
Questions are which is the better way in production purpose to write sql for this?
Looks like line :
Code:
           ( CASE when p_status IS NOT NULL THEN p.status = p_status else true END ) AND
is better of line :
Code:
	   ( p.status= p_status OR p_status IS NULL ) AND

and line
Code:
           ( p.sale_price BETWEEN coalesce(p_sale_price_from, f_min_numeric() ) AND coalesce(p_sale_price_till,f_max_numeric() ) )  AND
is better of line

Code:
( ( p.sale_price >= p_sale_price_from AND p.sale_price <= p_sale_price_till ) OR ( p_sale_price_from IS NULL OR p_sale_price_till IS null ) ) AND
where
Code:
CREATE OR REPLACE FUNCTION public.f_max_numeric()
 RETURNS numeric
 LANGUAGE sql
 IMMUTABLE
AS $function$SELECT 99999999999::numeric$function$
CREATE OR REPLACE FUNCTION public.f_min_numeric()
 RETURNS numeric
 LANGUAGE sql
 IMMUTABLE
AS $function$SELECT -1::numeric$function$
And I suppose that when I need to make range filter for date I would have make very similar with smallest and biggest dates.


and this line
Code:
           ( CASE when p_category_list IS NOT NULL THEN pc.category_id = ANY (p_category_list) else true END )
is better of line
Code:
	  ( pc.category_id = ANY (p_category_list) OR p_category_list IS NULL )
And foir rating(it is range 1-5):
Code:
( rows.rating BETWEEN coalesce(p_rating_from, 1 ) AND coalesce(p_rating_from, 5 ) )

I would like to know the better way you use?

The full function code is below, I would be thankfull for you opinion how to make it better for productivity:
Code:
CREATE OR REPLACE FUNCTION public.pd_get_product_prices(p_limit integer, p_offset integer, p_sort character varying DEFAULT 'rating'::character varying, p_sort_direction character varying DEFAULT 'asc'::character varying, p_title character varying DEFAULT NULL::character varying, p_status type_productstatus DEFAULT NULL::type_productstatus, p_in_stock boolean DEFAULT NULL::boolean, p_downloadable boolean DEFAULT NULL::boolean, p_virtual boolean DEFAULT NULL::boolean, p_sku character varying DEFAULT NULL::character varying, p_sale_price_from type_money DEFAULT NULL::numeric, p_sale_price_till type_money DEFAULT NULL::numeric, p_rating_from integer DEFAULT NULL::integer, p_rating_till integer DEFAULT NULL::integer, p_category_list integer[] DEFAULT NULL::integer[])
 RETURNS TABLE(id bigint, title character varying, status type_productstatus, slug character varying, sku character varying, user_id integer, regular_price type_money, sale_price type_money, in_stock boolean, short_description character varying, downloadable boolean, virtual boolean, rating_count integer, rating_summary integer, rating integer, published_at timestamp without time zone, created_at timestamp without time zone, main_image character varying, product_categories character varying[], product_categories_slug character varying[], product_tags character varying[], product_tags_id smallint[], bookmarks_count bigint, product_attributes jsonb)
 LANGUAGE sql
AS $function$

select * from ( SELECT p.id, p.title, p.status, p.slug, p.sku, p.user_id, p.regular_price, p.sale_price, p.in_stock, p.short_description, p.downloadable, p.virtual, p.rating_count, p.rating_summary,

CAST( floor( p.rating_summary / NULLIF(p.rating_count,0)  ) AS INTEGER ) as rating,

p.published_at, p.created_at,

(select pi.image from pd_product_image as pi where pi.product_id = p.id and pi.is_main = true ) as main_image,

( select array_agg(c.name) from pd_category as c, pd_product_category as pd where c.id= pd.category_id and pd.product_id = p.id) as product_categories,

( select array_agg(c.slug) from pd_category as c, pd_product_category as pd where c.id= pd.category_id and pd.product_id = p.id) as product_categories_slug,

( select array_agg(t.name) from pd_tag as t, pd_product_tag as pt where t.id= pt.tag_id and pt.product_id = p.id) as product_tags,

( select array_agg(t.id) from pd_tag as t, pd_product_tag as pt where t.id= pt.tag_id and pt.product_id = p.id) as product_tags_id,

( select count(*) from pd_product_bookmark where pd_product_bookmark.product_id = p.id and pd_product_bookmark.user_id = p.user_id ) as bookmarks_count,

( select pa.attributes_data from pd_product_attribute as pa where pa.product_id = p.id) as product_attributes

FROM pd_product AS p LEFT JOIN  pd_product_category AS pc ON pc.product_id = p.id

      WHERE

           ( CASE when p_status IS NOT NULL THEN p.status = p_status else true END ) AND

           ( CASE when p_in_stock IS NOT NULL THEN p.in_stock = p_in_stock else true END ) AND

           ( CASE when p_downloadable IS NOT NULL THEN p.downloadable = p_downloadable else true END ) AND

           ( CASE when p_virtual IS NOT NULL THEN p.virtual = p_virtual else true END ) AND

           ( CASE when p_sku IS NOT NULL THEN LOWER(p.sku) like LOWER(p_sku) else true END ) AND

           ( CASE when p_title IS NOT NULL THEN LOWER(p.title) like LOWER(p_title) else true END ) AND

           ( p.sale_price BETWEEN coalesce(p_sale_price_from, f_min_numeric() ) AND coalesce(p_sale_price_till,f_max_numeric() )
 )  AND

           ( CASE when p_category_list IS NOT NULL THEN pc.category_id = ANY (p_category_list) else true END )

      GROUP BY p.id

) as rows

      WHERE   ( rows.rating BETWEEN coalesce(p_rating_from, 1 ) AND coalesce(p_rating_from, 5 ) )


      ORDER BY

      CASE WHEN p_sort_direction = 'asc' THEN

          CASE p_sort

              -- sort by numeric fields

              WHEN 'rating' THEN rating

              WHEN 'sale_price' THEN sale_price

              ELSE NULL

          END

      ELSE

          NULL

      END

      ASC,

      CASE WHEN p_sort_direction = 'desc' THEN

          CASE p_sort -- sort by numeric fields

              WHEN 'rating' THEN rating

              WHEN 'sale_price' THEN sale_price

              ELSE NULL

          END

      ELSE

          NULL

      END

      DESC,

      CASE WHEN p_sort_direction = 'asc' THEN

          CASE p_sort   -- sort by string fields

              WHEN 'title' THEN title

              WHEN 'status' THEN CAST(status AS character varying )

              WHEN 'downloadable' THEN CAST(downloadable AS character varying )

              WHEN 'virtual' THEN CAST(virtual AS character varying )

              WHEN 'in_stock' THEN CAST(in_stock AS character varying )

              ELSE NULL

          END

      ELSE

          NULL

      END

      ASC,

      CASE WHEN p_sort_direction = 'desc' THEN

          CASE p_sort  -- sort by string fields

              WHEN 'title' THEN title

              WHEN 'status' THEN CAST(status AS character varying )

              WHEN 'downloadable' THEN CAST(downloadable AS character varying )

              WHEN 'virtual' THEN CAST(virtual AS character varying )

              WHEN 'in_stock' THEN CAST(in_stock AS character varying )

              ELSE NULL

          END

      ELSE

          NULL

      END

      DESC,

      CASE WHEN p_sort_direction = 'asc' THEN

          CASE p_sort

              -- sort by timestamp fields

              WHEN 'published_at' THEN published_at

              WHEN 'created_at' THEN created_at

              ELSE NULL

          END

      ELSE

          NULL

      END

      ASC,

      CASE WHEN p_sort_direction = 'desc' THEN

          CASE p_sort -- sort by timestamp fields

              WHEN 'published_at' THEN published_at

              WHEN 'created_at' THEN created_at

              ELSE NULL

          END

      ELSE

          NULL

      END

      DESC

      LIMIT p_limit  OFFSET p_offset;

$function$
Thanks!