在低版本的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
