我想使用 CTE 来格式化复杂的查询并返回多个表。
下面的代码：
CREATE OR REPLACE FUNCTION "public"."postgres_return_mutiple_table"(refcursor,refcursor)
RETURNS SETOF "pg_catalog"."refcursor" AS $BODY$
declare return_agents alias for $1;
return_agentpayrate alias for $2;
BEGIN
with t_agents as (
select * from agents join .... where .. /*maybe complex query*/
),
t_agentpayrate as (
select * from t_agents join .... where .. /*maybe complex query*/
)
OPEN return_agents FOR select * from t_agents
RETURN NEXT return_agents;
OPEN return_agentpayrate FOR
select * from t_agentpayrate RETURN
NEXT t_agentpayrate;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
当我运行这个函数时，它会出错：
ERROR: syntax error at or near "OPEN" LINE 12: OPEN return_agents FOR select * from t_agents; ^ , Time: 0.001000s
CREATE OR REPLACE FUNCTION public.fn_get_available_enterprise(
"shortname" varchar,
"filter" varchar,
"recordpage" int4,
"pageindex" int4,
refcursor,
refcursor
) returns setof refcursor
as
$$
declare avail_entp alias for $5;
avail_entp_count alias for $6;
begin
open avail_entp for
WITH available_enterprise AS (
SELECT distinct entinfo,ent.entname,ent.entabbr FROM get_agent_nocontian_entpaymenttype(shortname,filter,recordpage,pageindex)
JOIN wxbsenterprise ent on ent.entname = output_entname
where (("filter"!='' and (ent.entname like "filter" or entinfo like "filter" or entabbr like "filter"))
or ("filter"='' and 1=1))
limit recordpage offset (pageindex-1)*recordpage
),
enterpriserate_link_agent as(
select * from agententerprises
where entname=@entname and level=1
and (validate = true or (validate = false and enddate is not null and enddate >= current_date))
)
select * from available_enterprise;
return next avail_entp;
open avail_entp_count for
WITH available_enterprise_count as (
SELECT distinct entinfo,ent.entname,entabbr FROM get_agent_nocontian_entpaymenttype(shortname,filter,recordpage,pageindex) as epna
JOIN wxbsenterprise ent on ent.entname = output_entname
)
select count(*) from available_enterprise_count;
return next avail_entp_count;
end;
$$ language plpgsql;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句