database - 通过不在 postgresql 中工作来参数化 Order

Question

我是 postgresql 的新手。我正在尝试编写一个用于搜索的 postgresql 函数，它将仅返回匹配的 ID，并将订单 ID 作为参数中提供的列名返回。

我尝试用不同的方式解决问题。

我读过，在条件情况下，如果结果集中没有排序列，则不要排序数据。但是我编写的下面的函数返回 ID 以 ASC 排序，如果 DB 中提供的列类型是 text/character varying/date，我测试过。对于 integer/float 类型的列，根本不对 ID 进行排序。现在，我需要对整数/浮点型列进行排序，还需要根据需要对 ASC/DESC 进行排序。

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$

我也试过这种方式下单太简单了:
ORDERY BY _sort_field
和
ORDER BY quote_ident(_sort_field);
如果类型为 text/date/integer/float，它不会对任何列进行 asc/desc 排序!

我使用的是 postgresql-9.5.1-1-windows-x64 版本

Answer 1

asc 和 desc 是关键字，不能是排序表达式的一部分。这个数组技巧可以做到:

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