PostgreSQL CTE记录作为功能的参数 [英] PostgreSQL CTE records as parameters to function

问题描述

我有一个接受两个整数作为参数 my_function（input_a，input_b）的函数。是否有一种简单的方法可以将CTE的结果（返回 input_a，input_b 的记录）传递给函数？

I have a function that accepts two integers as parameters my_function(input_a, input_b). Is there an easy way to pass the results of a CTE (that returns records of input_a, input_b) into the function?

我应该是想用for循环编写自定义函数还是有更好的方法？

Should I be looking into writing a custom function with a for loop or is there a better approach?

推荐答案

如果函数返回单个记录，则：

If the function returns a single record then:

WITH cte AS (SELECT 1 a, 2 b)
SELECT my_function(a, b) FROM cte;

可以使用。但是，如果函数是SRF（Set-Returning-Function），则需要使用LATERAL，以使数据库知道您想要将JOIN语句中先前表的结果提供给以后的函数。加入。可以这样完成：

will work. However, if the function is an SRF (Set-Returning-Function), then you need to use LATERAL, to let the database know that you want to feed the results of the prior tables in the JOIN statement to the functions later on in the JOIN. This is accomplished like so:

WITH cte AS (SELECT 1 a, 2 b)
SELECT * FROM cte, LATERAL my_function(a, b);

LATERAL将使PostgreSQL从CTE中获取每一行，并使用以下值运行 my_function该行，将该函数的结果返回到整个SELECT语句。

The LATERAL will cause PostgreSQL to take each row from the CTE and run "my_function" with the values from that row, returning the results of that function to the overall SELECT statement.

这篇关于PostgreSQL CTE记录作为功能的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！