postgresql 存储过程demo

传递数组，返回值

CREATE OR REPLACE FUNCTION test(tt INTEGER[])
  returns integer as $$
	declare
	sqll text;
	 cou INTEGER;
BEGIN
sqll:='SELECT count(*) from (select UNNEST($1)) v';
   execute  sqll into cou using tt;
	 return cou;
END
$$
  LANGUAGE plpgsql

传递数组，返回table

CREATE OR REPLACE FUNCTION "public"."getvmcount"("datacenterid" _int4)
  RETURNS TABLE("Team" int4, "PoweredOff VM" int8, "PoweredOn VM" int8, "Suspended VM" int8, "llvm" int8, "zombie" int8) AS $$
	declare
	sqll text;
	testvm text;
	off text;
	onn text;
	Suspended text;
	mon text;
	da text;
	 cou INTEGER;
BEGIN

  off:='Off';
	onn:='On';
	Suspended:='Suspended';
	mon:='1 month';
	da:='60 days';
	sqll:='SELECT count(*) from (select UNNEST($1)) v';
   execute  sqll into cou using datacenterid;
	 if cou=4 then
	 testvm:='select
	vi.datacenter_id as Team,
	COUNT(CASE WHEN vi.state=$2 THEN 1 ELSE NULL END) as "PoweredOff VM",
	COUNT(CASE WHEN vi.state=$3 THEN 1 ELSE NULL END) as "PoweredOn VM",
	COUNT(CASE WHEN vi.state=$4 THEN 1 ELSE NULL END) as "Suspended VM",
	COUNT(CASE WHEN vi.up_time>$5::interval THEN 1 ELSE NULL END) as "llvm",
	COUNT(CASE WHEN vi.off_interval>$6::interval THEN 1 ELSE NULL END) as "zombie"
from dashboard_vm_instance_withteam vi
where vi.datacenter_id in (select * from unnest($1))
group by datacenter_id;';
	else
	testvm:='select
	team as Team,
	COUNT(CASE WHEN vi.state=$2 THEN 1 ELSE NULL END) as "PoweredOff VM",
	COUNT(CASE WHEN vi.state=$3 THEN 1 ELSE NULL END) as "PoweredOn VM",
	COUNT(CASE WHEN vi.state=$4 THEN 1 ELSE NULL END) as "Suspended VM",
	COUNT(CASE WHEN vi.up_time>$5::interval THEN 1 ELSE NULL END) as "llvm",
	COUNT(CASE WHEN vi.off_interval>$6::interval THEN 1 ELSE NULL END) as "zombie"
from dashboard_vm_instance_withteam vi
where vi.datacenter_id in (select * from unnest($1))
group by datacenter_id ,Team;';
end if;

   RETURN QUERY execute
testvm using datacenterid,off,onn,Suspended,mon,da;
END
$$
  LANGUAGE plpgsql VOLATILE

参考资料
参考资料