PostgreSQL 9.xで関数の作成サンプルコード

2017年1月6日

サンプルコード

CREATE FUNCTION FUNCTION_DEAL(refcursor) RETURNS refcursor AS $$
declare r record;
ref refcursor;
ff varchar:=’SELECT “CFTAA",’;

begin
for r in select distinct “CFTBB" from ttm loop
ff:=ff || 'max(case when “CFTBB"="’||r."CFTBB" || "" || ' then “CFTCCC" ' || ' end ) as ' ||r."CFTBBB" || ' ,’;
RAISE NOTICE '123(%)’, ff;
end loop;
ff:=substr(ff,1,length(ff)-1) || ' from ttm group by “CFTAA"';
open $1 for execute ff;
return $1;
end;

$$ LANGUAGE plpgsql;