判断语句
IF...THEN...END IF
postgres=# CREATE OR REPLACE PROCEDURE p_if() AS$$BEGINIF random()>0.5 THENRAISE NOTICE '随机数大于0.5';END IF;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_if();NOTICE: 随机数大于0.5CALLpostgres=#
IF...THEN...ELSE...END IF
postgres=# CREATE OR REPLACE PROCEDURE p_if_else() AS$$BEGINIF random()>0.99 THENRAISE NOTICE '随机数大于0.99';ELSERAISE NOTICE '随机数小于或等于0.99';END IF;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_if_else();NOTICE: 随机数小于或等于0.99CALLpostgres=#
IF...THEN...ELSIF...THEN...ELSE...END IF
postgres=# CREATE OR REPLACE PROCEDURE p_if_elsif() AS$$DECLAREv_float8 float8 := random();BEGINIF v_float8>0.99 THENRAISE NOTICE '随机数大于0.99';ELSIF v_float8>0.5 THENRAISE NOTICE '随机数大于0.50';ELSIF v_float8>0.25 THENRAISE NOTICE '随机数大于0.25';ELSERAISE NOTICE '随机数小于或等于0.25';END IF;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_if_elsif();NOTICE: 随机数大于0.50CALL
CASE 语句
postgres=# CREATE OR REPLACE PROCEDURE p_case() AS$$DECLAREv_float8 float8 := random();BEGINCASEWHEN v_float8>0.99 THENRAISE NOTICE '随机数大于0.99';WHEN v_float8>0.5 THENRAISE NOTICE '随机数大于0.50';WHEN v_float8>0.25 THENRAISE NOTICE '随机数大于0.25';ELSERAISE NOTICE '随机数小于或等于0.25';END CASE;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_case();NOTICE: 随机数小于或等于0.25CALLpostgres=#
循环语句
LOOP 循环
postgres=# CREATE OR REPLACE PROCEDURE p_loop() AS$$DECLAREv_id INTEGER := 1;BEGINLOOPRAISE NOTICE '%',v_id;EXIT WHEN random()>0.8;v_id := v_id + 1;END LOOP ;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_loop();NOTICE: 1NOTICE: 2NOTICE: 3CALLpostgres=#
WHILE 循环
postgres=# CREATE OR REPLACE PROCEDURE p_while() AS$$DECLAREv_id INTEGER := 1;v_random float8 ;BEGINLOOPRAISE NOTICE '%',v_id;v_id := v_id + 1;v_random := random();IF v_random > 0.8 THENRETURN;END IF;END LOOP ;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_while();NOTICE: 1CALL
FOR 循环
postgres=# CREATE OR REPLACE PROCEDURE p_for() AS$$BEGINFOR i IN 1..3 LOOPRAISE NOTICE 'i = %',i;END LOOP;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_for();NOTICE: i = 1NOTICE: i = 2NOTICE: i = 3CALLpostgres=# CREATE OR REPLACE PROCEDURE p_for_reverse() AS$$BEGINFOR i IN REVERSE 3..1 LOOPRAISE NOTICE 'i = %',i;END LOOP;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_for_reverse();NOTICE: i = 3NOTICE: i = 2NOTICE: i = 1CALL
使用 REVERSE 递减。
postgres=# CREATE OR REPLACE PROCEDURE p_for_by() AS$$BEGINFOR i IN 1..8 BY 2 LOOPRAISE NOTICE 'i = %',i;END LOOP;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_for_by();NOTICE: i = 1NOTICE: i = 3NOTICE: i = 5NOTICE: i = 7CALLpostgres=#
使用 BY 设置步长。
FOR 循环查询结果
postgres=# CREATE OR REPLACE PROCEDURE p_for_record() AS$$DECLAREv_rec RECORD;BEGINFOR v_rec IN SELECT relname,relkind FROM pg_class limit 2 LOOPRAISE NOTICE '%',v_rec;END LOOP;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_for_record();NOTICE: (pg_stat_statements,v)NOTICE: (pg_proc,v)CALLpostgres=#
FOREACH 循环一个数组
postgres=# CREATE OR REPLACE PROCEDURE p_foreach() AS$$DECLAREv_random_arr float8[]:=ARRAY[random(),random()];v_random float8;BEGINFOREACH v_random IN ARRAY v_random_arr LOOPRAISE NOTICE '%',v_random ;END LOOP;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_foreach();NOTICE: 0.744417542591691NOTICE: 0.804096563253552CALLpostgres=#postgres=# CREATE OR REPLACE PROCEDURE p_foreach_slice() AS$$DECLAREv_random_arr float8[][]:=ARRAY[ARRAY[random(),random()],ARRAY[random(),random()]];v_random float8;BEGINFOREACH v_random SLICE 0 IN ARRAY v_random_arr LOOPRAISE NOTICE '%',v_random ;END LOOP;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_foreach_slice();NOTICE: 0.0220407997258008NOTICE: 0.898449067492038NOTICE: 0.190678883343935NOTICE: 0.103653562255204CALLpostgres=#
循环会通过计算 expression 得到的数组的个体元素进行迭代。
postgres=# CREATE OR REPLACE PROCEDURE p_foreach_slice_1() AS$$DECLAREv_random_arr float8[][]:=ARRAY[ARRAY[random(),random()],ARRAY[random(),random()]];v_random float8[];BEGINFOREACH v_random SLICE 1 IN ARRAY v_random_arr LOOPRAISE NOTICE '%',v_random ;END LOOP;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_foreach_slice_1();NOTICE: {0.248282201588154,0.757913041394204}NOTICE: {0.0194511725567281,0.43799454299733}CALL
通过一个正 SLICE 值,FOREACH 通过数组的切片而不是单一元素迭代。
其它控制语句
动态执行
postgres=# CREATE OR REPLACE PROCEDURE p_execute() AS$$DECLAREv_sql TEXT;v_relname TEXT;BEGINv_sql := 'SELECT relname FROM pg_class limit 1';EXECUTE v_sql INTO v_relname;RAISE NOTICE 'relname = %',v_relname;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_execute();NOTICE: relname = pg_stat_statementsCALLpostgres=#
也可以使用 immediate。
postgres=# CREATE OR REPLACE PROCEDURE p_execute() AS$$DECLAREv_sql TEXT;v_relname TEXT;BEGINv_sql := 'SELECT relname FROM pg_class limit 1';EXECUTE immediate v_sql INTO v_relname;RAISE NOTICE 'relname = %',v_relname;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_execute();NOTICE: relname = s1CALLpostgres=#
动态执行就是拼 SQL 语句,然后使用 EXECUTE 命令执行。
执行一个没有结果的命令
postgres=# CREATE OR REPLACE PROCEDURE p_perform() AS$$BEGINperform md5(random()::text);END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# call p_perform();CALLpostgres=#
获取执行结果
postgres=# CREATE OR REPLACE PROCEDURE p_found() AS$$DECLAREv_relname TEXT;BEGINSELECT relname INTO v_relname FROM pg_class limit 1;IF FOUND THENRAISE NOTICE '查询到记录,值为%',v_relname;ELSERAISE NOTICE '查不到记录' ;END IF;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_found();NOTICE: 查询到记录,值为pg_stat_statementsCALL
获取影响行数
postgres=# CREATE OR REPLACE PROCEDURE p_row_count() AS$$DECLAREv_row_count BIGINT;BEGINdelete from t1;GET DIAGNOSTICS v_row_count = ROW_COUNT;RAISE NOTICE '查询到的记录数为 % ',v_row_count;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# call p_row_count();NOTICE: 查询到的记录数为 3CALLpostgres=#
GOTO
postgres=# create or replace procedure p_goto(v_maxnum integer) as$$declaremaxnum integer;beginmaxnum := v_maxnum;for i in 1..maxnum loopif i=3 thengoto label;end if;raise notice 'i=%',i;end loop;<<label>>raise notice 'goto end';end;$$language plpgsql;CREATE PROCEDUREpostgres=# call p_goto(5);NOTICE: i=1NOTICE: i=2NOTICE: goto endCALLpostgres=#
go 用于跳转到某个标签下。
俘获错误
错误俘获处理
postgres=# CREATE OR REPLACE PROCEDURE p_exception(a_id integer,a_nc text) AS$$BEGININSERT INTO t_exception VALUES(a_id,a_nc);RETURN ;EXCEPTION WHEN OTHERS THENRAISE NOTICE '执行出错';END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=#postgres=# CALL p_exception(1,'Tdsql_pg');CALLpostgres=# CALL p_exception(1,'Tdsql_pg');NOTICE: 执行出错CALL
获取错误相关信息
postgres=# CREATE OR REPLACE PROCEDURE p_exception_error(a_id integer,a_nc text) AS$$DECLAREv_sqlstate text;v_context text;v_message_text text;BEGININSERT INTO t_exception VALUES(a_id,a_nc);RETURN ;EXCEPTION WHEN OTHERS THENGET STACKED DIAGNOSTICS v_sqlstate = RETURNED_SQLSTATE,v_message_text = MESSAGE_TEXT,v_context = PG_EXCEPTION_CONTEXT;RAISE NOTICE '错误代码 : %',v_sqlstate;RAISE NOTICE '出错信息 : %',v_message_text;RAISE NOTICE '发生异常语句 : %',v_context;raise notice '错误代码 : % \\n出错信息 : % 发生异常语句 : %',v_sqlstate ,v_message_text,v_context;END;$$LANGUAGE plpgsql;CREATE PROCEDUREpostgres=# CALL p_exception_error(2,'Tdsql_pg');CALLpostgres=# CALL p_exception_error(2,'Tdsql_pg');NOTICE: 错误代码 : 23505NOTICE: 出错信息 : node:dn001, backend_pid:16204, nodename:dn001,backend_pid:16204,message:duplicate key value violates unique constraint "t_exception_id_uidx"NOTICE: 发生异常语句 : SQL statement "INSERT INTO t_exception VALUES(a_id,a_nc)"PL/pgSQL function p_exception_error(integer,text) line 7 at SQL statementNOTICE: 错误代码 : 23505 \\n出错信息 : node:dn001, backend_pid:16204, nodename:dn001,backend_pid:16204,message:duplicate key value violates unique constraint "t_exception_id_uidx" 发生异常语句 : SQL statement "INSERT INTO t_exception VALUES(a_id,a_nc)"PL/pgSQL function p_exception_error(integer,text) line 7 at SQL statementCALLpostgres=#