PostgreSQL动态构建CTE的PL/pgSQL函数执行报错求助

问题场景 #

需要实现数据归档功能：定期将主表符合条件的记录迁移到归档表，同时删除原表记录。通过tbl_archive_master表存储原表名、归档表名及过滤条件，动态构建CTE完成操作。手动执行生成的CTE语句正常，但函数执行时报错。

函数代码 #

CREATE OR REPLACE FUNCTION some_f() 
RETURNS text
AS
$func$
DECLARE
  r record;
  q text;
BEGIN
   for r in
        select concat(from_tbl_schema,'.',from_tbl_name)::text as main_t_name, concat(arch_schema,'.',arch_tbl_name)::text as  arch_t_name, where_clause::text
        from axis.tbl_archive_master t
        where t.from_tbl_name = 'tbl_req_tracking_arch' and t.arch_tbl_name='tbl_req_tracking_arch2'
   loop
    q := format('with deleted_rows as (delete from %s where %s returning * ) insert into %s select deleted_rows.* from deleted_rows ;',r.main_t_name, r.where_clause, r.arch_t_name);
    raise notice '%', q;
    execute q;
   end loop;
   perform 'end';
END;
$func$
language plpgsql;

执行报错信息 #

Axis=# select some_f();
NOTICE:  with deleted_rows as (delete from axis.tbl_req_tracking_arch where "CREATE_DATE" <= (current_date - 30) returning * ) insert into axis.tbl_req_tracking_arch2 select deleted_rows.* from deleted_rows ;
ERROR:  control reached end of function without RETURN
CONTEXT:  PL/pgSQL function some_f()

手动执行CTE结果 #

Axis=# with deleted_rows as (delete from axis.tbl_req_tracking_arch where "CREATE_DATE" <= (current_date - 30) returning * ) insert into axis.tbl_req_tracking_arch2 select deleted_rows.* from deleted_rows ;
INSERT 0 0
Axis=#

问题原因与解决方法 #

原因 #

函数声明RETURNS text，要求必须返回一个文本值，但函数体末尾没有RETURN语句返回内容，导致PostgreSQL抛出错误。perform 'end';只是执行无意义语句，无法替代RETURN的作用。

解决方案 #

有两种处理方式：

1. 改为无返回值函数
   如果不需要函数返回任何内容，将返回类型改为void，此时无需添加RETURN语句：

CREATE OR REPLACE FUNCTION some_f() 
RETURNS void  -- 修改返回类型
AS
$func$
DECLARE
  r record;
  q text;
BEGIN
   for r in
        select concat(from_tbl_schema,'.',from_tbl_name)::text as main_t_name, concat(arch_schema,'.',arch_tbl_name)::text as  arch_t_name, where_clause::text
        from axis.tbl_archive_master t
        where t.from_tbl_name = 'tbl_req_tracking_arch' and t.arch_tbl_name='tbl_req_tracking_arch2'
   loop
    q := format('with deleted_rows as (delete from %s where %s returning * ) insert into %s select deleted_rows.* from deleted_rows ;',r.main_t_name, r.where_clause, r.arch_t_name);
    raise notice '%', q;
    execute q;
   end loop;
END;
$func$
language plpgsql;

2. 添加返回语句
   如果需要返回执行状态或信息，在函数末尾添加RETURN语句返回文本：

CREATE OR REPLACE FUNCTION some_f() 
RETURNS text
AS
$func$
DECLARE
  r record;
  q text;
BEGIN
   for r in
        select concat(from_tbl_schema,'.',from_tbl_name)::text as main_t_name, concat(arch_schema,'.',arch_tbl_name)::text as  arch_t_name, where_clause::text
        from axis.tbl_archive_master t
        where t.from_tbl_name = 'tbl_req_tracking_arch' and t.arch_tbl_name='tbl_req_tracking_arch2'
   loop
    q := format('with deleted_rows as (delete from %s where %s returning * ) insert into %s select deleted_rows.* from deleted_rows ;',r.main_t_name, r.where_clause, r.arch_t_name);
    raise notice '%', q;
    execute q;
   end loop;
   RETURN '归档操作执行完成';  -- 添加返回语句
END;
$func$
language plpgsql;

内容的提问来源于stack exchange，提问作者Danish