sql - Postgres : Could not identify an ordering operator for type unknown

Question

我有这个带有准备语句的查询:

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

我将 name ASC 的值发送到 $1 并将 10 的值发送到 $2

出于某种原因，我收到此错误:

could not identify an ordering operator for type unknown

如果我硬编码 name ASC 而不是 $1，像这样:

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

它工作正常。

我做错了什么？

Answer 1

对于一列，您可以使用 CASE WHEN 对其进行参数化:

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;

或:

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;

使用 CASE，您不需要将列转换为相同的数据类型以避免隐式转换错误。

编辑:

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;