如何在PostgreSQL中更新内部的源表以进行循环主体

Question

我有一个表格，我要迭代循环，并且在试图更新for循环中的源表数据时，它没有反映。在下面的示例中，我希望Rasie通知语句应执行5次，但仅执行3次。

DO $$
DECLARE p_Id int;

BEGIN
    DROP TABLE IF EXISTS TempTbl;
    CREATE TEMPORARY TABLE TempTbl (Id int);
    INSERT INTO TempTbl VALUES (1);
    INSERT INTO TempTbl VALUES (2);
    INSERT INTO TempTbl VALUES (3);    
        
    FOR p_Id in SELECT * FROM TempTbl
    LOOP        
        RAISE NOTICE 'Value of p_Id: %', p_Id;

        INSERT INTO TempTbl
        SELECT 4
        WHERE 4 NOT IN (SELECT Id FROM TempTbl);
        
        INSERT INTO TempTbl
        SELECT 5
        WHERE 5 NOT IN (SELECT Id FROM TempTbl);      
    END LOOP;
    
END $$;

当前输出

NOTICE:  Value of p_Id: 1
NOTICE:  Value of p_Id: 2
NOTICE:  Value of p_Id: 3
 

预期输出

NOTICE:  Value of p_Id: 1
NOTICE:  Value of p_Id: 2
NOTICE:  Value of p_Id: 3
NOTICE:  Value of p_Id: 4
NOTICE:  Value of p_Id: 5

注意：我将从前3行中检索值4和5（即，我无法插入全部开始时的值）。

Answer 1

循环似乎是基于误解。无论哪种方式，这都是它的工作方式：

DO
$do$
DECLARE
   p_id int;
   val4 int;
   val5 int;
BEGIN
   DROP TABLE IF EXISTS pg_temp.tbl;
   CREATE TEMPORARY TABLE tbl (id int PRIMARY KEY);
   INSERT INTO tbl VALUES (1), (2), (3);    

   FOR p_id IN
      SELECT * FROM tbl
   LOOP        
      RAISE NOTICE 'Value of p_id: %', p_id;
      -- compute values val4 & val5
      -- which, oddly, seems to require a loop?
      -- I doubt that
   END LOOP;

   -- this somehow happens in the loop
   val4 := 4;
   val5 := 5;

   INSERT INTO tbl VALUES (val4) ON CONFLICT DO NOTHING;
   IF FOUND THEN
      RAISE NOTICE 'Value of p_id: %', val4;
   ELSE
      RAISE NOTICE 'Value of p_id: % (failed with unique violation)', val4;
   END IF;

   INSERT INTO tbl VALUES (val5) ON CONFLICT DO NOTHING;
   IF FOUND THEN
      RAISE NOTICE 'Value of p_id: %', val5;
   ELSE
      RAISE NOTICE 'Value of p_id: % (failed with unique violation)', val5;
   END IF;
END
$do$;

避免不在（选择...）中，除非您确切知道自己在做什么。请参阅：

并避免使用骆驼标识符。请参阅：