将函数返回的记录拆分为多列

一尘不染

将函数返回的记录拆分为多列

sql

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

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.

但是，假设您要在执行连接所产生的列上执行该函数，并且您无权修改该函数或使用替代函数？例如，使用一些玩具数据：

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元组值的结果。

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

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

不会影响结果形状。

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

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但是由于元组值不被视为数组，因此它给出了一个参数类型错误。

无法将函数求值移至该from部分时，如何在输出中获得多列？

阅读 205

2021-03-10

共1个答案

一尘不染

在Postgres 9.3 或更高版本中，最好通过联接来解决LATERAL：

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如果函数不返回行，则避免从左侧删除行

在您的评论中进行跟进：

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

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;

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

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不再计数。

在 较旧的版本中 （或通常），您也可以使用正确的语法来分解复合类型：

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。喜欢：

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 *除非您对结果中的行类型有多余的理解，否则您将无法避免。

2021-03-10