我在postgresql中使用动态排序字段/顺序的选项有哪些?

我发现可以使用动态sql插入排序字段和排序方向的参数。我还发现,要将这些参数插入到动态sql中,可以使用“USING param1,param2”。

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

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