postgres 实现查找所有的子记录,child_of

​​https://wiki.postgresql.org/wiki/Getting_list_of_all_children_from_adjacency_tree​​

首先表格得有id,parent_id 字段,

创建函数,函数返回所有子记录的集合:

CREATE OR REPLACE FUNCTION get_all_children_array(use_parent INT4) RETURNS INT4[] AS $$
DECLARE
    process_parents INT4[] := ARRAY[ use_parent ];
    children INT4[] := '{}';
    new_children INT4[];
BEGIN
    WHILE ( array_upper( process_parents, 1 ) IS NOT NULL ) LOOP
        new_children := ARRAY( SELECT id FROM [table_name] WHERE parent_id = ANY( process_parents ) AND id <> ALL( children ) );
        children := children || new_children;
        process_parents := new_children;
    END LOOP;
    RETURN children;
END;
$$ LANGUAGE plpgsql;      

查询调用

SELECT * FROM test WHERE id = any( get_all_children_array(3) );      

需要优化的:函数实现动态表名的查询