Postgres 9.6 How to loop over array and insert each array value into tables?

Question

I can't seem to figure this out, the following is an attempt to get an array of items to run as foreach loop in postgres using pgadmin query tool, for each item x in the array, do 2 insert statements where x is each array value:

I tried this based on https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY section 42.6.7. Looping through Arrays, here is some pseudocode:

DO
$do$
BEGIN
   FOREACH x IN ['Apple','Banana','Cantaloupe']
   LOOP

    INSERT INTO foods.fruits(id, comment, fruit_name)
    VALUES ((SELECT MAX(id) + 1 FROM foods.fruits), 'a comment', x);

    INSERT INTO foods.fruits_ordered(id, price, fruit_id)
    VALUES ((SELECT MAX(id) + 1 FROM foods.fruits_ordered), '5.00', (SELECT id FROM foods.fruits WHERE fruit_name = x));

   END LOOP;
END
$do$;

This should run the loop 3 times and do a total of 6 insertions. Any idea how to get this to work?

select max() + 1 is a terrible way to generate unique IDs. You should use a serial column (based on a sequence). But you don't need PL/pgSQL or a loop to begin with: dbfiddle.uk/… — a_horse_with_no_name
Sorry missed the comment field in the first insert and price field in second insert just updated them, the error I got was ERROR: syntax error at or near "[" btw — Big Mike

Unknown Unknown · Accepted Answer · 2020-09-17T19:41:35

this ['Apple','Banana','Cantaloupe'] may not a correct array definition, this should be more like array['Apple','Banana','Cantaloupe']::varchar[]

Fixed loop could be something like following

...
FOREACH x IN array(select array['Apple','Banana','Cantaloupe']::varchar[])
...

Next, that x should be declared before block is started(begin), as following

DO
$do$
/*
type could be text
Better be based on target field should be inserted as
foods.fruits.fruit_name%type
*/
declare x varchar(31); 
declare res text;
BEGIN
...

final script could be something like

DO
$do$
declare x _names.n%type;
BEGIN
   FOREACH x IN array(select array['Apple','Banana','Cantaloupe']::varchar[])
   LOOP
   /*inserts here...*/    
   END LOOP;
END
$do$;

Another option is using unnest function, that pivot an array into rows.

e.g.

select array[9,1,1,9,9,2]::int[]
/*
{9,1,1,9,9,2}
*/
select unnest("array") from (select array[9,1,1,9,9,2]::int[] as "array") as "req"
/*
9
1
1
9
9
2
*/

Loop over the select using for x in select ...

Postgres 9.6 How to loop over array and insert each array value into tables?

1 Answers