1

EDIT: I found a solution which I provided at the bottom.

I have the following postgres sql function:

CREATE OR REPLACE FUNCTION public.get_products(_sort_by TEXT DEFAULT 'DEFAULT'::TEXT)
 RETURNS SETOF products
 LANGUAGE sql
 STABLE
AS $function$
SELECT
  p.*
from
  products p
 ORDER BY p.is_mattress DESC, p.sale, p.regular
  
$function$;

This works. What I am trying to do is control the sort by logic depending on what _sort_by is when it comes in.

IE: _sort_by = 'DEFAULT' I want the sort by to be p.is_mattress DESC, p.sale, p.regular _sort_by = 'PRICE_LOW_TO_HIGH' would be a sort by of COALESCE(p.sale, p.regular) ASC

I am trying to get CASE to work here since it seems logically equivalent to what I want to do. this is my best attempt but this isnt resulting in an error so obviously isnt correct.

The error: ERROR: syntax error at or near "DESC" LINE 68: WHEN 'DEFAULT' THEN p.is_mattress DESC, p.sale, p.re...

CREATE OR REPLACE FUNCTION public.get_products(_sort_by TEXT DEFAULT 'DEFAULT'::TEXT)
 RETURNS SETOF products
 LANGUAGE sql
 STABLE
AS $function$
SELECT
  p.*
from
  products p
 ORDER BY 
    CASE _sort_by
        WHEN 'DEFAULT' THEN p.is_mattress DESC, p.sale, p.regular
        WHEN 'PRICE_LOW_TO_HIGH' THEN COALESCE(p.sale, p.regular) ASC
        WHEN 'PRICE_HIGH_TO_LOW' THEN COALESCE(p.sale, p.regular) DESC
    END
$function$;

The attached question was not what I was looking for. However, I was able to solve with the following query:

CREATE OR REPLACE FUNCTION public.get_products(_sort_by TEXT DEFAULT 'DEFAULT'::TEXT)
 RETURNS SETOF products
 LANGUAGE sql
 STABLE
AS $function$
SELECT
  p.*
from
  products p
  ORDER BY 
   CASE WHEN _sort_by = 'PRICE_LOW_TO_HIGH' THEN COALESCE(p.sale, p.regular) END ASC,
   CASE WHEN _sort_by = 'PRICE_HIGH_TO_LOW' THEN COALESCE(p.sale, p.regular) END DESC,
   CASE WHEN _sort_by = 'BRAND_A_TO_Z' THEN b.name END ASC,
   CASE WHEN _sort_by = 'BRAND_Z_TO_A' THEN b.name END DESC,
   CASE WHEN _sort_by = 'DEFAULT' THEN p.is_mattress END DESC,
   CASE WHEN _sort_by = 'DEFAULT' THEN p.sale END,
   CASE WHEN _sort_by = 'DEFAULT' THEN p.regular END
$function$;
Daniel
  • 31
  • 4
  • Use Dynamic SQL to assemble the tail of the query as needed. – The Impaler Feb 01 '23 at 22:37
  • @TheImpaler I can see how there is a possible solution with the question you linked. However, there is a significantly better way to solve this problem (which I provided). – Daniel Feb 01 '23 at 23:14
  • 1
    Yes, this solution will work quite well too. It may have two downsides, though: 1) It may not scale up well complex combinations of sorting orders, and 2) the optimizer won't be able to make good use of indexes for data retrieval, so the query may become slow (for big sets of data). – The Impaler Feb 02 '23 at 14:03
  • @TheImpaler Yes good point with the complexity of the order by. Would the optimizer have that same downfall using dynamic sql? – Daniel Feb 02 '23 at 15:48
  • If the `ORDER BY` clause does not have complex expressions in the list and has simple columns instead, the optimizer has far better chances of using indexes and use their natural ordering capabilities. But in the end, you should get the execution plans of the available options and compare them to find out for sure. – The Impaler Feb 02 '23 at 15:51

0 Answers0