looping throught each row till the end of a 2d array of integer in postgresql

Question

so basically I got a 2d array in a table. table SQL

CREATE TABLE IF NOT EXISTS public.table_name
(
"Person_Id" numeric(24,0) NOT NULL,
"Items" integer[],
);

so if I do this

select "Items"[:][:] 
from public.table_name;

I would get all the elements that I added manually

I put those values manually, and I tried this

DO
$do$
BEGIN 
FOR i IN 1..500 LOOP
    update public.table_name
    if("Items"[i][1] == null)
        then exit;
    end if;
    set "Items"[i][1] = 41
    where "Items"[i][1] = 6;
END LOOP;
END
$do$;

I don't want it like this but what I want is the loop to stop when my Items are done at the end. for the person_id 1, I need to do run the loop 3 times to get all elements and I know that but for each person, it's not always 3, it can vary, so how can I limit the loop so that it doesn't run more than what's needed. please help me FYI, the above code with the if clause gives me an error.

Answer 1

If you have to loop in SQL, there's probably a better way.

Instead of an array, this is much easier and faster as a join table. They also enforce referential integrity, meaning you can't refer to an item which does not exist. Join tables are how lists are traditionally stored in SQL.

Assuming {{1,4},{2,4},{3,4}} means they have 4 of item 1, 4 of item 2, and 4 of item 3...

create table people (
  id bigserial primary key
);

create table items (
  id bigserial primary key
);

-- The join table for the items each person has.
create table peoples_items (
  item_id bigint not null references items(id),
  person_id bigint not null references people(id),
  quantity int not null,

  -- A person can only one record per item.
  unique(item_id, person_id)
);

If I'm understanding your update, you want to change any instance of item 41 to item 6. You can do that in a single update.

update peoples_items
set item_id = 41
where item_id = 6;

Demonstration.

Other notes:

• Don't quote column names if you don't have to, it forces you to use the exact case.
• Use a simple bigserial for primary keys. If there's some sort of business specific key make that a separate column.