Find the maximum continuity field in PostgreSQL

I. Establishment of the table

lihao= #create table TB (ID int,pid int,name varchar);

lihao= #INSERT into TB VALUES (1, 0, ' Guangdong Province ');

lihao= #INSERT into TB VALUES (2, 0, ' Zhejiang Province ');

lihao= #INSERT into TB VALUES (3, 2, ' Quzhou ');

lihao= #INSERT into TB VALUES (4, 2, ' Hangzhou city ');

lihao= #INSERT into TB VALUES (5, 2, ' Huzhou ');

lihao= #INSERT into TB VALUES (6, 2, ' Jiaxing ');

lihao= #INSERT into TB VALUES (7, 2, ' Ningbo ');

lihao= #INSERT into TB VALUES (8, 2, ' Shaoxing ');

lihao= #INSERT into TB VALUES (9, 2, ' Taizhou ');

lihao= #INSERT into TB VALUES (10, 2, ' Wenzhou ');

lihao= #INSERT into TB VALUES (11, 2, ' Lishui ');

lihao= #INSERT into TB VALUES (12, 2, ' Jinhua ');

lihao= #INSERT into TB VALUES (13, 2, ' Zhoushan ');

lihao= #INSERT into TB VALUES (14, 4, ' Upper City ');

lihao= #INSERT into TB VALUES (15, 4, ' Lower Town ');

lihao= #INSERT into TB VALUES (16, 4, ' Gongshu District ');

lihao= #INSERT into TB VALUES (17, 4, ' Yuhang ');

lihao= #INSERT into TB VALUES (18, 11, ' Jindong District ');

lihao= #INSERT into TB VALUES (19, 1, ' Guangzhou ');

lihao= #INSERT into TB VALUES (20, 1, ' Shenzhen ');

lihao= #INSERT into TB VALUES (111, 0, ' 111 ');

lihao= #INSERT into TB VALUES (222, 1, ' 222 ');

lihao= #INSERT into TB VALUES (333, 2, ' 333 ');

lihao= #INSERT into TB VALUES (444, 4, ' 444 ');

lihao= #INSERT into TB VALUES (555, one, ' 555 ');

Ii. SQL statements

lihao=# SELECT DISTINCT (a.pid), array (select b.ID from TB b where a.pid = B.pid and exists (SELECT * from TB C where b.id = c.id + 1) Order by b.ID), from TB a order by a.pid;
PID | Array
-----+-----------------------------
0 | {2}
1 | {19,20}
2 | {3,4,5,6,7,8,9,10,11,12,13}
4 | {14,15,16,17}
11 | {18}

lihao=# SELECT DISTINCT (a.pid), array (select b.ID from TB b where a.pid = B.pid and NOT EXISTS (SELECT * from TB C where b . id = c.id + 1) Order by b.ID), from TB a order by a.pid;
PID | Array
-----+---------
0 | {1,111}
1 | {222}
2 | {333}
4 | {444}
11 | {555}

Third, Shell script

#!/bin/bash
counter= ' psql-c ' select COUNT (*) from (select DISTINCT (PID) from TB) a "| Sed-n ' 3p '
num=$[$counter + 2]
Pid= (' psql-c ' select DISTINCT (PID) from TB ORDER by pid "| sed-n ' 3, ' $num ' ' P ')
echo "==========="
echo "Pid:{id}"
echo "==========="

For PID in ${pid[*]}
Do
m= ' psql-c ' select COUNT (*) from TB where pid= $pid "| Sed-n ' 3p '
n=$[$m + 2]
Id= (' psql-c ' select ID from TB where pid= $pid the order by id ' | sed-n ' 3, ' $n ' P ')

For ((a=0;a< $m; a++))
Do
id1=${id[$a]}
id2=${id[$[$a +1]}

diff=$[$id 2-$id 1]
if [$diff! = 1];then
id_new=${id[@]/$id 1/}
Fi
Done

Echo $pid ": {" ${id_new[*]} "}"
Done

Find the maximum continuity field in PostgreSQL