CODEWITHSUNDEEP
sqlpostgresqlloopsfor-looppsql
michal.jakubeczy
michal.jakubeczy

Reputation: 9479

PosgreSQL - loop syntax

I have the following loop (simplified for an example):

DO $$
  DECLARE
    l record;

  BEGIN
    FOR l IN SELECT id, country_id FROM logo LOOP
      WITH cs AS (
        INSERT INTO logo_settings (targeted) VALUES (true)
        RETURNING id
      )
      INSERT INTO logo_settings_targeted (logo_settings_id, country_id)
      VALUES 
      ( (SELECT id FROM cs),
        logo.country_id,
      );
    END LOOP;
  END;
END $$;

The body of a loop works fine. But if I wrap it into a loop (I would like to make records for all records from logo table) - it shows an error saying: 

ERROR: syntax error at or near "END"
  Position: 712

END;
^

meaning the last EnND before END $$; which does not give much sense to me. I do not know what to fix to make it running. Any hints?

Upvotes: 0

Views: 43

Answers (2)

S-Man
S-Man

Reputation: 23726

Additionally to answer of @Andronicus, this is one possible way to do this without PL/pgsql:

Click: demo:db<>fiddle

WITH countries AS  (
    SELECT id, country_id, 
        row_number() OVER ()                                        -- 1
    FROM logo
), ins_settings AS (
    INSERT INTO logo_settings(targeted)
    SELECT true FROM countries c
    RETURNING id
)
INSERT INTO logo_settings_targeted (logo_settings_id, country_id)
SELECT
  ins.id, c.country_id
FROM
  (SELECT id, row_number() OVER () FROM ins_settings) ins            -- 2
JOIN countries c ON c.row_number = ins.row_number
  1. Put the first SELECT query (which you used for the loop) into a second CTE at the top.
  2. The trick is, that you can join the outputs from the logo SELECT statement and the first insert. You can use the fact, that both outputs have the same number of rows. Simply add a column with a row count to both outputs. This can be done, for example, using the row_number() window function. As you can see, for the logo SELECT I already did this directly in the CTE (1), for the INSERT output I added this in a subquery (2). Now there are the same identifiers on both tables, which can be used for the join.
  3. The join is the basis for the second INSERT.

Upvotes: 1

Andronicus
Andronicus

Reputation: 26056

There is one end to many. end loop closes the loop body:

DO $$
  DECLARE
    l record;

  BEGIN
    FOR l IN SELECT id, country_id FROM logo LOOP
      WITH cs AS (
        INSERT INTO logo_settings (targeted) VALUES (true)
        RETURNING id
      )
      INSERT INTO logo_settings_targeted (logo_settings_id, country_id)
      VALUES 
      ( (SELECT id FROM cs),
        logo.country_id,
      );
    END LOOP;
  END $$;

Upvotes: 2

Related Questions