判断语句
IF...THEN...END IF
postgres=# CREATE OR REPLACE FUNCTION f26() RETURNS VOID ASpostgres-# $$postgres$# BEGINpostgres$# IF random()>0.5 THENpostgres$# RAISE NOTICE '随机数大于0.5';postgres$# END IF;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# select f26();NOTICE: 随机数大于0.5f26-----(1 row)postgres=#
IF...THEN...ELSE...END IF
postgres=# CREATE OR REPLACE FUNCTION f26() RETURNS VOID ASpostgres-# $$postgres$# BEGINpostgres$# IF random()>0.99 THENpostgres$# RAISE NOTICE '随机数大于0.99';postgres$# ELSEpostgres$# RAISE NOTICE '随机数小于或等于0.99';postgres$# END IF;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# select f26();NOTICE: 随机数小于或等于0.99f26-----(1 row)postgres=#
IF...THEN...ELSIF...THEN...ELSE...END IF
postgres=# CREATE OR REPLACE FUNCTION f26() RETURNS VOID ASpostgres-# $$postgres$# DECLAREpostgres$# v_float8 float8 := random();postgres$# BEGINpostgres$# IF v_float8>0.99 THENpostgres$# RAISE NOTICE '随机数大于0.99';postgres$# ELSIF v_float8>0.5 THENpostgres$# RAISE NOTICE '随机数大于0.50';postgres$# ELSIF v_float8>0.25 THENpostgres$# RAISE NOTICE '随机数大于0.25';postgres$# ELSEpostgres$# RAISE NOTICE '随机数小于或等于0.25';postgres$# END IF;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f26();NOTICE: 随机数大于0.50f26-----(1 row)
CASE 语句
postgres=# CREATE OR REPLACE FUNCTION f26() RETURNS VOID ASpostgres-# $$postgres$# DECLAREpostgres$# v_float8 float8 := random();postgres$# BEGINpostgres$# CASEpostgres$# WHEN v_float8>0.99 THENpostgres$# RAISE NOTICE '随机数大于0.99';postgres$# WHEN v_float8>0.5 THENpostgres$# RAISE NOTICE '随机数大于0.50';postgres$# WHEN v_float8>0.25 THENpostgres$# RAISE NOTICE '随机数大于0.25';postgres$# ELSEpostgres$# RAISE NOTICE '随机数小于或等于0.25';postgres$# END CASE;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f26();NOTICE: 随机数大于0.50f26-----(1 row)
循环语句
LOOP 循环
postgres=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID ASpostgres-# $$postgres$# DECLAREpostgres$# v_id INTEGER := 1;postgres$# BEGINpostgres$# LOOPpostgres$# RAISE NOTICE '%',v_id;postgres$# EXIT WHEN random()>0.8;postgres$# v_id := v_id + 1;postgres$# END LOOP ;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f27();NOTICE: 1NOTICE: 2f27-----(1 row)
使用 EXIT 退出循环。
postgres=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID ASpostgres-# $$postgres$# DECLAREpostgres$# v_id INTEGER := 1;postgres$# v_random float8 ;postgres$# BEGINpostgres$# LOOPpostgres$# RAISE NOTICE '%',v_id;postgres$# v_id := v_id + 1;postgres$# v_random := random();postgres$# IF v_random > 0.8 THENpostgres$# RETURN;postgres$# END IF;postgres$# END LOOP ;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f27();NOTICE: 1NOTICE: 2NOTICE: 3NOTICE: 4NOTICE: 5f27-----(1 row)postgres=#
使用 RETURN 退出循环返回。
WHILE 循环
postgres=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID ASpostgres-# $$postgres$# DECLAREpostgres$# v_id INTEGER := 1;postgres$# v_random float8 := random() ;postgres$# BEGINpostgres$# WHILE v_random > 0.8 LOOPpostgres$# RAISE NOTICE '%',v_id;postgres$# v_id := v_id + 1;postgres$# v_random = random();postgres$# END LOOP;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f27();NOTICE: 1f27-----(1 row)
FOR 循环
postgres=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID ASpostgres-# $$postgres$# BEGINpostgres$# FOR i IN 1..3 LOOPpostgres$# RAISE NOTICE 'i = %',i;postgres$# END LOOP;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f27();NOTICE: i = 1NOTICE: i = 2NOTICE: i = 3f27-----(1 row)postgres=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID ASpostgres-# $$postgres$# BEGINpostgres$# FOR i IN REVERSE 3..1 LOOPpostgres$# RAISE NOTICE 'i = %',i;postgres$# END LOOP;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f27();NOTICE: i = 3NOTICE: i = 2NOTICE: i = 1f27-----(1 row)
使用 REVERSE 递减。
postgres=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID ASpostgres-# $$postgres$# BEGINpostgres$# FOR i IN 1..8 BY 2 LOOPpostgres$# RAISE NOTICE 'i = %',i;postgres$# END LOOP;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f27();NOTICE: i = 1NOTICE: i = 3NOTICE: i = 5NOTICE: i = 7f27-----(1 row)
使用 BY 设置步长。
FOR 循环查询结果
postgres=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID ASpostgres-# $$postgres$# DECLAREpostgres$# v_rec RECORD;postgres$# BEGINpostgres$# FOR v_rec IN SELECT * FROM public.t LOOPpostgres$# RAISE NOTICE '%',v_rec;postgres$# END LOOP;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f27();NOTICE: (1,tdsql_pg)NOTICE: (2,pgxz)f27-----(1 row)
FOREACH 循环一个数组
postgres=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID ASpostgres-# $$postgres$# DECLAREpostgres$# v_random_arr float8[]:=ARRAY[random(),random()];postgres$# v_random float8;postgres$# BEGINpostgres$# FOREACH v_random IN ARRAY v_random_arr LOOPpostgres$# RAISE NOTICE '%',v_random ;postgres$# END LOOP;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f27();NOTICE: 0.452758576720953NOTICE: 0.975814974401146f27-----(1 row)postgres=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID ASpostgres-# $$postgres$# DECLAREpostgres$# v_random_arr float8[][]:=ARRAY[ARRAY[random(),random()],ARRAY[random(),random()]];postgres$# v_random float8;postgres$# BEGINpostgres$# FOREACH v_random SLICE 0 IN ARRAY v_random_arr LOOPpostgres$# RAISE NOTICE '%',v_random ;postgres$# END LOOP;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f27();NOTICE: 0.0588191924616694NOTICE: 0.368828620761633NOTICE: 0.813376842066646NOTICE: 0.415377039927989f27-----(1 row)
循环会通过计算 expression 得到的数组的个体元素进行迭代。
postgres=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID ASpostgres-# $$postgres$# DECLAREpostgres$# v_random_arr float8[][]:=ARRAY[ARRAY[random(),random()],ARRAY[random(),random()]];postgres$# v_random float8[];postgres$# BEGINpostgres$# FOREACH v_random SLICE 1 IN ARRAY v_random_arr LOOPpostgres$# RAISE NOTICE '%',v_random ;postgres$# END LOOP;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f27();NOTICE: {0.578366641886532,0.78098024148494}NOTICE: {0.783956411294639,0.450278480071574}f27-----(1 row)
通过一个正 SLICE 值,FOREACH 通过数组的切片而不是单一元素迭代。
其它控制语句
动态执行
postgres=# CREATE OR REPLACE FUNCTION f27(a_id INTEGER) RETURNS text ASpostgres-# $$postgres$# DECLAREpostgres$# v_sql TEXT;postgres$# v_mc TEXT;postgres$# BEGINpostgres$# v_sql := 'SELECT mc FROM t WHERE id='||a_id::TEXT;postgres$# EXECUTE v_sql INTO v_mc;postgres$# RETURN v_mc;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f27(1);f27-------tdsql_pg(1 row)
动态执行就是拼 SQL 语句,然后使用 EXECUTE 命令执行。
执行一个没有结果的命令
postgres=# CREATE OR REPLACE FUNCTION f27() RETURNS void ASpostgres-# $$postgres$# BEGINpostgres$# perform f27(1);postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f27();f27-----(1 row)postgres=#
获取执行结果
postgres=# DROP FUNCTION f27(INTEGER);DROP FUNCTIONpostgres=# CREATE OR REPLACE FUNCTION f27(a_id INTEGER) RETURNS VOID ASpostgres-# $$postgres$# DECLAREpostgres$# v_mc TEXT;postgres$# BEGINpostgres$# SELECT mc INTO v_mc FROM t WHERE id=a_id;postgres$# IF FOUND THENpostgres$# RAISE NOTICE '查询到记录,值为%',v_mc;postgres$# ELSEpostgres$# RAISE NOTICE '查不到记录' ;postgres$# END IF;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f27(1);NOTICE: 查询到记录,值为tdsql_pgf27-----(1 row)postgres=# SELECT f27(3);NOTICE: 查不到记录f27-----(1 row)
获取影响行数
postgres=# CREATE OR REPLACE FUNCTION f27(a_id INTEGER) RETURNS VOID ASpostgres-# $$postgres$# DECLAREpostgres$# v_mc TEXT;postgres$# v_row_count BIGINT;postgres$# BEGINpostgres$# SELECT mc INTO v_mc FROM t WHERE id=a_id;postgres$# GET DIAGNOSTICS v_row_count = ROW_COUNT;postgres$# RAISE NOTICE '查询到的记录数为 % ',v_row_count;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f27(1);NOTICE: 查询到的记录数为 1f27-----(1 row)postgres=# SELECT f27(3);NOTICE: 查询到的记录数为 0f27-----(1 row)
俘获错误
错误俘获处理
postgres=# CREATE TABLE t_exception (id integer not null,nc text);CREATE TABLEpostgres=# create unique index t_exception_id_uidx on t_exception using btree(id);CREATE INDEXpostgres=# CREATE OR REPLACE FUNCTION f27(a_id integer,a_nc text) RETURNS TEXT ASpostgres-# $$postgres$# BEGINpostgres$# INSERT INTO t_exception VALUES(a_id,a_nc);postgres$# RETURN '';postgres$# EXCEPTION WHEN OTHERS THENpostgres$# RETURN '执行出错';postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f27(1,'tdsql_pg');f27-----(1 row)postgres=# SELECT f27(1,'tdsql_pg');f27----------执行出错(1 row)
获取错误相关信息
postgres=# CREATE OR REPLACE FUNCTION f27(a_id integer,a_nc text) RETURNS TEXT ASpostgres-# $$postgres$# DECLAREpostgres$# v_sqlstate text;postgres$# v_context text;postgres$# v_message_text text;postgres$# BEGINpostgres$# INSERT INTO t_exception VALUES(a_id,a_nc);postgres$# RETURN '';postgres$# EXCEPTION WHEN OTHERS THENpostgres$# GET STACKED DIAGNOSTICS v_sqlstate = RETURNED_SQLSTATE,postgres$# v_message_text = MESSAGE_TEXT,postgres$# v_context = PG_EXCEPTION_CONTEXT;postgres$# RAISE NOTICE '错误代码 : %',v_sqlstate;postgres$# RAISE NOTICE '出错信息 : %',v_message_text;postgres$# RAISE NOTICE '发生异常语句 : %',v_context;postgres$# RETURN '错误代码 : '||v_sqlstate || '\\n出错信息 : '||v_message_text|| '发生异常语句 : '||v_context;postgres$# END;postgres$# $$postgres-# LANGUAGE plpgsql;CREATE FUNCTIONpostgres=# SELECT f27(1,'tdsql_pg');NOTICE: 错误代码 : 23505NOTICE: 出错信息 : node:16385, error 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 f27(integer,text) line 7 at SQL statementf27---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------错误代码 : 23505\\n出错信息 : node:16385, error duplicate key value violates unique constraint "t_exception_id_uidx"发生异常语句 : SQL statement "INSERT INTO t_exception VALUES(a_id,a_nc)"+PL/pgSQL function f27(integer,text) line 7 at SQL statement(1 row)