with function 语法支持

通过with子句，我们可以把很多原本需要存储过程来实现的复杂逻辑用一句SQL来进行表达。KingbaseES 从V008R006C004B0021 版本开始，支持 with function 语法。例子如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

with   function f_sum(i_relnamespace oid,i_relname text) return text   is     v_name text;   begin     select i_relnamespace::regnamespace||'.'||i_relname into v_name ;     return v_name;   end; select f_sum(relnamespace,relname) from pg_class limit 10;   f_sum ------------------------------- pg_toast.pg_toast_16384 pg_toast.pg_toast_16384_index public.t pg_catalog.pg_statistic pg_catalog.pg_type pg_toast.pg_toast_2600 pg_toast.pg_toast_2600_index pg_toast.pg_toast_2604 pg_toast.pg_toast_2604_index pg_toast.pg_toast_3456 (10 rows)

 

with function 可以支持DML 操作：

?

1 2 3 4 5 6 7 8

with   function f_sum(i_id integer,i_name text) return text   is   begin     insert into t1 values(i_id,i_name);     return i_name;   end; select f_sum(oid,relname) from pg_class limit 10;