Hello everyone, there is a postgresql (9.4.10) - a function for reading products and data from related tables. Is it possible to set the sorting order with the parameter (p_sort) order-by-limit-in-table-function

And I do:

CREATE OR REPLACE FUNCTION public.pd_get_product_prices(p_limit integer, p_offset integer, p_sort character varying DEFAULT 'rating', p_sort_direction character varying DEFAULT 'asc', p_title character varying DEFAULT NULL::character varying, p_status type_productstatus DEFAULT NULL::type_productstatus, p_in_stock 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 integer, title character varying, status type_productstatus, sku character varying, user_id smallint, regular_price type_money, sale_price type_money, in_stock boolean, short_description character varying, virtual boolean, rating_count integer, rating_summary integer, rating integer, created_at timestamp without time zone, main_image character varying, product_categories character varying[], product_categories_id smallint[], product_tags character varying[], product_tags_id smallint[], product_attributes jsonb) LANGUAGE sql AS $function$ select * from ( SELECT p.id, p.title, p.status, p.sku, p.user_id, p.regular_price, p.sale_price, p.in_stock, p.short_description, p.virtual, p.rating_count, p.rating_summary, CAST( floor( p.rating_summary / NULLIF(p.rating_count,0) ) AS INTEGER ) as rating, 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.id) 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_id, ( 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 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 ( p.status= p_status OR p_status IS NULL ) AND ( p.in_stock = p_in_stock OR p_in_stock IS NULL ) AND ( p.sku like p_sku OR p_sku IS NULL ) AND ( p.title like p_title OR p_title IS NULL ) AND ( ( 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 ( pc.category_id = ANY (p_category_list) OR p_category_list IS NULL ) GROUP BY p.id ) as rows WHERE( ( rows.rating >= p_rating_from AND rows.rating <= p_rating_till ) OR ( p_rating_from IS NULL OR p_rating_till IS null ) ) ORDER BY -- Simplified to NULL if not sorting in ascending order. CASE WHEN p_sort_direction = 'asc' THEN CASE p_sort -- Check for each possible value of p_sort. WHEN 'rating' THEN rating WHEN 'sale_price' THEN sale_price WHEN 'title' THEN title --- etc. ELSE NULL END ELSE NULL END ASC, -- Same as before, but for p_sort_direction = 'desc' CASE WHEN p_sort_direction = 'desc' THEN CASE p_sort WHEN 'rating' THEN rating WHEN 'sale_price' THEN sale_price WHEN 'title' THEN title ELSE NULL END ELSE NULL END DESC LIMIT p_limit OFFSET p_offset; $function$

And the error:

 ERROR: CASE types numeric and character varying cannot be matched LINE 42: WHEN 'title' THEN title ^

What is the right way then ?

Thank!

  • it looks like your p_sort number is leaving - etki

1 answer 1

You did not bring the DDL tables, but apparently the fields rating and sale_price type numeric or can be brought to it without sale_price , but the title most likely varchar . So it is impossible, CASE should generate the value of only one data type :

Must be convertible to a single output type

You can use an explicit cast to the text, but this is very unlikely what you need. Sorting numbers as strings works quite logically for a machine, but not very logical for a person.

You can use the second case separately for text data.

You can use pl/pgsql to build the query as a string, then return query execute through the return query execute .