在函数中使用一个查询来填充另一个查询的变量

Question

我试图从一个表dwh.sgmt_clients中提取一个# (partner_id)列表，然后将这些数字逐个插入到我的函数中变量$1所在的大型查询中。然后，我希望它们显示为一个查询。我无论如何也搞不清楚

CREATE OR REPLACE FUNCTION dwh.cu_summary_function()
 RETURNS setof record AS
$$
declare partner_id ALIAS FOR $1
BEGIN
RETURN
select a.partner_id from dwh.sgmt_clients a 
loop
RETURN query
  select
avg(klicount)::int as average_kli, min(klicount) as min_kli, max(klicount) as max_kli,
(select count(kli) from ad_delivery.sgmt_kli_adic a where a.partner_id = $1) as total_kli, 
(select count(distinct(kli))from ad_delivery.sgmt_kli_adic a where a.partner_id = $1) as total_distinct_kli, a.partner_id
from (
select adic, count(kli) as klicount, x.partner_id
from ad_delivery.sgmt_kli_adic x
where x.partner_id = $1
group by adic, x.partner_id
) as a
group by a.partner_id;
end loop
END
$$
 LANGUAGE plpgsql VOLATILE
 COST 100;

Answer 1

CREATE OR REPLACE FUNCTION dwh.cu_summary_function(int) --<<<<<<<<<
RETURNS setof record AS

此参数定义有点过时，请使用：

CREATE OR REPLACE FUNCTION dwh.cu_summary_function(partner_id int)
RETURNS setof record AS