LEFT JOIN LATERAL with a function result, how to prevent unnecessary function calls?
-1
votes
2
answers
314
views
I have a costly function and it only provides relevant data when certain conditions are met. That data is inserted into the result through a LEFT JOIN LATERAL as shown below. As it works now, the function is called for every row.
Is there a way to rewrite it so that it is only called for rows where t1.type is equal to 5? The below still calls the function for every row, although I have a condition set.
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;
Test case:
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;
Asked by Björn Morén
(143 rep)
Apr 12, 2024, 05:37 AM
Last activity: Apr 12, 2024, 11:43 AM
Last activity: Apr 12, 2024, 11:43 AM