在 postgresql 中使用动态排序字段/顺序有哪些选项? [英] What are my options for using dynamic sort field/order in postgresql?

问题描述

我发现我可以使用动态sql来插入排序字段和排序方向的参数.我还发现将这些参数插入动态sql可以使用USING param1, param2".

I have found that i can use dynamic sql to plug in parameters for the sort field and the sort direction. I have also found that to plug those parameters into the dynamic sql one can use "USING param1, param2".

这是我正在尝试使用的 plpgsql 函数.不幸的是，postgresql 在应该插入排序方向参数的位置抛出错误.

Here is my plpgsql function i am trying to get working. Unfortunately postgresql is throwing an error at the location where the sort direction param is supposed to be interpolated.

CREATE OR REPLACE FUNCTION manager_performance_get_urls_by_crawl_id(
    p_account_id character varying(64),
    p_crawl_id character varying(64),
    p_field character varying(20),
    p_direction character varying(20),
    p_page integer,
    p_total integer
)
RETURNS TABLE(id character varying(64), source_url text, http_status_code integer, ref_cnt integer, utype rec_url_type, total_size numeric, total_time numeric, secured integer, mime_type character varying(100)) AS $BODY$
DECLARE
    SQL text := '
        SELECT u.id, u.source_url, u.http_status_code, u.ref_cnt, u.utype, u.total_size, u.total_time, u.secured, u.mime_type FROM url AS u
        JOIN crawl AS c ON(u.crawl_id = c.id)
        JOIN site AS s ON(c.site_id = s.id)
        JOIN person AS p ON(s.person_id = p.id)
        WHERE p.account_id = $1 AND u.crawl_id = $2
        GROUP BY u.id ORDER BY $3 $4 LIMIT $6 OFFSET ($5 * $6);
    ';
BEGIN
    RETURN QUERY EXECUTE SQL USING p_account_id, p_crawl_id, p_field, p_direction, p_page, p_total;

END;
$BODY$ LANGUAGE plpgsql;

返回的错误:

<b>Warning</b>:  pg_query_params(): Query failed: ERROR:  syntax error at or near &quot;$4&quot;
LINE 7:         GROUP BY u.id ORDER BY $3 $4 LIMIT $6 OFFSET ($5 * $...
                                          ^
QUERY:  
        SELECT u.id, u.source_url, u.http_status_code, u.ref_cnt, u.utype, u.total_size, u.total_time, u.secured, u.mime_type FROM url AS u
        JOIN crawl AS c ON(u.crawl_id = c.id)
        JOIN site AS s ON(c.site_id = s.id)
        JOIN person AS p ON(s.person_id = p.id)
        WHERE p.account_id = $1 AND u.crawl_id = $2
        GROUP BY u.id ORDER BY $3 $4 LIMIT $6 OFFSET ($5 * $6);

CONTEXT:  PL/pgSQL function manager_performance_get_urls_by_crawl_id(character varying,character varying,character varying,character varying,integer,integer) line 12 at RETURN QUERY

推荐答案

您不能在这样的查询中使用查询参数来指定 ASC 或 DESC:

You cannot use a query parameter to specify ASC or DESC in a query like this:

ORDER BY $3 $4

您应该将方向作为 boolean 传递并像这样构造查询:

You should pass the direction as a boolean and construct the query like that:

sql := 'SELECT ... ORDER BY $3 '
       || CASE WHEN p_ascending THEN 'ASC' ELSE 'DESC' END
       || 'LIMIT ...';

这篇关于在 postgresql 中使用动态排序字段/顺序有哪些选项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！