PostgreSQL函数实现按变量动态切换ASC/DESC排序方向

问题分析 #

你遇到的报错是因为在CTE的ORDER BY子句中，直接用CASE引用price_delta_percentage时，PostgreSQL的解析顺序导致无法识别该别名；同时原函数缺少接收sort参数的定义，这也是必须补充的部分。

解决方案 #

方法1：利用数值列特性实现动态排序（兼容SQL函数） #

由于price_delta_percentage是数值类型，我们可以通过对其取反来模拟升序/降序的切换，无需复杂的CASE分支，同时补充函数的参数定义：

CREATE OR REPLACE FUNCTION get_top_price_changes(
    set_code varchar,
    days int,
    sort varchar DEFAULT 'DESC' -- 添加排序方向参数，默认降序
)
RETURNS TABLE(
    product_id varchar, 
    name varchar, 
    price_today numeric,
    price_yesterday numeric,
    price_delta numeric,
    price_delta_percentage numeric
)
LANGUAGE 'sql'
COST 100
STABLE STRICT PARALLEL SAFE 
AS $BODY$
    WITH cte AS (
        SELECT
            product_id,
            name,
            SUM(CASE WHEN rank = 1 THEN trend_price ELSE NULL END) price_today, 
            SUM(CASE WHEN rank = 2 THEN trend_price ELSE NULL END) price_yesterday,
            SUM(CASE WHEN rank = 1 THEN trend_price ELSE 0 END) - SUM(CASE WHEN rank = 2 THEN trend_price ELSE 0 END) as price_delta,
            ROUND(((SUM(CASE WHEN rank = 1 THEN trend_price ELSE NULL END) / SUM(CASE WHEN rank = 2 THEN trend_price ELSE NULL END) - 1) * 100), 2) as price_delta_percentage
        FROM (
            SELECT
                magic_sets_cards.name,
                pricing.product_id,
                pricing.trend_price, 
                pricing.date, 
                RANK() OVER (PARTITION BY product_id ORDER BY date DESC) AS rank
            FROM pricing
                JOIN magic_sets_cards_identifiers ON magic_sets_cards_identifiers.mcm_id = pricing.product_id
                JOIN magic_sets_cards ON magic_sets_cards.id = magic_sets_cards_identifiers.card_id
                JOIN magic_sets ON magic_sets.id = magic_sets_cards.set_id
            WHERE date BETWEEN CURRENT_DATE - days AND CURRENT_DATE
                AND magic_sets.code = set_code
                AND pricing.trend_price > 0.25) p
        WHERE rank IN (1,2)
          -- 提前过滤无效数据，避免后续计算出错
          AND SUM(CASE WHEN rank = 1 THEN trend_price ELSE NULL END) IS NOT NULL
          AND SUM(CASE WHEN rank = 2 THEN trend_price ELSE NULL END) IS NOT NULL
        GROUP BY product_id, name
        -- 核心：通过取反实现动态排序
        ORDER BY 
            CASE WHEN sort = 'DESC' THEN price_delta_percentage ELSE -price_delta_percentage END DESC
    )
    SELECT * FROM cte
    LIMIT 5;
$BODY$;

方法2：使用PL/pgSQL动态SQL（更灵活） #

如果需要支持非数值列的动态排序，或者更复杂的排序逻辑，可以改用PL/pgSQL语言，通过动态拼接SQL实现：

CREATE OR REPLACE FUNCTION get_top_price_changes(
    set_code varchar,
    days int,
    sort varchar DEFAULT 'DESC'
)
RETURNS TABLE(
    product_id varchar, 
    name varchar, 
    price_today numeric,
    price_yesterday numeric,
    price_delta numeric,
    price_delta_percentage numeric
)
LANGUAGE plpgsql
COST 100
STABLE STRICT PARALLEL SAFE 
AS $BODY$
BEGIN
    -- 验证排序方向参数合法性
    IF sort NOT IN ('ASC', 'DESC') THEN
        RAISE EXCEPTION 'sort参数只能是ASC或DESC';
    END IF;
    
    RETURN QUERY EXECUTE format(
        'WITH cte AS (
            SELECT
                product_id,
                name,
                SUM(CASE WHEN rank = 1 THEN trend_price ELSE NULL END) price_today, 
                SUM(CASE WHEN rank = 2 THEN trend_price ELSE NULL END) price_yesterday,
                SUM(CASE WHEN rank = 1 THEN trend_price ELSE 0 END) - SUM(CASE WHEN rank = 2 THEN trend_price ELSE 0 END) as price_delta,
                ROUND(((SUM(CASE WHEN rank = 1 THEN trend_price ELSE NULL END) / SUM(CASE WHEN rank = 2 THEN trend_price ELSE NULL END) - 1) * 100), 2) as price_delta_percentage
            FROM (
                SELECT
                    magic_sets_cards.name,
                    pricing.product_id,
                    pricing.trend_price, 
                    pricing.date, 
                    RANK() OVER (PARTITION BY product_id ORDER BY date DESC) AS rank
                FROM pricing
                    JOIN magic_sets_cards_identifiers ON magic_sets_cards_identifiers.mcm_id = pricing.product_id
                    JOIN magic_sets_cards ON magic_sets_cards.id = magic_sets_cards_identifiers.card_id
                    JOIN magic_sets ON magic_sets.id = magic_sets_cards.set_id
                WHERE date BETWEEN CURRENT_DATE - $1 AND CURRENT_DATE
                    AND magic_sets.code = $2
                    AND pricing.trend_price > 0.25) p
            WHERE rank IN (1,2)
              AND SUM(CASE WHEN rank = 1 THEN trend_price ELSE NULL END) IS NOT NULL
              AND SUM(CASE WHEN rank = 2 THEN trend_price ELSE NULL END) IS NOT NULL
            GROUP BY product_id, name
            ORDER BY price_delta_percentage %s
        )
        SELECT * FROM cte LIMIT 5',
        sort -- 动态插入排序方向
    ) USING days, set_code;
END;
$BODY$;

关键说明 #

• 方法1利用数值列的正负反转实现排序方向切换，完全兼容SQL函数，性能更优。
• 方法2使用动态SQL，适合更复杂的排序场景，通过format和USING参数化处理，避免SQL注入风险。
• 提前在CTE中过滤无效数据，既避免后续计算出错，也能提升查询效率。

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