Dynamic ORDER BY and ASC / DESC in a plpgsql function

Question

Following the approach mentioned in this link, I want to pass ORDER BY and sorting order to a function dynamically.

ORDER BY is working fine but I am not able to pass sorting order (ASC / DESC).

What I have now:

CREATE OR REPLACE FUNCTION list(_limit integer,_offset integer,sort_by varchar(100), _order varchar(100),_category varchar(100))
  RETURNS TABLE(
     id INTEGER,
     name VARCHAR,
     clientname VARCHAR,
     totalcount BIGINT
  ) AS
$$
DECLARE
   empty text := '';
BEGIN
RETURN Query EXECUTE
'SELECT d.id,
d.name,
d.clientname,
 count(*) OVER() AS full_count FROM design_list as d 
    where ($5 = $6 Or d.category Ilike $5) 
        ORDER BY ' || quote_ident(sort_by) || ' LIMIT $1 offset $2'
USING _limit,_offset,sort_by, _order,_category, empty;
END;
$$  LANGUAGE plpgsql;

Haircloth

I don't get this at all. Why are you mixing concatenation with USING/$ in the same statement? Why are you putting 5 parameters in USING when you don't use $3 and $4 (sort_by and _order, as it happens)? You ORDER BY sort_by, don't use _order at all, and say ordering is working and sorting isn't??? – Affair

Answer 1

I would do it like this:

CREATE OR REPLACE FUNCTION list(
      _category varchar(100)
    , _limit int
    , _offset int
    , _order_by varchar(100)
    , _order_asc_desc text = 'ASC')  -- last param with default value
  RETURNS TABLE(id int, name varchar, clientname varchar, totalcount bigint)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _empty text := '';
BEGIN
   -- Assert valid _order_asc_desc
   IF upper(_order_asc_desc) IN ('ASC', 'DESC', 'ASCENDING', 'DESCENDING') THEN
      -- proceed
   ELSE
      RAISE EXCEPTION 'Unexpected value for parameter _order_asc_desc.
                       Allowed: ASC, DESC, ASCENDING, DESCENDING. Default: ASC';
   END IF;
   
   RETURN QUERY EXECUTE format(
     'SELECT id, name, clientname, count(*) OVER() AS full_count
      FROM   design_list
      WHERE ($1 = $2 OR category ILIKE $1) 
      ORDER  BY %I %s
      LIMIT  %s
      OFFSET %s'
    , _order_by, _order_asc_desc, _limit, _offset)
   USING _category, _empty;
END
$func$;

Core feature: use format() to safely and elegantly concatenate your query string. Related:

• INSERT with dynamic table name in trigger function
• Format specifier for integer variables in format() for EXECUTE?

ASC / DESC (or ASCENDING / DESCENDING) are fixed key words. I added a manual check (IF ...) and later concatenate with a simple %s. That's one way to assert legal input. For convenience, I added an error message for unexpected input and a parameter default, so the function defaults to ASC if the last parameter is omitted in the call. Related:

• Optional argument in PL/pgSQL function
• ERROR: input parameters after one with a default value must also have defaults in Postgres

Addressing Pavel's valid comment, I concatenate _limit and _offset directly, so the query is already planned with those parameters.

_limit and _offset are integer parameters, so we can use plain %s without the danger of SQL injection. You might want to assert reasonable values (exclude negative values and values too high) before concatenating ...

Other notes:

• Use a consistent naming convention. I prefixed all parameters and variables with an underscore _, not just some.

• Not using table qualification inside EXECUTE, since there is only a single table involved and the EXECUTE has its separate scope.

• I renamed some parameters to clarify. _order_by instead of _sort_by; _order_asc_desc instead of _order.

Pelkey

LIMIT, OFFSET are important clauses for optimization - I am not sure if passing these parameters via USING clause can have negative impact on execution plan quality. This is classic example of single query wrapping - important and well known performance antipattern. – Embitter

@PavelStehule: Good point. I added an alternative for that as well. – Pelkey

Answer 2

non dynamic sql solution.

CREATE OR REPLACE FUNCTION list(
...
in_use_asc boolean default false,
_order_by varchar(100)
..
)
..


CREATE TEMPORARY TABLE tempHolder ON COMMIT DROP AS
SELECT SELECT id, name, clientname, count(*) OVER() AS full_count
      FROM   design_list
      WHERE ($1 = $2 OR category ILIKE $1);

     IF in_use_asc = TRUE THEN
        RETURN QUERY SELECT * FROM tempHolder ORDER BY _order_by asc LIMIT {} OFFSET {};
    ELSE 
        RETURN QUERY SELECT * FROM tempHolder ORDER BY _order_by desc LIMIT {} OFFSET {};
    END IF;

Shouldnt be any slower because SQL needs to grab everything anyway because of the ORDER BY plus you avoid the dynamic SQL.

Duiker

I don't think this will work. In your RETURN QUERY, it will just apply the ORDER BY to the literal string value passed in to _order_by_, which is a constant value and will thus negate any ordering. I only mention this because I'm trying to figure out the same problem and tried the same thing you did! – Binder