CREATE OR REPLACE FUNCTION getCostAndVm (datacenterid INTEGER)
RETURNS TABLE (
t1 INT4,
PoweredOffVM BIGINT,
PoweredOnVM BIGINT,
Svm BIGINT,
llvm BIGINT,
zvm BIGINT
)
AS $$
BEGIN
RETURN QUERY
select
team as t1,
COUNT(CASE WHEN vi.state = ‘Off’ THEN 1 ELSE NULL END) as “PoweredOffVM”,
COUNT(CASE WHEN vi.state = ‘On’ THEN 1 ELSE NULL END) as “PoweredOnVM”,
COUNT(CASE WHEN vi.state = ‘Suspended’ THEN 1 ELSE NULL END) as “Svm”,
COUNT(CASE WHEN vi.up_time > ‘1 month’::interval THEN 1 ELSE NULL END) as “llvm”,
COUNT(CASE WHEN vi.off_interval > ‘60 days’::interval THEN 1 ELSE NULL END) as “zvm”
from dashboard_vm_instance_withteam vi
where vi.datacenter_id=datacenterid
group by datacenter_id ,t1;
END
$$ LANGUAGE ‘plpgsql’;

返回结果集的是函数，不反回结果集的是存储过程