CREATE FUNCTION remove_empty_wbs (_rev_id bigint) RETURNS SETOF BIGINT
    LANGUAGE plpgsql
AS $$
DECLARE
    _cnt INT;
    _proj_id INT;
    _project_cur RECORD;
BEGIN
    FOR _project_cur IN SELECT proj_id FROM admuser.project WHERE rev_id = _rev_id
    LOOP
      _proj_id := _project_cur.proj_id;
      LOOP
        WITH RECURSIVE temp1 ( proj_id, WBS_id, PARENT_WBS_id, wbs_short_name, PATH, LEVEL ) AS (
          SELECT    T1.proj_id
            , T1.WBS_id
            , T1.PARENT_WBS_id
            , T1.wbs_short_name
            , CAST (T1.wbs_short_name AS VARCHAR (1024)) as PATH
            , 1
          FROM admuser.projwbs T1 WHERE T1.proj_node_flag = 'Y' AND T1.proj_id = _proj_id AND T1.rev_id=_rev_id
          UNION
          SELECT    T2.proj_id
            , T2.WBS_id
            , T2.PARENT_WBS_id
            , T2.wbs_short_name
            , CAST ( temp1.PATH ||'->'|| T2.wbs_short_name AS VARCHAR(1024))
            , LEVEL + 1
          FROM admuser.projwbs T2 INNER JOIN temp1 ON( temp1.WBS_id = T2.PARENT_WBS_id AND T2.proj_id = _proj_id AND T2.rev_id=_rev_id)
        ), temp2 AS (
            SELECT temp1.*, relcnt.cnt
              FROM temp1
              LEFT JOIN (
                SELECT
                  wbs_id,
                  count(*) AS cnt
                FROM admuser.task  WHERE proj_id = _proj_id AND rev_id=_rev_id
                GROUP BY wbs_id) relcnt ON temp1.wbs_id = relcnt.wbs_id
        ), temp3 AS (
          DELETE FROM admuser.projwbs WHERE proj_id = _proj_id AND rev_id=_rev_id AND  wbs_id IN (
           SELECT a1.wbs_id  FROM temp2 a1 LEFT JOIN temp2 a2 on a1.wbs_id=a2.parent_wbs_id
            WHERE a2.wbs_id is NULL and a1.cnt is null
          ) RETURNING 1
        )
        SELECT count(*) INTO _cnt FROM temp3;

      EXIT WHEN _cnt IS NULL OR _cnt = 0;
      END LOOP;
   END LOOP;
END;
$$