Postgres analogue to CROSS APPLY in SQL Server

user_1178399 0
Viewed 45462

I need to migrate SQL queries written for MS SQL Server 2005 to Postgres 9.1.
What is the best way to substitute for CROSS APPLY in this query? 

SELECT *
FROM V_CitizenVersions         
CROSS APPLY     
       dbo.GetCitizenRecModified(Citizen, LastName, FirstName, MiddleName,
BirthYear, BirthMonth, BirthDay, ..... ) -- lots of params

GetCitizenRecModified() function is a table valued function. I can't place code of this function because it's really enormous, it makes some difficult computations and I can't abandon it.

lateral postgresql-9.1 cross-apply postgresql sql
4 Answers

I like Erwin Brandstetter's answer however, I've discovered a performance problem: when running

SELECT *, (f_citizen_rec_modified(col1, col2)).*
FROM   v_citizenversions v

The f_citizen_rec_modified function will be ran 1 time for every column it returns (multiplied by every row in v_citizenversions). I did not find documentation for this effect, but was able to deduce it by debugging. Now the question becomes, how can we get this effect (prior to 9.3 where lateral joins are available) without this performance robbing side effect?

Update: I seem to have found an answer. Rewrite the query as follows:

select x.col1, x.col2, x.col3, (x.func).* 
FROM (select SELECT v.col1, v.col2, v.col3, f_citizen_rec_modified(col1, col2) func
FROM   v_citizenversions v) x

The key difference being getting the raw function results first (inner subquery) then wrapping that in another select that busts those results out into the columns. This was tested on PG 9.2

user_22839540
Related