CODEWITHSUNDEEP
sqlpostgresqlplpgsql
ayan ahmedov
ayan ahmedov

Reputation: 391

insert a row into table in 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".

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

Upvotes: 3

Views: 6940

Answers (2)

Pavel Stehule
Pavel Stehule

Reputation: 45940

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:

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.

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

Upvotes: 4

roman
roman

Reputation: 117606

why don't you insert it like this:

insert into anothertable
select id from table

Upvotes: 1

Related Questions