This is the first time I'm trying to write a plpgsql function and I'm having some difficulty. I would like to write a function that loops through each row and updates the row depending on specific field value I've attached (2) screenshots of the original table and what the table should look like after running the function. This code is just focusing on 1 record at this point and once I got this working I was going to figure out the loop

create or replace function migrate_contact_roles() returns setof test_contact_roles as $$
  declare
    d_roles text[] := array[]::text[];
    d_id int;
    d_acct boolean;
    str_acct text = "acct";
  begin
    -- lookup 1 contact
    select 
      id, is_acct into d_id, d_acct
      from test_contact_roles where id = 1;

    if (d_acct) then
      d_roles := d_roles || str_acct;
    end if;
    update test_contact_roles set roles = d_roles where id = d_id;
    return query select * from get_contacts()
      where id = 1;
  end;
$$ language plpgsql;

When I run the function I'm getting the following error > Failed to run sql query: column "acct" does not exist Any help or suggestions greatly appreciated! Thank you

why a loop? just make a single update query something like this:

sql
update test_contact_roles set roles = array_append(roles,'acct') where is_acct;

bc of my experience level. Thanks!

to be clear, you don't need to write a function, just run that one SQL command.

oh

for safety, do it inside a transaction:

sql
begin;
update ....;
-- select rows to inspect it did what you want
commit;

I was just dealing with 1 field in my example but I've got 3 field is_tech, is_admin, is_acct. So if is_admin and is_tech is true the roles field should be ["admin","tech"]

You can run them one after the other. First pass set "admin" WHERE is_admin and next pass set "tech" WHERE is_tech.

makes sense. yeah just one-time

is the roles column "not null"? if it can be null, you need to do something otherwise the append will just remain null.

it's allow null but with a default ARRAY[]::text[]

that should be safe, but run this to verify:

select * from test_contact_roles where roles is null

yeah, i get Success. No rows returned

Then you're good to go with those updates.

cool, thanks for your time I appreciate it