Stack Exchange
Syntax error when trying to perform FOREACH
I have the following function:
begin;
CREATE OR REPLACE FUNCTION convert_categories_16_12_2019() RETURNS VOID AS $$
DECLARE
    category_index VARCHAR;
    has_extra1_content  BOOLEAN;
    has_extra2_content BOOLEAN;
    result_category BOOLEAN;
    r RECORD;
BEGIN
    FOR r IN (SELECT * FROM campaign_campaign) LOOP
        result_category = FALSE;
        has_extra1_content  = FALSE;
        has_extra2_content = FALSE;

        FOREACH category_index IN ARRAY regexp_split_to_array(r.category, ',') LOOP

            IF (category_index::int = 6 OR
                category_index::int = 7) 
            THEN has_extra1_content = TRUE 
            END IF;

            IF (category_index::int = 0 OR 
                category_index::int = 1 OR
                category_index::int = 2 OR
                category_index::int = 3 OR
                category_index::int = 4 OR                          
                category_index::int = 5)                            
            THEN has_extra2_content = TRUE 
            END IF;

            CASE
                WHEN has_extra1_content  AND NOT has_extra2_content
                THEN result_category = 0;

                WHEN has_extra2_content AND has_extra1_content 
                THEN result_category = 1;
            ELSE
                result_category = 2;
            END CASE;

        END LOOP;

        UPDATE campaign_campaign 
        SET campaign_campaign.category = result_category
        WHERE id = r.id;

    END LOOP;
    RETURN;
END
$$ LANGUAGE plpgsql;

select convert_categories_16_12_2019();
DROP FUNCTION convert_categories_16_12_2019;
rollback;

But when I am trying to run it I get:
ERROR:  ERROR:  syntax error(near: "END")
LINE 21:             END IF;

Can somebody explain why this error happens? I do not see any issues with syntax. 
Resources I used:
https://dba.stackexchange.com/questions/234657/syntax-error-when-using-exception-inside-for-loop
https://www.postgresql.org/docs/9.4/plpgsql-control-structures.html
The issue was a missing ; after statement in THEN branch, so it should be:
IF (category_index::int = 6 OR
    category_index::int = 7) 
THEN has_extra1_content = TRUE; /* semicolon here too! */
END IF;

And by advise of you, dear stackoverflow users, I have replaced the function with this UPDATE statement:
UPDATE campaign_campaign SET category =

    CASE WHEN (
        campaign_campaign.category != '' 
        AND regexp_split_to_array(campaign_campaign.category, ',')::int[] && ARRAY[6,7] 
        AND NOT regexp_split_to_array(campaign_campaign.category, ',')::int[] && ARRAY[0,1,2,3,4,5]
    ) THEN 0

    WHEN (
        campaign_campaign.category != ''
        AND regexp_split_to_array(campaign_campaign.category, ',')::int[] && ARRAY[0,1,2,3,4,5]
        AND NOT regexp_split_to_array(campaign_campaign.category, ',')::int[] && ARRAY[6,7]
    ) THEN 1

    ELSE 2

    END;


You are missing and END IF for your IF statements.

Why do you use a slow and inefficient cursor loop rather than just a single UPDATE statement?

@a_horse_with_no_name I have made this on purpose because if I added END IF I got syntax error on "END IF".

@a_horse_with_no_name Actually this a migration script and should be run only ones.

@Andrey are both if statements ended with a END IF;? If so, please update the question

Even if you run something only once, you should choose the more efficient (and way easier) solution. A single UPDATE statement is all you need. And even if you insist on doing this with a slow and inefficient PL/pgSQL loop there is no need to create and drop a function. Simply use a DO block.

Changed query to one with END IF statements, but again have "Syntax Error"((

@a_horse_with_no_name Now I am not missing END IF could you please explain why new syntax error raised? Aggred about easier way but this was a first attempt...

Now you are missing a ; and the end of the line where you assign the value. This would all be sooo much easier with a single UPDATE statement

@a_horse_with_no_name O-o-o... it was so silly, sorry. Looks like I need a time to rest(

You are looping through the list in category_index to set result_category repeatedly - which makes no sense as only the last setting can prevail. Please always start question like this one with your Postgres version and table definition. And disclose what category_index can look like. (Probably shouldn't be a text string ...)

@Erwin Brandstetter Thanks! it is really a string like "0,1,2"...

Looks like you can replace all of it with just:
UPDATE campaign_campaign 
SET    category = 
   CASE WHEN string_to_array(category, ',') && '{6,7}'::text[] THEN
      CASE WHEN string_to_array(category, ',') && '{0,1,2,3,4,5}'::text[] THEN '1' ELSE '0' END
   ELSE '2' END;

This assumes no insignificant whitespace in your original text strings. Else, a cast to int[] (like you had) is still necessary.
Target columns in UPDATE statements cannot be table-qualified, btw., so not SET campaign_campaign.category = ....
You should then convert the column category to integer.
Or use true / false / null in a boolean column instead.
