关于PostgreSQL中自定义sql函数语法

在项目中需要用到一个sql递归操作，而且此sql片段在持久层使用较多，所以干脆直接把其sql功能封装成sql函数：

CREATE OR REPLACE FUNCTION "public"."get_sub_org"("p_class_id" text, "p_dept_id" text)
  RETURNS TABLE("the_dept_id" varchar) AS $BODY$

  BEGIN
  -- Routine body goes here...
 
   
  /*获取机构下所有的子机构ID*/
  IF (p_dept_id is null or p_dept_id = '') THEN
  
    RETURN QUERY
      SELECT department_id FROM jc_rankdiagram WHERE class_id = p_class_id group by department_id;
 
  ELSE
  RETURN QUERY
    WITH RECURSIVE r AS
    (
    SELECT * FROM jc_rankdiagram WHERE class_id = p_class_id AND department_id = p_dept_id
    UNION ALL
    SELECT jc_rankdiagram.* FROM jc_rankdiagram, r WHERE jc_rankdiagram.parent_id = r.department_id
    )

    SELECT r.department_id FROM r group by department_id;

  END IF;
      
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000