我有以下类型的几个大查询（为清楚起见而简化）。
create function myfunction()
returns void
as $$
begin
...
with t as (
total as total,
total * 100 / total as total_percent,
total / people.count as total_per_person,
part1 as part1,
part1 * 100 / total as part1_percent,
part1 / people.count as part1_per_person,
part2 as part2,
part2 * 100 / total as part2_percent,
part2 / people.count as part2_per_person,
...
from (
select
total.amount as total
part1.amount as part1
part2.amount as part2
...
people.amount as people
from (select ...from mytable..) total
left join (select ...from mytable..) part1 on ...
left join (select ...from mytable..) part2 on ...
...
left join (select ...from mytable..) people on ...
) r
)
insert into another_table -- << NOW I NEED TO REPLACE THIS WITH "RETURN QUERY"
select .., total from t
union select .., total_percent from t
union select .., total_per_person from t
union select .., part1 from t
union select .., part1_percent from t
union select .., part1_per_person from t
union select .., part2 from t
union select .., part2_percent from t
union select .., part2_per_person from t
...
...
$$ language plpgsql;
之所以这么大，是因为大多数列是从其他列派生的。查询旨在最大程度地减少提取数据和聚合过程中的重复，从而最大程度地减少了运行时间（因为该查询的mytable行数几乎不超过400万，因此运行该查询大约需要10秒）。所有15列均插入与union运算符组合的another_table中。
with .. as ..子句在这种情况下效果很好。但是现在，在重构程序时，我必须将生成的数据集交给另一个函数进行后期处理（而不是插入到another_table中）。
所以，我不得不更换insert into another_table用return query，但WITH .. AS ..不喜欢那样。
换句话说，这是我尝试达到的更新功能（该功能不起作用-解释器return query在with .. as块之后不期望）：
create function myfunction()
returns setof data -- << now returning a data set
as $$
begin
...
with t as (
--SAME QUERY
) r
)
return query -- << line that is changed
-- SAME SELECT HERE
...
$$ language plpgsql;
现在我的问题是，有什么替代品WITH .. AS ..？因此，我可以使用return query它。我计划尝试使用临时表，但是我仍然很好奇如何重写用编写的查询with .. as ...。
问题中的查询有两个明显的废话部分。既然您之前已经执行过它，那么我认为这些是人工简化的产物吗？
就像： 毫无意义，因为它会燃烧成正义。或：没有联接条件的联接，这是普通的语法错误。 total * 100 / total 100
除此之外，RETURN QUERY不是SQL而是plpgsql命令：
您可能忽略了提及您正在使用带有plpgsql代码的functionorDO语句，或者您试图对SQL使用无效的语法。
在plpgsql中，如果您RETURN QUERY 在SQL查询之前放置CTE（公用表表达式）（这是WITH子句的规范名称），它是SQL语句的一部分，那么它可以工作（排除明显的语法错误）：
RETURN QUERY -- plpgsql command
WITH t AS ( ... ) -- here starts the SQL query
SELECT .., total FROM t
UNION SELECT .., total_percent FROM t
UNION SELECT.., total_per_person FROM t
-- etc.
在讨论时，最后一部分很可能是错误的。我很确定您想要UNION ALL，而不是UNION将结果中的所有重复项折叠起来。
更好的是，结合使用此智能技术和VALUES表达式LATERAL来“反枢轴”您的长行：
...
SELECT t1.*
FROM t, LATERAL (
VALUES
(.., t.total) -- whatever you may be hiding behind ".."
, (.., t.total_percent)
, (.., t.total_per_person)
, (.., t.part1)
, (.., t.part1_percent)
-- etc.
) t1 ("name_for ..", total);
应该明显更短，更便宜。在dba.SE上的相关答案中将此想法归功于@Andriy
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句