Аналог Postgres для кросс-применения в SQL Server

Мне нужно перенести SQL-запросы, написанные для MS SQL Server 2005, в Postgres 9.1.
Как лучше всего заменить CROSS APPLY в этом запросе? 

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

GetCitizenRecModified() Функция-это функция с табличным значением. Я не могу поместить код этой функции, потому что она действительно огромна, она делает некоторые сложные вычисления, и я не могу отказаться от нее.

4 12
sql postgresql postgresql-9.1 lateral

4 ответа:

В Постгресе 9.3 или позже использовать LATERAL присоединиться: 

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;

Почему LEFT JOIN LATERAL ... ON true?

Для более старых версий существует очень простой способ выполнить то, что я думаю, что вы пытаетесь с помощью функции set-returning (RETURNS TABLE или RETURNS SETOF record или RETURNS record):

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

Функция вычисляет значения один раз для каждой строки внешнего запроса. Если функция возвращает несколько строк, полученные строки умножаются соответственно. Всескобки синтаксически необходимы для декомпозиции типа строки. Табличная функция может выглядеть примерно так: 

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;
Если вы хотите применить предложение WHERE, вам нужно обернуть его в подзапрос или CTE, поскольку столбцы не видны на одном уровне. (И это лучше для производительности в любом случае, потому что вы предотвращаете повторную оценку для каждого выходного столбца функция): 
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;
Есть несколько других способов сделать это или что-то подобное. Все зависит от того, чего именно вы хотите.
13

Некромантия:
Новое в PostgreSQL 9.3:

Ключевое слово LATERAL

Левое | правое / внутреннее соединение боковое

INNER JOIN LATERAL это то же самое, что CROSS APPLY
и LEFT JOIN LATERAL то же самое, что OUTER APPLY

Пример использования: 

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
10

Мне нравится ответ Эрвина Брандштеттера, однако я обнаружил проблему производительности: при запуске 

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

Функция f_citizen_rec_modified будет выполняться 1 раз для каждого возвращаемого столбца (умножается на каждую строку в v_citizenversions). Я не нашел документации для этого эффекта, но смог вывести его путем отладки. Теперь возникает вопрос, как мы можем получить этот эффект (до 9.3, где доступны боковые соединения) без этой грабящей производительность стороны эффект?

Обновление: кажется, я нашел ответ. Перепишите запрос следующим образом: 

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

Ключевое отличие заключается в том, что сначала получают необработанные результаты функции (внутренний подзапрос), а затем переносят их в другой select, который разбивает эти результаты на столбцы. Это было проверено на PG 9.2

2

Эта ссылка показывает, как это сделать в Postgres 9.0+:

PostgreSQL: параметризация рекурсивного CTE

Это ниже по странице в разделе под названием "эмуляция перекрестного применения с функциями возврата набора". Пожалуйста, обратите внимание на список ограничений после примера.

1