Postgres analogue to CROSS APPLY in SQL Server

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 aliasFROM   v_citizenversions vLEFT   JOIN LATERAL f_citizen_rec_modified(v.col1, v.col2) f ON trueWHERE  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) AS$func$SELECT s.col_c, s.col_dFROM   some_tbl sWHERE  s.col_a = $1AND    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   ) xWHERE (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.

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 -- 989LEFT 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 

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) funcFROM   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