postgresql存储过程练习题,查找丢失的id

/* 
找出表 seq_tbl 中缺少的seq
seq
1
2
3
5
6
8


*/

DO $$
DECLARE 
res integer ;
missing_seq  int[];
BEGIN
    SELECT max(seq) into res from seq_tbl;
		LOOP
			IF (NOT EXISTS(SELECT * from seq_tbl where seq=res))
			then 
			  SELECT  ARRAY_APPEND(missing_seq, res) into missing_seq ;
			END IF;
			
			res:=res-1;
			IF res=0 THEN
				EXIT; 
			END IF; 
		END LOOP;

    RAISE NOTICE '缺少 %!', missing_seq;		

END $$;
-- 执行结果: NOTICE:  缺少 {7,4}!

使用到了loop循环,谓词判断,数组定义,数组赋值

DO $$
DECLARE 
res integer ;
missing_seq  int[];
BEGIN
		EXECUTE 'SELECT max(seq) from seq_tbl' into res;
		LOOP
			PERFORM  1 from seq_tbl where seq=res;
			IF NOT FOUND THEN 
			  SELECT  ARRAY_APPEND(missing_seq, res) into missing_seq ;
			END IF;
			
			res:=res-1;
			IF res=0 THEN
				EXIT; 
			END IF; 
		END LOOP;

    RAISE NOTICE '缺少 %!', missing_seq;		

END $$;
-- 执行结果: NOTICE:  缺少 {7,4}!

使用到了EXECUTE动态执行SQL, PERFORM关键字