TDSQL PostgreSQL版 控制结构

一、IF…THEN…ELSE…END IF

postgres=# CREATE OR REPLACE FUNCTION f26() RETURNS VOID ASpostgres-# $$ postgres$# BEGIN postgres$# 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.99 f26 —–(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$# BEGIN postgres$# 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.50 f26 —–(1 row)

三、CASE 语句

postgres=# CREATE OR REPLACE FUNCTION f26() RETURNS VOID ASpostgres-# $$ postgres$# DECLAREpostgres$# v_float8 float8 := random();postgres$# BEGIN postgres$# CASE postgres$# 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.50 f26 —–(1 row)

四、LOOP 循环

postgres=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID ASpostgres-# $$ postgres$# DECLAREpostgres$# v_id INTEGER := 1;postgres$# BEGIN postgres$# LOOP postgres$# 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: 2 f27 —–(1 row)

使用 EXIT 退出循环。

postgres=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID ASpostgres-# $$ postgres$# DECLAREpostgres$# v_id INTEGER := 1;postgres$# v_random float8 ;postgres$# BEGIN postgres$# LOOP postgres$# 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: 5 f27 —–(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$# BEGIN postgres$# 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: 1 f27 —–(1 row)

六、FOR 循环

postgres=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID ASpostgres-# $$ postgres$# BEGIN postgres$# 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 = 3 f27 —–(1 row)postgres=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID ASpostgres-# $$ postgres$# BEGIN postgres$# 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 = 1 f27 —–(1 row)

使用 REVERSE 递减。

postgres=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID ASpostgres-# $$ postgres$# BEGIN postgres$# 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 = 7 f27 —–(1 row)

使用 BY 设置步长。

七、FOR 循环查询结果

postgres=# CREATE OR REPLACE FUNCTION f27() RETURNS VOID ASpostgres-# $$ postgres$# DECLAREpostgres$# v_rec RECORD;postgres$# BEGIN postgres$# 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$# BEGIN postgres$# 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.975814974401146 f27 —–(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$# BEGIN postgres$# 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.415377039927989 f27 —–(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$# BEGIN postgres$# 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$# BEGIN postgres$# 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$# BEGIN postgres$# 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$# BEGIN postgres$# 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_pg f27 —–(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$# BEGIN postgres$# 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: 查询到的记录数为 1 f27 —–(1 row)postgres=# SELECT f27(3);NOTICE: 查询到的记录数为 0 f27 —–(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$# BEGIN postgres$# INSERT INTO t_exception VALUES(a_id,a_nc);postgres$# RETURN '';postgres$# EXCEPTION WHEN OTHERS THEN postgres$# 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$# DECLARE postgres$# v_sqlstate text;postgres$# v_context text;postgres$# v_message_text text;postgres$# BEGIN postgres$# INSERT INTO t_exception VALUES(a_id,a_nc);postgres$# RETURN '';postgres$# EXCEPTION WHEN OTHERS THEN postgres$# 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 statement f27 ——————————————————————————————————————————————————————————————— 错误代码 : 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)