Postgresql script doesn't work as I expect

All we need is an easy explanation of the problem, so here it is.

DO $$
DECLARE companyNrs text[] = ARRAY(SELECT LPAD(companynr::text, 5, '0') FROM s_users);
        companyNr text;
BEGIN
  FOREACH companyNr IN ARRAY companyNrs
  LOOP        
    raise notice 'notice message %', companyNr;
  END LOOP;
END; $$

The postgresql script above works perfectly on DataGrip. But if I add a DECLARE statement into the loop(as below), it gives multiple syntax errors. What is the rule here ?

Postgresql script doesn't work as I expect

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Where did you find such syntax in the documentation?

You need declare variables in the same block.

DO $$
DECLARE companyNrs text[] = ARRAY[1,2,3];
        companyNr text;
        v_sql text;
BEGIN
  FOREACH companyNr IN ARRAY companyNrs
  LOOP
    v_sql := format('foo bar %L', companyNr);
    raise notice 'notice message %', v_sql;
  END LOOP;
END; $$;

Or it is possible to declare another sub-block:

DO $$
DECLARE companyNrs text[] = ARRAY[1,2,3];
        companyNr text;
BEGIN
  FOREACH companyNr IN ARRAY companyNrs
  LOOP
    --
    -- Create a subblock
    --
    DECLARE
        v_sql text;
    BEGIN
        v_sql := format('foo bar %L', companyNr);
        raise notice 'notice message %', v_sql;
    END;
  END LOOP;
END; $$;

Subblocks with EXCEPTION clause are implicit savepoints and have a performance impact. Not sure about subblocks without EXCEPTION clause. They may not be cheap syntactic sugar either.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply