CODEWITHSUNDEEP
databasepostgresqlprepared-statementsql-order-by
X-Coder
X-Coder

Reputation: 2736

Parameterised Order by not working in postgresql

I'm new in postgresql. I'm trying to write a postgresql function for searching that will return matching IDs only and order IDs as column name supplied in the parameter.

I tried to solve the problem different ways.

I've read that order by in the conditional case don't order data if the ordering column don't have in the result set. But the function below I've written, returns IDs in ASC ordering iff the supplied column type in DB is text/character varying/date, I tested. For integer/float type column not sorting IDs at all. Now, I need to sort for integer/float type column and also ASC/DESC as needed.

CREATE OR REPLACE FUNCTION public.search_products(_name text DEFAULT NULL::text, _category_id integer DEFAULT NULL::integer, _min_mrp double precision DEFAULT NULL::double precision, _max_mrp double precision DEFAULT NULL::double precision, _sort_field text DEFAULT NULL::text)
 RETURNS SETOF bigint
 LANGUAGE sql
AS $function$

    select product.id 
        from product
        where
            case
                when _category_id is null then (_name is null or lower(product.name) like '%'|| lower(_name) ||'%' and (_min_mrp is null or _max_mrp is null Or (product.market_retail_price BETWEEN _min_mrp and _max_mrp)))
                when _category_id is not null then (_name is null or lower(product.name) like '%'|| lower(_name) ||'%' and (_min_mrp is null or _max_mrp is null Or (product.market_retail_price BETWEEN _min_mrp and _max_mrp))) /*will be updated after category id deciding */
            end

            ORDER BY
                CASE 
                    WHEN(_sort_field similar to 'market_retail_price asc') THEN product.market_retail_price || ' ASC' /* it is float type column, and not working for asc/desc anything */
                    WHEN(_sort_field similar to 'approved_by asc') THEN product.approved_by || ' ASC' /* it is integer type column, and not working for asc/desc anything */
                    WHEN(_sort_field similar to 'approved_on asc') THEN product.approved_on || ' ASC' /* date type column, it works for asc order only. Even if text is 'approved_on desc' in other case, it match the case, but returns data in asc order only. THAT'S TOTALLY WIERED. Never sort desc. */
                    WHEN(_sort_field similar to 'supplier_id asc') THEN product.supplier_id || ' ASC'
                    WHEN(_sort_field similar to 'product_status asc') THEN product.product_status || ' ASC' /* text type, and working for asc only */
                    WHEN(_sort_field similar to 'name desc') THEN (product.name || ' DESC') /* not working for desc order, but returns data in asc sort */
                    ELSE product.id || ' ASC'
                END
$function$

I also trying this way to order too simply:
ORDERY BY _sort_field
and
ORDER BY quote_ident(_sort_field);
it does not sort asc/desc for any column if the type text/date/integer/float whatever it is!

I'm using postgresql-9.5.1-1-windows-x64 version

Upvotes: 0

Views: 2283

Answers (2)

X-Coder
X-Coder

Reputation: 2736

Finally, I solved my problem using dynamic query (dynamic query is painful though). Without dynamic query I could not fix orderby (ASC and DESC) problem as supplied parameter. Here is the modified functions. Thanks @ClodoaldoNeto. and the other man who actually instructed me to use dynamic query.

Here is the function, hope this will help others too:

CREATE OR REPLACE FUNCTION public.search_products(_name text DEFAULT NULL::text, _category_id integer DEFAULT NULL::integer, _min_mrp double precision DEFAULT NULL::double precision, _max_mrp double precision DEFAULT NULL::double precision, _sort_column_name text DEFAULT NULL::text, _sorting_type text DEFAULT 'asc'::text)
 RETURNS SETOF bigint
 LANGUAGE plpgsql
AS $function$
BEGIN

    if($1 is not null) then
        $1 := '%'||$1||'%';
    end if;

    RETURN QUERY EXECUTE
    'SELECT product.id
    FROM product
    WHERE (($1 is null OR product.name ~~* $1) AND ($3 is null OR $4 is null OR (product.market_retail_price BETWEEN $3 and $4)) AND ( $2 IS NULL OR (product.id IN (SELECT product_category_map.product_id FROM product_category_map where product_category_map.category_id = $2))))
    ORDER BY ' 
        || quote_ident($5) 
        || ' '
        || $6
    USING _name, _category_id, _min_mrp, _max_mrp, _sort_column_name, _sorting_type; 

END;
$function$

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

ascand desc are keywords and must not be part of the ordering expression. This array trickery will do it:

order by
    (array [
        to_char(p.market_retail_price, '00000000009'),
        p.approved_by,
        to_char(p.approved_on, 'YYYY-MM-DD'),
        to_char(p.supplier_id, '00000000009'),
        p.product_status
    ])
    [array_position (
        array [
            'market_retail_price asc',
            'approved_by asc',
            'approved_on asc',
            'supplier_id asc',
            'product_status asc'
        ], _sort_field
    )] asc,
    (array [p.name])[array_position (array ['name desc'], _sort_field)] desc

Upvotes: 2

Related Questions