CODEWITHSUNDEEP
sqlpostgresqlstored-proceduresplpgsql
Dan
Dan

Reputation: 2509

dynamic order by sql postgresql

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.

Upvotes: 3

Views: 7507

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

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

Upvotes: 13

Related Questions