我有一个昂贵的函数,它仅在满足某些条件时提供相关数据。该数据通过 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;