Sample Header Ad - 728x90

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