with function 语法支持

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

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 操作：

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;