dynamic order by sql postgresql

Question

How can i achieve dynamic order by column and sort direction in a postgresql function.

Here is what i have so far:

CREATE OR REPLACE FUNCTION get_urls_by_crawl_id(
    p_account_id character varying(64),
    p_crawl_id character varying(64),
    p_sort_column character varying(30),
    p_sort_direction character varying(30),
    p_page integer,
    p_total integer
)
RETURNS TABLE(id character varying(64), source_url text, http_status_code integer, ref_cnt integer) AS $BODY$
BEGIN
    RETURN QUERY SELECT u.id, u.source_url, u.http_status_code, u.ref_cnt 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 = p_account_id AND u.crawl_id = p_crawl_id AND u.secured = 0 
    ORDER BY p_sort_column (CASE WHEN p_sort_direction = 'ASC' THEN ASC ELSE DESC END) LIMIT p_total OFFSET (p_page * p_total);
END;
$BODY$ LANGUAGE plpgsql;

the psql client returns this error:

ERROR:  syntax error at or near "ASC"
LINE 16: ...t_column (CASE WHEN p_sort_direction = 'ASC' THEN ASC ELSE D...

I have tried multiple forms of the CASE statement but none seems to work.

Gordon Linoff · Accepted Answer · 2018-02-18 19:46:00Z

13

Use two different order by keys:

ORDER BY (case when p_sort_direction = 'ASC' then p_sort_column end)
 asc,
            p_sort_column desc
LIMIT p_total OFFSET (p_page * p_total);

Note that you have another problem. p_sort_column is a string. You would need to use dynamic SQL to insert it into the code.

Alternatively, you can use a series of cases:

order by (case when p_sort_column = 'column1' and p_sort_direction = 'ASC' then column1 end) asc,
         (case when p_sort_column = 'column1' and p_sort_direction = 'DESC' then column1 end) desc,
         (case when p_sort_column = 'column2' and p_sort_direction = 'ASC' then column2 end) asc,
         (case when p_sort_column = 'column2' and p_sort_direction = 'DESC' then column2 end) desc,
         . . .

edited Feb 18, 2018 at 19:46

answered Feb 18, 2018 at 19:43

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

dynamic order by sql postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related