Postgres: Could not identify an ordering operator for type unknown

Question

I have this query with prepared statement:

SELECT * FROM ONLY service_services
UNION ALL
SELECT * FROM fleet.service_services
WHERE deleted=false
ORDER BY $1
LIMIT $2

I send the value of name ASC to $1 and 10 to $2

For some reason I am getting this error:

could not identify an ordering operator for type unknown

If I hard code the name ASC instead of $1, like this:

SELECT * FROM ONLY service_services
UNION ALL
SELECT * FROM fleet.service_services
WHERE deleted=false
ORDER BY name ASC
LIMIT $1

It is working fine.

What am I doing wrong?

Answer 1

For one column you can use CASE WHEN to parametrize it:

SELECT * FROM ONLY service_services
UNION ALL
SELECT * FROM fleet.service_services
WHERE deleted=false
ORDER BY 
  CASE WHEN $1 = 'name' THEN name
       WHEN $1 = 'col_name' THEN col_name
       ELSE ...
  END
LIMIT $2;

or:

SELECT * FROM ONLY service_services
UNION ALL
SELECT * FROM fleet.service_services
WHERE deleted=false
ORDER BY 
  CASE $1
       WHEN 'name' THEN name
       WHEN 'col_name' THEN col_name
       ELSE column_name -- default sorting
  END
LIMIT $2;

Using CASE you nay need to cast column to the same datatype to avoid implicit conversion errors.

EDIT:

SELECT sub.*
FROM (
    SELECT * FROM ONLY service_services
    UNION ALL
    SELECT * FROM fleet.service_services
    WHERE deleted=false
) As sub
ORDER BY 
    CASE $1
           WHEN 'name' THEN name
           WHEN 'col_name' THEN col_name
           ELSE column_name -- default sorting
    END
LIMIT $2;

Answer 2

you can't pass column names as a variable (unless you're using dynamic query building)