You where right, Albert. I made some tests and found that it’s possible, indeed. The use is the same as in a SELECT statement. For example: UPDATE st SET some_row = A.another_row, some_row2 = A.another_row/2 FROM some_table st CROSS APPLY (SELECT TOP 1 another_row FROM another_table at WHERE at.shared_id=st.shared_id) AS A WHERE …
In Postgres 9.3 or later use a LATERAL join: SELECT v.col_a, v.col_b, f.* — no parentheses, f is a table alias FROM v_citizenversions v LEFT JOIN LATERAL f_citizen_rec_modified(v.col1, v.col2) f ON true WHERE f.col_c = _col_c; Why LEFT JOIN LATERAL … ON true? Record returned from function has columns concatenated For older versions, there is … Read more
Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well? See the article in my blog for detailed performance comparison: INNER JOIN vs. CROSS APPLY CROSS APPLY works better on things that have no simple JOIN condition. This one selects 3 … Read more