What are my options for using dynamic sort field/order in postgresql?

Question

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".

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;

The error that is returned:

<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

Laurenz Albe · Accepted Answer · 2018-02-24 03:22:54Z

2

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

ORDER BY $3 $4

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 ...';

edited Feb 24, 2018 at 3:22

answered Feb 23, 2018 at 19:39

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Over a year ago

thanks for the reply, i don't think that is going to work, my understanding is that after the column name the sort direction such as ASC or DESC is placed once space after the sort column without a comma, the comma is used when specifying more than one column to sort by

Laurenz Albe Over a year ago

I had misunderstood the question. See my modified answer.

Dan Over a year ago

Thanks, i ended up using the postgresql format function and passed in parameters that way.

Collectives™ on Stack Overflow

What are my options for using dynamic sort field/order in postgresql?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related