PosgreSQL - loop syntax

Question

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?

Did you forget to add the language at end? $$ LANGUAGE plpgsql; — Vivek
– Vivek, Commented Feb 19, 2020 at 6:38

Andronicus · Accepted Answer · 2020-02-19 06:38:51Z

2

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 $$;

answered Feb 19, 2020 at 6:38

Andronicus

26.2k18 gold badges55 silver badges92 bronze badges

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

1 Comment

michal.jakubeczy Over a year ago

thanks, this was the case. I thought I have to close DO with end as well (dollars confused me). My first PostgreSQL loop :)

S-Man · Accepted Answer · 2020-02-19 07:16:07Z

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

Put the first SELECT query (which you used for the loop) into a second CTE at the top.
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.
The join is the basis for the second INSERT.

Collectives™ on Stack Overflow

PosgreSQL - loop syntax

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related