尝试在 PostgreSQL 函数中执行动态 CTE-解网

问：

我正在尝试创建一个 Postgresql 函数，该函数将动态构建 CTE 并执行它以执行存档/清除。

上下文：主表中的记录必须存档到其相应的存档表中，然后定期从原始表中删除。为此，tbl_archive_master表具有原始表（from_tbl_name）、存档表（arch_tbl_name）和用于执行存档和清除的 where 条件（where_clause）的列。

下面是函数，但它似乎没有执行 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;  -- this line fails
   end loop;
   perform 'end';
END;
$func$
language plpgsql;

...即使 RAISE NOTICE 显示它已正常填充。


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.tbl_req_tracking_arch中没有要存档和清除的记录。

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=#

PostgreSQL 函数 common-table-expression dynamic-sql

谢谢你@LaurenzAlbe和@Zegarek！！我将其更改为返回“end”，现在我可以看到执行部分正在工作。Axis=# 选择 some_f（）;注意：deleted_rows为（从axis.tbl_req_tracking_arch中删除，其中“CREATE_DATE” <= （current_date - 30）返回 * ）插入axis.tbl_req_tracking_arch2从deleted_rows中选择 deleted_rows.* ;some_f -------- 结束（1 行） Axis=# select count（） from axis.tbl_req_tracking_arch; select count（） from axis.tbl_req_tracking_arch2;计数 ------- 3709 （1 行）计数 ------- 19620 （1 行）

答：

0赞 Laurenz Albe 11/14/2023 #1

PostgreSQL 并不像您想象的那样抱怨这一行，而是抱怨缺少返回 .另一方面，你的陈述没有任何用处。也许你的意思是说EXECUTERETURNtextPERFORM

RETURN 'end';

而不是

PERFORM 'end';

后一个语句只是运行，然后丢弃结果。SELECT 'end';

上一个：如何在函数中传递克隆的元素？

下一个：否则错误出现在 postgressql 的函数中

尝试在 PostgreSQL 函数中执行动态 CTE

Trying to execute a dynamic CTE in PostgreSQL function

评论