PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to insert sort direction into SQL query dynamically

I have this at the end of my SQL query:

ORDER BY
    CASE
        WHEN :sortField = 'creationDate' THEN update_.creation_date
        ELSE update_.default_time_field
        END,
    CASE WHEN :sortDirection = 'DESC' THEN DESC END;

However, this gives me a compile error (the ‘DESC’ refers to the DESC at the very end. Not the strings before it):

<expression> expected, got 'DESC'

Which is fine, but I’m having a hard time going around it. SQL just won’t let me.

I am using Postgres 11. I am injecting parameters using Java code (Micronaut-Data. No, I cannot use Pageable).

Here is how the above looks when I actually enter the values for the variables:

ORDER BY
    CASE
        WHEN 'creationDate' = 'creationDate' THEN update_.creation_date
        ELSE update_.default_time_field
        END,
    CASE WHEN 'DESC' = 'DESC' THEN DESC END;
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Answer based on @dale-k's comment:

    ORDER BY
  CASE WHEN :sortField = 'creationDate' AND :sortDirection = 'DESC' THEN update_.creation_date END DESC,
  CASE WHEN :sortField = 'creationDate' AND :sortDirection = 'ASC' THEN update_.creation_date END ASC,
  CASE WHEN :sortField is NULL THEN update_.default_time_field END DESC;

  2. 0

    Sort the time-difference from a fixed reference point:

    select * 
from (values
  (1, '2023-01-01 12:30:00'::timestamp)
, (2, '2023-01-02 13:45:00'::timestamp)
, (2, '2024-01-02 13:45:00'::timestamp)) as t(id, dt)
order by 
    case when :sortDirection='DESC' then -1 else 1 end *
    extract(epoch from (dt - '1900-01-01 00:00:00'::timestamp))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search