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) );      

需要優化的:函數實作動态表名的查詢