背景
GOTO语句是一个无条件的跳转语句,可以将程序的执行流程跳转到某个标签指定位置。 标签必须在其执行范围内是唯一的。在LightDB-A Database 23.2版本中PL/pgSQL对GOTO语句进行了支持。
场景
PL/pgSQL中匿名块、FUNCTION、PROCEDURE中支持GOTO语句.
但是GOTO的使用存在如下限制
- 指定跳转位置的标签必须定义于一个可以执行的语句或PL/pgSQL块之前。
- 可以从嵌套块跳转到外部块,但不能从外层块跳转到嵌套块。
- 不能从IF语句外部跳到IF语句内部。
- 不能从循环体外跳到循环体内。
- 不能从子程序外部跳到子程序内部。
- 不能从CASE语句外跳转到CASE语句内。
- 不能从异常块跳转到正常块,也不能从正常块跳转到异常块。
案例演示
以下案例使用psql命令可以执行运行。
嵌套BLOCK
goto不支持从外部block GOTO到内部block,如下案例:
DO $$
BEGIN
RAISE NOTICE 'out block 1';
goto testlabel;
RAISE NOTICE 'out block 2';
BEGIN
RAISE NOTICE 'in block 1';
<<testlabel>>
RAISE NOTICE 'in block 2';
END;
RAISE NOTICE 'out block 3';
END$$;
NOTICE: out block 1
ERROR: illegal GOTO statement; this GOTO cannot branch to label "testlabel"
CONTEXT: PL/pgSQL function inline_code_block
goto支持从内部block GOTO到外部block,如下案例:
DO $$
BEGIN
RAISE NOTICE 'out block 1';
goto testlabel;
RAISE NOTICE 'out block 2';
<<testlabel>>
BEGIN
RAISE NOTICE 'in block 1';
goto testlabel2;
RAISE NOTICE 'in block 2';
END;
RAISE NOTICE 'out block 3';
<<testlabel2>>
RAISE NOTICE 'out block 4';
END$$;
NOTICE: out block 1
NOTICE: in block 1
NOTICE: out block 4
LOOP
goto不支持从外部GOTO到LOOP内部,如下案例:
DO
$$
BEGIN
RAISE NOTICE 'OUT LOOP 1';
goto testlabel;
LOOP
RAISE NOTICE 'IN LOOP 1';
<<testlabel>>
RAISE NOTICE 'IN LOOP 2';
END LOOP;
RAISE NOTICE 'OUT LOOP 2';
END$$;
NOTICE: OUT LOOP 1
ERROR: illegal GOTO statement; this GOTO cannot branch to label "testlabel"
CONTEXT: PL/pgSQL function inline_code_block
goto支持从LOOP内部GOTO到外部,如下案例:
DO
$$
BEGIN
LOOP
RAISE NOTICE 'IN LOOP 1';
goto testlabel;
RAISE NOTICE 'IN LOOP 2';
END LOOP;
<<testlabel>>
RAISE NOTICE 'OUT LOOP 1';
END$$;
NOTICE: IN LOOP 1
NOTICE: OUT LOOP 1
WHILE
goto不支持从外部GOTO到WHILE内部,如下案例:
DO
$$
BEGIN
RAISE NOTICE 'OUT LOOP 1';
goto testlabel;
WHILE true LOOP
RAISE NOTICE 'IN LOOP 1';
<<testlabel>>
RAISE NOTICE 'IN LOOP 2';
END LOOP;
RAISE NOTICE 'OUT LOOP 2';
END$$;
NOTICE: OUT LOOP 1
ERROR: illegal GOTO statement; this GOTO cannot branch to label "testlabel"
CONTEXT: PL/pgSQL function inline_code_block
goto支持从WHILE内部GOTO到外部,如下案例:
DO
$$
BEGIN
RAISE NOTICE 'OUT LOOP 1';
WHILE true LOOP
RAISE NOTICE 'IN LOOP 1';
goto testlabel;
RAISE NOTICE 'IN LOOP 2';
END LOOP;
<<testlabel>>
RAISE NOTICE 'OUT LOOP 2';
END$$;
NOTICE: OUT LOOP 1
NOTICE: IN LOOP 1
NOTICE: OUT LOOP 2
FOR
goto不支持从外部block GOTO到FOR内部,如下案例:
DO
$$
DECLARE
i INTEGER;
BEGIN
RAISE NOTICE 'OUT LOOP 1';
goto testlabel;
FOR i IN 1..10 LOOP
RAISE NOTICE 'IN LOOP 1';
<<testlabel>>
RAISE NOTICE 'IN LOOP 2';
END LOOP;
RAISE NOTICE 'OUT LOOP 2';
END$$;
NOTICE: OUT LOOP 1
ERROR: illegal GOTO statement; this GOTO cannot branch to label "testlabel"
CONTEXT: PL/pgSQL function inline_code_block
goto支持从内部FOR GOTO到外部block,如下案例:
DO
$$
DECLARE
i INTEGER;
BEGIN
RAISE NOTICE 'OUT LOOP 1';
FOR i IN 1..10 LOOP
RAISE NOTICE 'IN LOOP 1';
goto testlabel;
RAISE NOTICE 'IN LOOP 2';
END LOOP;
<<testlabel>>
RAISE NOTICE 'OUT LOOP 2';
END$$;
NOTICE: OUT LOOP 1
NOTICE: IN LOOP 1
NOTICE: OUT LOOP 2
FOREACH
goto不支持从外部block GOTO到FOREACH内部,如下案例:
DO
$$
DECLARE
i INTEGER[];
x INTEGER;
BEGIN
i := ARRAY[1,2,3,4];
RAISE NOTICE 'OUT LOOP 1';
goto testlabel;
FOREACH x IN ARRAY i LOOP
RAISE NOTICE 'IN LOOP 1';
<<testlabel>>
RAISE NOTICE 'IN LOOP 2';
END LOOP;
RAISE NOTICE 'OUT LOOP 2';
END$$;
NOTICE: OUT LOOP 1
ERROR: illegal GOTO statement; this GOTO cannot branch to label "testlabel"
CONTEXT: PL/pgSQL function inline_code_block
goto支持从内部FOREACH GOTO到外部block,如下案例:
DO
$$
DECLARE
i INTEGER[];
x INTEGER;
BEGIN
i := ARRAY[1,2,3,4];
RAISE NOTICE 'OUT LOOP 1';
FOREACH x IN ARRAY i LOOP
RAISE NOTICE 'IN LOOP 1';
goto testlabel;
RAISE NOTICE 'IN LOOP 2';
END LOOP;
<<testlabel>>
RAISE NOTICE 'OUT LOOP 2';
END$$;
NOTICE: OUT LOOP 1
NOTICE: IN LOOP 1
NOTICE: OUT LOOP 2
COMMIT
goto支持GOTO到COMMIT,如下案例:
CREATE TABLE IF NOT EXISTS test1(a int) DISTRIBUTED BY(a);
DO $$
BEGIN
INSERT INTO test1 (a) VALUES (1);
INSERT INTO test1 (a) VALUES (2);
goto testlabel;
INSERT INTO test1 (a) VALUES (3);
<<testlabel>>
COMMIT;
END$$;
select * from test1 order by a asc;
a
---
1
2
(2 rows)
DROP TABLE test1;
ROLLBACK
goto支持GOTO到rollback,如下案例:
CREATE TABLE IF NOT EXISTS test1(a int) DISTRIBUTED BY(a);
DO $$
BEGIN
INSERT INTO test1 (a) VALUES (1);
INSERT INTO test1 (a) VALUES (2);
goto testlabel;
INSERT INTO test1 (a) VALUES (3);
<<testlabel>>
ROLLBACK;
END$$;
select * from test1 order by a asc;
a
---
(0 rows)
DROP TABLE test1;
IF
goto不支持从外部block GOTO到IF内部block,如下案例:
DO $$
BEGIN
RAISE NOTICE 'out block 1';
goto testlabel;
RAISE NOTICE 'out block 2';
IF true THEN
RAISE NOTICE 'in block 1';
<<testlabel>>
RAISE NOTICE 'in block 2';
END IF;
RAISE NOTICE 'out block 3';
END$$;
NOTICE: out block 1
ERROR: illegal GOTO statement; this GOTO cannot branch to label "testlabel"
CONTEXT: PL/pgSQL function inline_code_block
goto支持从IF内部block GOTO到外部block,如下案例:
DO $$
BEGIN
RAISE NOTICE 'out block 1';
goto testlabel;
RAISE NOTICE 'out block 2';
<<testlabel>>
IF true THEN
RAISE NOTICE 'in block 1';
goto testlabel2;
RAISE NOTICE 'in block 2';
END IF;
RAISE NOTICE 'out block 3';
<<testlabel2>>
RAISE NOTICE 'out block 4';
END$$;
NOTICE: out block 1
NOTICE: in block 1
NOTICE: out block 4
CASE
goto不支持从外部block GOTO到CASE内部block,如下案例:
DO $$
BEGIN
RAISE NOTICE 'out block 1';
goto testlabel;
RAISE NOTICE 'out block 2';
CASE 2
WHEN 1 THEN
<<testlabel>>
RAISE NOTICE 'in block 1';
ELSE
RAISE NOTICE 'in block 2';
END CASE;
RAISE NOTICE 'out block 3';
END$$;
NOTICE: out block 1
ERROR: illegal GOTO statement; this GOTO cannot branch to label "testlabel"
CONTEXT: PL/pgSQL function inline_code_block
goto支持从CASE内部block GOTO到外部block,如下案例:
DO $$
BEGIN
RAISE NOTICE 'out block 1';
goto testlabel;
RAISE NOTICE 'out block 2';
<<testlabel>>
CASE 2
WHEN 1 THEN
RAISE NOTICE 'in block 1';
ELSE
RAISE NOTICE 'in block 2';
goto testlabel2;
RAISE NOTICE 'in block 3';
END CASE;
RAISE NOTICE 'out block 3';
<<testlabel2>>
RAISE NOTICE 'out block 4';
END$$;
NOTICE: out block 1
NOTICE: in block 2
NOTICE: out block 4
EXIT
goto支持GOTO到EXIT,如下案例:
DO $$
BEGIN
RAISE NOTICE 'out block 1';
LOOP
goto testlabel;
RAISE NOTICE 'out block 2';
<<testlabel>>
EXIT;
END LOOP;
RAISE NOTICE 'out block 3';
END$$;
NOTICE: out block 1
NOTICE: out block 3
RETURN
goto支持GOTO到RETURN,如下案例:
DO $$
BEGIN
RAISE NOTICE 'out block 1';
LOOP
goto testlabel;
RAISE NOTICE 'out block 2';
<<testlabel>>
RETURN;
END LOOP;
RAISE NOTICE 'out block 3';
END$$;
NOTICE: out block 1
GOTO
goto支持GOTO到GOTO,如下案例:
DO $$
BEGIN
goto testlabel;
RAISE NOTICE 'out block 1';
<<testlabel>>
goto testlabel3;
<<testlabel2>>
RAISE NOTICE 'out block 2';
<<testlabel3>>
RAISE NOTICE 'out block 3';
<<testlabel4>>
RAISE NOTICE 'out block 4';
END$$;
NOTICE: out block 3
NOTICE: out block 4
EXCEPTION
goto不支持从正常块与异常块相互GOTO,如下案例:
不支持从异常块GOTO到正常块。
DO $$
DECLARE
i INTEGER ;
BEGIN
RAISE NOTICE 'block 1';
i := 1/0;
<<testlabel>>
RAISE NOTICE 'block 2';
EXCEPTION
WHEN DATA_EXCEPTION THEN
RAISE NOTICE 'exception block 1';
goto testlabel;
RAISE NOTICE 'exception block 2';
END$$;
NOTICE: block 1
NOTICE: exception block 1
ERROR: illegal GOTO statement; this GOTO cannot branch to label "testlabel"
CONTEXT: PL/pgSQL function inline_code_block
也不支持从正常块GOTO到异常块
DO $$
DECLARE
i INTEGER ;
BEGIN
raise notice '1';
goto testlabel;
EXCEPTION
WHEN division_by_zero THEN
<<testlabel>>
RAISE NOTICE '2';
return ;
END;
$$ LANGUAGE plpgsql;
NOTICE: 1
ERROR: illegal GOTO statement; this GOTO cannot branch to label "testlabel"
CONTEXT: PL/pgSQL function inline_code_block
NULL
支持GOTO到NULL,如下示例:
DO $$
BEGIN
RAISE NOTICE 'STEP 1';
GOTO label;
RAISE NOTICE 'STEP 2';
<<label>>
NULL;
RAISE NOTICE 'STEP 3';
END$$;
NOTICE: STEP 1
NOTICE: STEP 3
DO
结论
上面示例中演示了匿名块中的GOTO,plpgsql函数(FUNCTION)与plpgsql存储过程(PROCEDURE)也同样适用,值得注意的是,plpgsql 23.2版本FUNCTION中还不支持事务,所以不能在FUNCTION中GOTO到COMMMIT/ROLLBACK。