如何在SQL查询中使用(func()).*语法避免使用多个函数评估? [英] How to avoid multiple function evals with the (func()).* syntax in an SQL query?
问题描述
当函数返回TABLE或SETOF composite-type时，例如以下示例函数:
When a function returns a TABLE or a SETOF composite-type, like this sample function:
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;
可以通过多种方法访问结果:
the results can be accessed by various methods:
1)select * from func(3)将产生以下输出列:
1) select * from func(3) will produce these output columns :
i | j
---+---
1 | 3
2 | 9
3 | 27
2)select func(3)将仅生成ROW类型的一个输出列.
2) select func(3) will produce only one output column of ROW type.
func
-------
(1,3)
(2,9)
(3,27)
3)select (func(3)).*将产生类似#1的结果:
3) select (func(3)).* will produce like #1:
i | j
---+---
1 | 3
2 | 9
3 | 27
当函数参数来自表或子查询时，语法#3是唯一可能的语法，如下所示:
When the function argument comes from a table or a subquery, the syntax #3 is the only possible one, as in:
select N, (func(N)).* from (select 2 as N union select 3 as N) s;
或与此相关的 answer 相同.如果我们有LATERAL JOIN，我们可以使用它，但是在PostgreSQL 9.3发行之前，它不受支持，而且以前的版本仍然会使用多年.
or as in this related answer. If we had LATERAL JOIN we could use that, but until PostgreSQL 9.3 is out, it's not supported, and the previous versions will still be used for years anyway.
现在语法3的问题在于，函数调用的次数与结果中的列数相同.没有明显的原因，但是它确实发生了.
通过在函数中添加RAISE NOTICE 'called for %', n，我们可以在9.2版中看到它.通过上面的查询，它输出:
Now the problem with syntax #3 is that the function is called as many times as there are columns in the result. There's no apparent reason for that, but it happens.
We can see it in version 9.2 by adding a RAISE NOTICE 'called for %', n in the function. With the query above, it outputs:
NOTICE: called for 2
NOTICE: called for 2
NOTICE: called for 3
NOTICE: called for 3
现在，如果将函数更改为返回4列，就像这样:
Now if the function is changed to return 4 columns, like this:
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,n*n::bigint,1,1
union all select 3,n*n*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 function calls were needed, 8 were actually made. The ratio is the number of output columns.
使用语法2会产生相同的结果(除了输出列的布局)，这些多次调用不会发生:
With syntax #2 that produces the same result except for the output columns layout, these multiple calls don't happen:
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构造?
Questions
Is there a syntax or a construct with 9.2 that would achieve the expected result by doing only the minimum required function calls?
奖金问题:为什么根本没有进行多次评估?
Bonus question: why do the multiple evaluations happen at all?
推荐答案
您可以将其包装在子查询中，但是如果没有OFFSET 0 hack，就不能保证安全.在9.3中，使用LATERAL.该问题是由于解析器有效地将*宏扩展为列列表而引起的.
You can wrap it up in a subquery but that's not guaranteed safe without the OFFSET 0 hack. In 9.3, use LATERAL. The problem is caused by the parser effectively macro-expanding * into a column list.
位置:
SELECT (my_func(x)).* FROM some_table;
将从函数中计算n结果列的时间计算my_func n次:
will evaluate my_func n times for n result columns from the function, this formulation:
SELECT (mf).* FROM (
SELECT my_func(x) AS mf FROM some_table
) sub;
通常不会，并且往往不会在运行时添加其他扫描.为了保证不会执行多次评估，您可以使用OFFSET 0 hack或滥用PostgreSQL的失败来跨CTE边界进行优化:
generally will not, and tends not to add an additional scan at runtime. To guarantee that multiple evaluation won't be performed you can use the OFFSET 0 hack or abuse PostgreSQL's failure to optimise across CTE boundaries:
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以获得更合理的行为:
In PostgreSQL 9.3 you can use LATERAL to get a saner behaviour:
SELECT mf.*
FROM some_table
LEFT JOIN LATERAL my_func(some_table.x) AS mf ON true;
LEFT JOIN LATERAL ... ON true像原始查询一样保留所有行，即使函数调用不返回任何行.
LEFT JOIN LATERAL ... ON true retains all rows like the original query, even if the function call returns no row.
创建一个不可内联的函数作为演示:
Create a function that isn't inlineable as a demonstration:
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;
然后尝试上述版本.您会看到第一个每次调用会引发三个通知；后者只举一个.
then try the above versions. You'll see that the first raises three notices per invocation; the latter only raise one.
好问题.太可怕了.
它看起来像:
(func(x)).*
扩展为:
(my_func(x)).i, (func(x)).j, (func(x)).k, (func(x)).l
根据debug_print_parse，debug_print_rewritten和debug_print_plan的分析，
. (修剪后的)分析树如下所示:
in parsing, according to a look at debug_print_parse, debug_print_rewritten and debug_print_plan. The (trimmed) parse tree looks like this:
: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
...
}
)
因此，基本上，我们使用的是哑巴分析器黑客工具，通过克隆节点来扩展通配符.
So basically, we're using a dumb parser hack to expand wildcards by cloning nodes.
这篇关于如何在SQL查询中使用(func()).*语法避免使用多个函数评估?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
