在pl / pgsql中的表中插入一行

I am trying to update two tables by inserting some rows into the other. But when I try to do something like this :

我试图通过在另一个中插入一些行来更新两个表。但是当我尝试做这样的事情时:

BEGIN
    FOR id IN (SELECT id FROM table1) LOOP
     PERFORM (INSERT INTO anothertable VALUES(id));
    END LOOP;
END;

It gives an error I don't know why. syntax error at or near "INTO".

它给出了一个错误,我不知道为什么。 “INTO”或其附近的语法错误。

Is it even possible to do such thing without cursors and such updating one by one ?

是否有可能在没有游标的情况下做这样的事情并逐个更新?

2 个解决方案

#1

This is example of bad using PERFORM and bad plpgsql programming style. You must not use a PERFORM there. You should not use a parenthesis there (PL/pgSQL is based on ADA language - not C!). Some correct patterns are:

这是使用PERFORM和糟糕的plpgsql编程风格的不良示例。你不能在那里使用PERFORM。你不应该在那里使用括号(PL / pgSQL基于ADA语言 - 而不是C!)。一些正确的模式是:

FOR _id IN 
          SELECT s.id 
             FROM sometab s
LOOP
  INSERT INTO othertab(id) VALUES(_id);
END LOOP;

or faster (and shorter) pattern

或更快(和更短)的模式

INSERT INTO othertab(id)
   SELECT id 
       FROM other tab

I used a qualified name and prefixes to reduce a risk of conflict between SQL identifiers and PL/pgSQL variables.

我使用了限定名称和前缀来降低SQL标识符和PL / pgSQL变量之间发生冲突的风险。

Note: PERFORM is implemented as SELECT statement without result processing. So your statement was SELECT (INSERT INTO tab ...) what is unsupported feature now.

注意:PERFORM是作为SELECT语句实现的,没有结果处理。所以你的陈述是SELECT(INSERT INTO tab ...)现在不支持的功能。

#2

why don't you insert it like this:

你为什么不这样插入它:

insert into anothertable
select id from table

#1

FOR _id IN 
          SELECT s.id 
             FROM sometab s
LOOP
  INSERT INTO othertab(id) VALUES(_id);
END LOOP;