PL/pgSQL函数中动态传递ORDER BY与ASC/DESC排序方向问题

我来帮你搞定这个排序方向无法正常传递的问题！你的核心问题出在动态SQL的拼接方式和参数合法性校验上，直接拼接未处理的排序方向不仅可能失效，还存在SQL注入风险。下面是修正后的完整函数代码，以及关键要点的解释：

修正后的函数代码 #

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
    -- 先校验排序方向，只允许ASC/DESC，非法输入默认用ASC
    valid_order varchar(4) := UPPER(_order);
BEGIN
    -- 验证排序方向合法性
    IF valid_order NOT IN ('ASC', 'DESC') THEN
        valid_order := 'ASC';
    END IF;

    RETURN Query EXECUTE format(
        'SELECT 
            d.id, 
            d.name, 
            d.clientname,
            COUNT(*) OVER() AS totalcount  -- 用窗口函数获取总条数
        FROM your_table_name d  -- 替换成你的实际表名
        WHERE d.category = $1  -- 用USING传递参数，避免SQL注入
        ORDER BY %I %s  -- %I格式化字段名，%s用已校验的排序方向
        LIMIT $2 OFFSET $3',
        sort_by, valid_order
    ) USING _category, _limit, _offset;
END;
$$ LANGUAGE plpgsql;

关键要点解释 #

• 排序方向合法性校验：先把传入的_order转成大写，然后判断是否是合法的ASC或DESC，非法输入直接默认用ASC，避免因为大小写错误（比如传了asc或者desc小写）或者恶意输入导致SQL错误。
• 使用format()构建动态SQL：
  • %I会自动将字段名（sort_by）格式化为合法的SQL标识符，处理字段名包含特殊字符、空格或者和SQL关键字重名的情况。
  • %s用来插入已校验过的排序方向，因为已经做了合法性校验，不用担心注入风险。
• 用USING传递参数：像_category、_limit、_offset这类值型参数，用USING传递比直接拼接字符串更安全，也不需要手动加引号转义。
• 总计数的正确获取：用COUNT(*) OVER()窗口函数可以在返回数据行的同时，一次性获取符合条件的总条数，比单独查询计数更高效。

调用这个函数的时候，就可以正常传递ASC或DESC作为排序方向了，比如：

SELECT * FROM list(10, 0, 'name', 'DESC', 'electronics');

内容的提问来源于stack exchange，提问作者Sachin