greenplum 递归函数

在低版本的greenplum中不支持递归.所以可以写一个函数来支持

CREATE OR REPLACE FUNCTION with_recursive(
    id bigint[],
    table_name character varying,
    id_name character varying,
    pid_name character varying)
  RETURNS bigint[] AS
$BODY$
declare
        vsql text;
        ids int8[];
begin
        if(id is null) then return null; end if;

        vsql ='select array_agg('|| id_name ||') from ' || table_name ||
                                ' where 1=1 and ' || pid_name || '=any(array['|| array_to_string(id,',') || '])';
        --raise notice 'vsql:%',vsql;

        if(vsql is not null) then execute vsql into ids;end if;

        vsql ='select subids from (select array_cat(array['|| array_to_string(id,',') ||
                                ']::int8[],with_recursive(array['|| array_to_string(ids,',') ||
                                '],'''|| table_name ||''','''||id_name||''','''||pid_name||''')) subids) t';
        --raise notice 'vsql:%',vsql;

        if(vsql is not null) then execute vsql into ids;end if;

        return coalesce(ids,id);

end $BODY$
  LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION with_recursive(bigint[], character varying, character varying, character varying)
  OWNER TO gpadmin;
COMMENT ON FUNCTION with_recursive(bigint[], character varying, character varying, character varying) IS '递归函数';

使用

select unnest(with_recursive(array[4],'chains','cid','pid')) subchains;

转载: http://udn.yyuap.com/thread-114853-1-1.html