sql - postgresql 错误：无法从函数或多命令字符串执行 VACUUM

Question

所有我必须做的块，我计划运行以对具有从 n 到 n 的死行的表执行真空操作，
但是面临错误 ERROR: VACUUM cannot be executed from a function or multi-command string

询问如何运行它，我需要清理 200 多个模式和 800 多个表，我无法在完整数据库上或一个一个地执行它，这就是为什么我打算使用游标来执行它。

这是代码

DO 
$$

--declaretion of varibles
 DECLARE d_date timestamp :=NOW(); -- tday  date
         t_sql text;                -- main SQL stetent  updae 
         t_simulation_Before text ; -- hold select for simulation  before 
         t_value TEXT ; -- value used to update 
         i_min_n_dead_tup integer   := 0;
         i_max_n_dead_tup integer   := 1000 ;    

 -----------------------EXECUTE FLAG------------------
 ----- do not set to = Y if not ready to deploy-------
        v_exec char(1) :='N';
 -----------------------------------------------------
 DECLARE cur_upd_text  refcursor;
  
BEGIN
  OPEN Cur_upd_text FOR -- cursor. to go over all schemas and create proper dynamic SQl statment

  SELECT 'VACUUM FULL ANALYZE '||schemaname ||'.'|| relname  , n_dead_tup
    FROM pg_stat_user_tables
   where n_dead_tup >0 and  n_dead_tup< 1000 
    ;

  LOOP
        
    FETCH Cur_upd_text INTO t_simulation_Before, t_value ;-- assignin results from select to variables
    EXIT WHEN NOT FOUND;

    IF v_exec = 'Y' THEN  -- executing statments 
        RAISE NOTICE 'Statment to EXECUTE %, n_dead_tup %',t_simulation_Before, t_value; 
        EXECUTE t_simulation_Before; -- Execute dynamic SQL
    ELSE    
        RAISE NOTICE 'Statment to EXECUTE %, n_dead_tup %',t_simulation_Before, t_value;  -- use this is want to check what variable holds
    END IF;
  END LOOP;
  
  CLOSE Cur_upd_text; -- close and deallocate cursor
  
END
$$;```


    

标签： sqlpostgresql