主页 / user-288561

Björn Morén's questions

Martin Hope
Björn Morén
Asked: 2024-11-08 14:43:57 +0800 CST
  • 8

我想以三种排序顺序之一返回我的帖子行，由sorting_param函数参数控制。排序很复杂，不适合简单的排序ORDER BY CASE ...。所以我正在考虑将每个排序写入其自己的 CTE，然后选择其中一个 CTE 作为最终输出。但是，我不希望 PostgreSQL 浪费时间在这三个上，只浪费我选择的那个。那么这三个都会实现还是只有我选的那个会实现？或者有更好的方法吗？PostgreSQL 版本 16。简化代码如下。

WITH posts_by_order1 AS
(
    SELECT p.id
    FROM post p
    ORDER BY [some complex ordering 1]
    LIMIT 10
),
posts_by_order2 AS
(
    SELECT p.id
    FROM post p
    ORDER BY [some complex ordering 2]
    LIMIT 10
),
posts_by_order3 AS
(
    SELECT p.id
    FROM post p
    ORDER BY [some complex ordering 3]
    LIMIT 10
)
SELECT * FROM posts_by_order1 WHERE sorting_param = 0
UNION ALL
SELECT * FROM posts_by_order2 WHERE sorting_param = 1
UNION ALL
SELECT * FROM posts_by_order3 WHERE sorting_param = 2;

不幸的是我不能这样做：

    ORDER BY 
        CASE 
            WHEN sorting_param = 0 THEN p.date 
            WHEN sorting_param = 1 THEN p.name 
            ELSE NULL 
        END DESC, ...
postgresql
Martin Hope
Björn Morén
Asked: 2024-04-12 13:37:18 +0800 CST
  • 4

我有一个昂贵的函数，它仅在满足某些条件时提供相关数据。该数据通过 LEFT JOIN LATERAL 插入到结果中，如下所示。现在，该函数会为每一行调用。

有没有办法重写它，以便仅对 t1.type 等于 5 的行调用它？尽管我设置了条件，但下面仍然为每一行调用该函数。

SELECT t1.name, t1.type, t2.col1, t2.col2
FROM table1 t1
LEFT JOIN LATERAL function_name(t1.col1, t1.col2) AS t2 ON t1.type = 5;

测试用例：

CREATE TABLE table1 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    type INTEGER,
    col1 INTEGER,
    col2 INTEGER
);

-- Sample data for table1
INSERT INTO table1 (name, type, col1, col2)
VALUES
    ('Row 1', 5, 10, 20),
    ('Row 2', 7, 15, 25),
    ('Row 3', 10, 30, 40); 

CREATE OR REPLACE FUNCTION function_name(col1_arg INTEGER, col2_arg INTEGER)
RETURNS TABLE (col1 INTEGER, col2 INTEGER)
AS $$
BEGIN
    RAISE LOG 'Function called';
    RETURN QUERY SELECT col1_arg * 2 AS col1, col2_arg * 3 AS col2;
END;
$$ LANGUAGE plpgsql;


-- Test Query
SELECT t1.name, t1.type, t2.col1, t2.col2
FROM table1 t1
LEFT JOIN LATERAL function_name(t1.col1, t1.col2) AS t2 ON t1.type = 5;
postgresql