如何在SQL查询中使用（func（））。*语法避免使用多个函数评估？

小编典典

如何在SQL查询中使用（func（））。*语法避免使用多个函数评估？

sql

Context
当函数返回aTABLE或a时SETOF composite-type，例如以下示例函数：

CREATE FUNCTION func(n int) returns table(i int, j bigint) as $$
BEGIN
  RETURN QUERY select 1,n::bigint 
      union all select 2,n*n::bigint
      union all select 3,n*n*n::bigint;
END
$$ language plpgsql;

可以通过多种方法访问结果：

1）select * from func(3)将产生以下输出列：

i Ĵ
--- +-
 1 | 3
 2 | 9
 3 | 27

2）select func(3)将仅产生ROW类型的一个输出列。

unc

（1,3）
（2,9）
（3,27）


3）`select (func(3)).*`会产生类似＃1的结果：

i Ĵ
— +-
1 | 3
2 | 9
3 | 27


当函数参数来自表或子查询时，语法＃3是唯一可能的语法，如下所示：

select N, (func(N)).* from (select 2 as N union select 3 as N) s;


或如此相关答案所示。如果LATERAL JOIN可以的话，可以使用它，但是直到发布PostgreSQL 9.3为止，才不支持它，并且以前的版本仍然会使用很多年。

问题
现在，语法＃3的问题在于该函数被调用的次数与结果中列的调用次数相同。没有明显的原因，但是它确实发生了。通过RAISE NOTICE 'called for %', n在函数中添加，我们可以在9.2版中看到它。使用上面的查询，它输出：

NOTICE: called for 2
NOTICE: called for 2
NOTICE: called for 3
NOTICE: called for 3

现在，如果将函数更改为返回4列，如下所示：

CREATE FUNCTION func(n int) returns table(i int, j bigint,k int, l int) as $$
BEGIN
raise notice ‘called for %’, n;
RETURN QUERY select 1,n::bigint,1,1
union all select 2,nn::bigint,1,1
union all select 3,nn*n::bigint,1,1;
END
$$ language plpgsql stable;


然后相同的查询输出：

NOTICE: called for 2
NOTICE: called for 2
NOTICE: called for 2
NOTICE: called for 2
NOTICE: called for 3
NOTICE: called for 3
NOTICE: called for 3
NOTICE: called for 3


需要2个函数调用，实际进行了8个调用。该比率是输出列数。

使用语法2会产生相同的结果（除了输出列的布局），这些多次调用不会发生：

select N,func(N) from (select 2 as N union select 3 as N) s;


给出：

NOTICE: called for 2
NOTICE: called for 3


随后是6个结果行：

n | func
— + ------------
2 | （1,2,1,1）
2 | （2,4,1,1）
2 | （3,8,1,1）
3 | （1,3,1,1）
3 | （2,9,1,1）
3 | （3,27,1,1）
```

问题
是否存在仅通过执行最少的必需函数调用即可达到预期结果的9.2语法或构造？

额外的问题：为什么要进行多次评估？

阅读 494

2021-03-23

共1个答案

小编典典

您可以将其包装在子查询中，但是如果没有OFFSET 0hack ，就不能保证安全。在9.3中，使用LATERAL。该问题是由于解析器有效地将宏扩展*为列列表而引起的。

解决方法
在哪里：

SELECT (my_func(x)).* FROM some_table;

将计算函数中结果列的my_func n时间n，公式如下：

SELECT (mf).* FROM (
    SELECT my_func(x) AS mf FROM some_table
) sub;

通常不会，并且往往不会在运行时添加其他扫描。为了保证不会执行多次评估，您可以使用OFFSET 0黑客或滥用PostgreSQL的失败来跨CTE边界进行优化：

SELECT (mf).* FROM (
    SELECT my_func(x) AS mf FROM some_table OFFSET 0
) sub;

或者：

WITH tmp(mf) AS (
    SELECT my_func(x) FROM some_table
)
SELECT (mf).* FROM tmp;

在PostgreSQL 9.3中，您可以LATERAL用来获得更合理的行为：

SELECT mf.*
FROM some_table
LEFT JOIN LATERAL my_func(some_table.x) AS mf ON true;

LEFT JOIN LATERAL ... ON true即使函数调用不返回任何行，也保留与原始查询类似的所有行。

演示版
创建一个不可内联的函数作为演示：

CREATE OR REPLACE FUNCTION my_func(integer)
RETURNS TABLE(a integer, b integer, c integer) AS $$
BEGIN
    RAISE NOTICE 'my_func(%)',$1;
    RETURN QUERY SELECT $1, $1, $1;
END;
$$ LANGUAGE plpgsql;

和伪数据表：

CREATE TABLE some_table AS SELECT x FROM generate_series(1,10) x;

然后尝试以上版本。您会看到第一个每次调用会引发三个通知；后者只举一个。

为什么？
好问题。这太糟糕了。

看起来像：

(func(x)).*

扩展为：

(my_func(x)).i, (func(x)).j, (func(x)).k, (func(x)).l

在解析，根据一看debug_print_parse，debug_print_rewritten和debug_print_plan。（修剪后的）分析树如下所示：

   :targetList (
      {TARGETENTRY 
      :expr 
         {FIELDSELECT 
         :arg 
            {FUNCEXPR 
            :funcid 57168 
                 ...
            }
         :fieldnum 1 
         :resulttype 23 
         :resulttypmod -1 
         :resultcollid 0
         }
      :resno 1 
      :resname i 
       ...
      }
      {TARGETENTRY 
      :expr 
         {FIELDSELECT 
         :arg 
            {FUNCEXPR 
            :funcid 57168 
                 ...
            }
         :fieldnum 2 
         :resulttype 20 
         :resulttypmod -1 
         :resultcollid 0
         }
      :resno 2 
      :resname j 
       ...
      }
      {TARGETENTRY 
      :expr 
         {FIELDSELECT 
         :arg 
            {FUNCEXPR 
            :funcid 57168 
             ...
            }
         :fieldnum 3 
         :...
         }
      :resno 3 
      :resname k 
       ...
      }
      {TARGETENTRY 
      :expr 
         {FIELDSELECT 
         :arg 
            {FUNCEXPR 
            :funcid 57168 
             ...
            }
         :fieldnum 4 
          ...
         }
      :resno 4 
      :resname l 
       ...
      }
   )

因此，基本上，我们使用的是哑巴分析器黑客工具，通过克隆节点来扩展通配符。

2021-03-23