与SQL Server中的交叉应用类似的Postgres

本文介绍了与SQL Server中的交叉应用类似的Postgres的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要将为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或RETURNS SETOF record或RETURNS record ):

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.