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.
In Postgres 9.3 or later use a LATERAL
join:
SELECT v.col_a, v.col_b, f.* -- no parentheses here, 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
?
For older versions, there is a very simple way to accomplish what I think you are trying to with a set-returning function (RETURNS TABLE
or RETURNS SETOF record
OR RETURNS record
):
SELECT *, (f_citizen_rec_modified(col1, col2)).*
FROM v_citizenversions v
The function computes values once for every row of the outer query. If the function returns multiple rows, resulting rows are multiplied accordingly. All parentheses are syntactically required to decompose a row type. The table function could look something like this:
CREATE OR REPLACE FUNCTION f_citizen_rec_modified(_col1 int, _col2 text)
RETURNS TABLE(col_c integer, col_d text) AS
$func$
SELECT s.col_c, s.col_d
FROM some_tbl s
WHERE s.col_a = $1
AND s.col_b = $2
$func$ LANGUAGE sql;
You need to wrap this in a subquery or CTE if you want to apply a WHERE
clause because the columns are not visible on the same level. (And it's better for performance anyway, because you prevent repeated evaluation for every output column of the function):
SELECT col_a, col_b, (f_row).*
FROM (
SELECT col_a, col_b, f_citizen_rec_modified(col1, col2) AS f_row
FROM v_citizenversions v
) x
WHERE (f_row).col_c = _col_c;
There are several other ways to do this or something similar. It all depends on what you want exactly.