将函数返回的记录拆分为多列 [英] Split function-returned record into multiple columns

问题描述

在基本的Postgres函数教程中，有一个带有OUT参数的示例，如下所示:

In a basic Postgres function tutorial there is an example with OUT parameters like so:

create or replace function hi_lo(a numeric, 
                                 b numeric, 
                                 c numeric, 
                                 OUT hi numeric, 
                                 OUT lo numeric)
as $$
begin
hi := greatest(a, b, c);
lo := least(a, b, c);
end; $$
language plpgsql;

然后结果看起来像

select hi_lo(2, 3, 4);
-- returns one column, "hi_lo" with value "(4, 2)".

select * from hi_lo(2, 3, 4);
-- returns two columns, "hi" / 4 and "lo" / 2.

但是，假设您想在执行连接的列上执行该功能，并且您无权修改该功能或使用替代功能?例如，使用一些玩具数据:

But suppose you want to execute the function on columns that come from performing a join, and that you do not have access to modify the function or use an alternative function? For example, using some toy data:

select hi_lo(a.actor_id, length(a.name), ma.movie_id) 
from 
    actors a 
join 
    movies_actors ma 
on 
    a.actor_id = ma.movie_id 
limit 10;

在单列"hi_lo"中返回的结果具有2元组的值.

returns results in a single column "hi_lo" have 2-tuple values.

将查询括在括号中并尝试从中查询select *不会更改输出的格式.所以

Wrapping the query in parentheses and trying to a select * from it does not change the format of the output. So

select *
from (
    select hi_lo(a.actor_id, length(a.name), ma.movie_id) 
    from 
        actors a 
    join 
        movies_actors ma 
    on 
        a.actor_id = ma.movie_id 
    limit 10;
) rr

不影响结果形状.

以下尝试导致错误子查询必须仅返回一列"

The following try results in the error "subquery must return only one column"

select (
    select * from hi_lo(a.actor_id, length(a.name), ma.movie_id)
) 
from 
    actors a 
join 
    movies_actors ma 
on
    a.actor_id = ma.movie_id 
limit 10;

最后，我也尝试了unnest，但是由于元组值不被视为数组，因此它给出了参数类型错误.

Finally, I also tried unnest but it gives an argument type error as the tuple values are not treated as arrays.

当无法将函数求值移动到from部分时，如何在输出中获得多列?

How can you achieve multiple columns in the output when you cannot move the function evaluation into the from section?

推荐答案

在Postgres 9.3 或更高版本中，最好使用LATERAL连接解决此问题:

In Postgres 9.3 or later this is best solved with a LATERAL join:

SELECT *
FROM   actors a 
JOIN   movies_actors ma on a.actor_id = ma.movie_id 
LEFT   JOIN LATERAL hi_lo(a.actor_id, length(a.name), ma.movie_id) x ON true
LIMIT  10;

避免重复评估函数(对于输出中的每一列-必须以任何一种方式为每个输入行调用该函数).
LEFT JOIN LATERAL ... ON true避免在函数不返回任何行的情况下从左侧删除行:

Avoids repeated evaluation of the function (for each column in the output - the function does have to be called for each input row either way).
LEFT JOIN LATERAL ... ON true to avoid dropping rows from the left side if the function returns no row:

• 什么是和PostgreSQL中的子查询之间有什么区别?

您的评论中的后续操作:

仅由函数调用产生的扩展列

only the expanded columns produced by the function call

SELECT x.*  -- that's all!
FROM   actors a 
JOIN   movies_actors ma on a.actor_id = ma.movie_id 
LEFT   JOIN LATERAL hi_lo(a.actor_id, length(a.name), ma.movie_id) x ON true
LIMIT  10;

但是由于您不关心其他列，因此可以简化为:

But since you don't care about other columns, you can simplify to:

SELECT x.*
FROM   actors a 
JOIN   movies_actors ma on a.actor_id = ma.movie_id 
     , hi_lo(a.actor_id, length(a.name), ma.movie_id) x
LIMIT  10;

这是一个隐式CROSS JOIN LATERAL.如果该函数偶尔偶尔实际上会返回"no row"，则结果可能会有所不同:我们没有获得这些行的NULL值，这些行被消除了-LIMIT不再对其进行计数.

Which is an implicit CROSS JOIN LATERAL. If the function can actually return "no row" occasionally, the result can be different: we don't get NULL values for the rows, those rows are just eliminated - and LIMIT does not count them any more.

在较旧的版本(或通常情况下)中，您也可以使用正确的语法来分解复合类型:

In older versions (or generally) you can also just decompose the composite type with the right syntax:

SELECT *, (hi_lo(a.actor_id, length(a.name), ma.movie_id)).*  -- note extra parentheses!
FROM   actors a 
JOIN   movies_actors ma on a.actor_id = ma.movie_id 
LIMIT  10;

缺点是由于Postgres查询计划程序的弱点，该函数对函数输出中的每一列都进行一次评估.最好将调用移到子查询或CTE中，并分解外部SELECT中的行类型.喜欢:

The drawback is that the function is evaluated once for each column in the function output due to a weakness in the Postgres query planner. It's better to move the call into a subquery or CTE and decompose the row type in the outer SELECT. Like:

SELECT actor_id, movie_id, (x).*  -- explicit column names for the rest
FROM  (
   SELECT *, hi_lo(a.actor_id, length(a.name), ma.movie_id) AS x
   FROM   actors a 
   JOIN   movies_actors ma on a.actor_id = ma.movie_id 
   LIMIT  10
   ) sub;

但是您必须命名单独的列，并且除非您对结果中的行类型有多余的了解，否则不能使用SELECT *. 相关:

But you have to name individual columns and can't get away with SELECT * unless you are ok with the row type in the result redundantly. Related:

• 避免在同一电话上多次通话扩展合成结果时的功能

• Avoid multiple calls on same function when expanding composite result
• How to avoid multiple function evals with the (func()).* syntax in an SQL query?

这篇关于将函数返回的记录拆分为多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！