Postgres analogue to CROSS APPLY in SQL Server

Question

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.

Answer 1

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 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)   LANGUAGE sql AS $func$ SELECT s.col_c, s.col_d FROM   some_tbl s WHERE  s.col_a = $1 AND    s.col_b = $2 $func$; 

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.

Answer 2

Necromancing:
New in PostgreSQL 9.3:

The LATERAL keyword

left | right | inner JOIN LATERAL

INNER JOIN LATERAL is the same as CROSS APPLY
and LEFT JOIN LATERAL is the same as OUTER APPLY

Example usage:

SELECT * FROM T_Contacts   --LEFT JOIN T_MAP_Contacts_Ref_OrganisationalUnit ON MAP_CTCOU_CT_UID = T_Contacts.CT_UID AND MAP_CTCOU_SoftDeleteStatus = 1  --WHERE T_MAP_Contacts_Ref_OrganisationalUnit.MAP_CTCOU_UID IS NULL -- 989   LEFT JOIN LATERAL  (     SELECT           --MAP_CTCOU_UID              MAP_CTCOU_CT_UID            ,MAP_CTCOU_COU_UID           ,MAP_CTCOU_DateFrom          ,MAP_CTCOU_DateTo       FROM T_MAP_Contacts_Ref_OrganisationalUnit     WHERE MAP_CTCOU_SoftDeleteStatus = 1     AND MAP_CTCOU_CT_UID = T_Contacts.CT_UID       /*       AND      (          (__in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo)          AND          (__in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom)      )      */    ORDER BY MAP_CTCOU_DateFrom     LIMIT 1  ) AS FirstOE