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.

Tim Biegeleisen · Accepted Answer · 2018-09-04 08:24:56Z

4

You can't use FOR loops outside of procedural code. But, in general Postgres (and SQL) is optimized to already do set based operations. So, you may phrase this as an INSERT INTO ... SELECT:

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

edited Sep 4, 2018 at 8:24

answered Sep 3, 2018 at 14:40

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Weblurk Over a year ago

Hmm, you don't seem to have included the value TRUE of the access column which I need to insert.

Tim Biegeleisen Over a year ago

@Weblurk You're right; I updated my answer. You should be able to select TRUE as a literal in the SELECT clause.

Weblurk Over a year ago

This worked perfectly, but I think you made a typo, leaving a "u" character after INSERT INTO. I'll mark this as accepted, but can you edit your answer, removing the "u"?

Jasen · Accepted Answer · 2022-03-01 21:37:26Z

4

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.

edited Mar 1, 2022 at 21:37

answered Sep 3, 2018 at 23:03

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related