postgresql递归查询总结

背景

由于业务需要，app_labels表存储了所有专题以及content的树结构，而且这个树结构是不确定深度的，某些场景我们面临着根据某个item来获取父、子item,这种情况显然一个简单的关联关系就能搞定，今天遇到的问题是要取某个item的所有父级，最初想法是把app_labels表里面所有数据返回前端，前端做递归处理，这样做显然不太合理，其一，数据量太庞大，其二，这个表刷新非常频繁，前端也要实时刷新数据，代价有点高……，经一番查询，发现sql也可以做递归。

app_labels表结构

jVKIfW.jpg

其中关键是label_sign以及superlabel_sign来记录树关系，测试数据如下：

CREATE TABLE testapp_db.app_labels
(
    label_id bigint NOT NULL DEFAULT nextval('testapp_db.app_labels_label_id_seq'::regclass),
    label_sign character varying(100) COLLATE pg_catalog."default" NOT NULL,
    label_fullname text COLLATE pg_catalog."default",
    superlabel_sign character varying(100) COLLATE pg_catalog."default",
    label_order integer DEFAULT 0,
    delete_flag boolean NOT NULL DEFAULT false,
    create_time timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT app_label_pkey PRIMARY KEY (label_id),
    CONSTRAINT label_order_is_unique UNIQUE (label_order, superlabel_sign),
    CONSTRAINT label_sign_is_unique UNIQUE (label_sign),
    CONSTRAINT superlabel_sign_fkey FOREIGN KEY (superlabel_sign)
        REFERENCES testapp_db.app_labels (label_sign) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

插入测试数据如下：

insert into app_labels
(label_sign, label_fullname, superlabel_sign, label_order)
VALUES
('sales', 'subject_salses', 'course', '0'),
('s-c1', 'subject_sales-category_1', 'sales', '1'), 
('s-c2', 'subject_sales-category_2', 'sales', '2'), 
('s-c3', 'subject_sales-category_3', 'sales', '3'),
('s1', 'content_pss-1', NULL, 0),
('s1-1', 'content_pss-1-chapter_1', 's1', '1'),
('s1-1.1', 'content_pss-1-chapter_1.1', 's1-1', '1'),
('s1-1.2', 'content_pss-1-chapter_1.2', 's1-1', '2'),
('s1-1.3', 'content_pss-1-chapter_1.3', 's1-1', '3'),
('s1-2', 'content_pss-1-chapter_2', 's1', '2'),
('s1-2.1', 'content_pss-1-chapter_2.1', 's1-2', '1'),
('s1-2.2', 'content_pss-1-chapter_2.2', 's1-2', '2'),
('s1-2.3', 'content_pss-1-chapter_2.3', 's1-2', '3'),
('s1-3', 'content_pss-1-chapter_3', 's1', '3'),
('s1-3.1', 'content_pss-1-chapter_3.1', 's1-3', '1'),
('s1-3.2', 'content_pss-1-chapter_3.2', 's1-3', '2'),
('s2', 'content_pss-2', NULL, 0),
('s2-1', 'content_pss-2-chapter_1', 's2', '1'),
('s2-1.1', 'content_pss-2-chapter_1.1', 's2-1', '1'),
('s2-1.2', 'content_pss-2-chapter_1.2', 's2-1', '2'),
('s2-2', 'content_pss-2-chapter_2', 's2', '2'),
('s2-2.1', 'content_pss-2-chapter_2.1', 's2-2', '1'),
('s2-2.2', 'content_pss-2-chapter_2.2', 's2-2', '2'),
('s2-2.3', 'content_pss-2-chapter_2.3', 's2-2', '3'),
('s3', 'content_pss-3', NULL, 0),
('s3-1', 'content_pss-3-chapter_1', 's3', 1),
('s3-1.1', 'content_pss-3-chapter_1.1', 's3-1', 1),
('s3-1.2', 'content_pss-3-chapter_1.2', 's3-1', 2),
('s3-1.3', 'content_pss-3-chapter_1.3', 's3-1', 3),
('s3-2', 'content_pss-3-chapter_2', 's3', 2),
('s3-2.1', 'content_pss-3-chapter_2.1', 's3-2', 1),
('s3-2.1.1', 'content_pss-3-chapter_2.1.1', 's3-2.1', 1),
('s3-2.1.2', 'content_pss-3-chapter_2.1.2', 's3-2.1', 2),
('s3-2.2', 'content_pss-3-chapter_2.2', 's3-2', 2),
('s4', 'content_pss-4', NULL, 0),
('s4-1', 'content_pss-4-chapter_1', 's4', 0),
('s4-1.1', 'content_pss-4-chapter_1.1', 's4-1', 1),
('s4-1.2', 'content_pss-4-chapter_1.2', 's4-1', 2)
;

查询item所有父标签

postgresql关于递归的官方文档

尝试编写递归sql语句

WITH RECURSIVE tmp AS ( 
       SELECT * FROM app_labels WHERE label_sign = 's3-2.1' 
     union   ALL 
       SELECT app_labels.* FROM app_labels, tmp WHERE app_labels.label_sign = tmp.superlabel_sign 
     ) 
 SELECT * FROM tmp;

返回结果如下：

LpxteR.jpg

貌似成功了，说明递归管用，下一步我们要把它转换成客户端可以访问的接口形式：

CREATE or replace FUNCTION func_get_parent1(in in_id varchar, out o_area text)  AS 

$$ 
DECLARE
   v_rec_record RECORD;
BEGIN

  o_area = '';
  FOR v_rec_record IN (WITH RECURSIVE tmp AS (SELECT *
                           FROM testapp_db.app_labels
                          WHERE label_sign = in_id
                         union ALL
                         SELECT testapp_db.app_labels.*
                           FROM testapp_db.app_labels, tmp
                          WHERE testapp_db.app_labels.label_sign = tmp.superlabel_sign)SELECT name
                         FROM tmp
                        ORDER BY id) LOOP
    o_area := o_area || '~' || v_rec_record.label_sign;
  END LOOP;
  return;
END; 
$$
LANGUAGE 'plpgsql';

查看是否执行起效：

select func_get_parent1('s3-2.1') ;

结果如下

image-20200617230654064.png

所有父节点都出来了 ，这里我只需要父节点的label即可，若有其他需要，可修改sql语句，客户端请求：

T4Q1Wo.jpg

成功啦！！

查询item所有子标签

照葫芦画瓢把递归语句稍微改下即可：

WITH RECURSIVE tmp AS ( 
       SELECT * FROM app_labels WHERE label_sign = 's3-2.1' 
     union   ALL 
       SELECT app_labels.* FROM app_labels, tmp WHERE app_labels.superlabel_sign = tmp.label_sign 
     ) 
 SELECT * FROM tmp;

自己试试去吧！