How do I avoid syntax error in FOR loops in postgresql?

Question

I'm using PostgreSQL 10.5 and I have the following SQL:

FOR temprow IN
    SELECT o.objectid, o.nametag, cor.userseqno, cor.commseqno
    FROM "commuserobjectrights" as cor
    LEFT JOIN "object" as o ON cor.objectid = o.objectid
    WHERE o.nametag LIKE 'commission.video_questions'
LOOP
    INSERT INTO u commuserobjectrights (objectid, commseqno, userseqno, access) 
    VALUES (temprow.objectid, temprow.commseqno, temprow.userseqno, TRUE);
END LOOP;

which throws the following error:

ERROR: syntax error at or near "FOR" Position: 3

I have never used loops before but according the documentation, postgresql should have support for these types of loops. And yes, I have checked and double checked that all tables and column names are spelled correctly.

Answer 1

FOR is procedural code, so you need to use DO or use it in stored code.

DO $$ 
DECLARE
  temprow record ;
BEGIN 
  FOR temprow IN
    SELECT o.objectid, o.nametag, cor.userseqno, cor.commseqno
    FROM "commuserobjectrights" as cor
    LEFT JOIN "object" as o ON cor.objectid = o.objectid
    WHERE o.nametag LIKE 'commission.video_questions'
  LOOP
    INSERT INTO commuserobjectrights (objectid, commseqno, userseqno, access) 
    VALUES (temprow.objectid, temprow.commseqno, temprow.userseqno, TRUE);
  END LOOP;
END;
$$;

This is not the most efficient way to do this task but for other tasks where you can't easily write SQL DO may be useful.