Trying to execute a dynamic CTE in PostgreSQL function

Question

I'm trying to create a Postgresql function which will dynamically build the CTE and execute it to do the archive/purge.

Context: Records from main tables must be archived to their corresponding archive table and then deleted from the original table on a periodic basis. For this, tbl_archive_master table has the columns which have of the original table (from_tbl_name), the archive table (arch_tbl_name) and the where condition used (where_clause) to do the archive and purge.

Below is the function but it doesn't seem to execute 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;

...even though RAISE NOTICE shows it is populated fine.

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()

But if I run the cte directly, it runs fine. In below case, there were no records in axis.tbl_req_tracking_arch to archive and purge.

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

Answer 1

PostgreSQL doesn't complain about the EXECUTE line like you think, but about the lack of a RETURN statement that returns a text. On the other hand, your PERFORM statement doesn't do anything useful. Perhaps you meant to say

RETURN 'end';

instead of

PERFORM 'end';

The latter statement just runs SELECT 'end';, then discards the result.