Postgres 类似于 SQL Server 中的 CROSS APPLY [英] Postgres analogue to CROSS APPLY in SQL Server

问题描述

我需要将为 MS SQL Server 2005 编写的 SQL 查询迁移到 Postgres 9.1.
在此查询中替换 CROSS APPLY 的最佳方法是什么?

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() 函数是一个表值函数.这个函数的代码我放不下，因为它真的很大，计算起来很困难，我不能放弃.

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.

推荐答案

在 Postgres 9.3 或更高版本中使用 LATERAL 加入:>

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;

为什么LEFT JOIN LATERAL ... ON true?

• 从函数返回的记录具有连接的列

对于旧版本，有一种非常简单的方法可以实现我认为您正在尝试使用集合返回函数(RETURNS TABLE or RETURNS SETOF record OR 退货记录):

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;

如果要应用 WHERE 子句，则需要将其包装在子查询或 CTE 中，因为列在同一级别不可见.(无论如何，这对性能更好，因为您可以防止对函数的每个输出列进行重复评估):

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.

这篇关于Postgres 类似于 SQL Server 中的 CROSS APPLY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！