**GOAL**: A user can update their post. One element of the post is a list of 'tags'. If the user changes tags when they update the post, it needs to add/remove tags from the mapping table.

**INPUTS**: My front end captures the tags as a list of text. E.g.

tags = ['dev', 'cloud']

**DESIRED LOGIC**: For each item in the array, check if mapping table (

jobs_tags

) has a record with that tag + job id combination. There are 3 paths from there: 1) If matching record exists, do nothing 2) If matching record does not exist , insert a row 3) If additional records exist in mapping table that do not match any items in the array, delete those records

**TABLE SCHEMA EXCERPT**:

sql
jobs
- id uuid

tags
- id bigint
- tag text

jobs_tags
- job_id references jobs
- tag_id references tags
- primary key (job_id, tag_id)

**Work-In-Progress Function**:

sql
create or replace function test_function (
  in i_job_id uuid,
  in i_categories text[]
)
returns table (
  job_id uuid,
  tag_id bigint,
  tag text
)
language plpgsql
as $$
begin
  -- insert any new tags found and
  -- ignore any that already exist
  insert into public.jobs_tags (job_id, tag_id) 
  select i_job_id as job_id, tags.id as tag_id
  from unnest(i_categories) as new_tag
  inner join public.tags
  on tags.tag = new_tag
  on conflict
  do nothing;

  -- remove old unused tags
  with v1 as (
    select jt.job_id, jt.tag_id
    from public.jobs_tags jt
    inner join public.tags t
    on jt.tag_id = t.id
    where jt.job_id = i_job_id
      and t.tag != all(i_categories)
  )
  delete from public.jobs_tags jt
  using v1
  where jt.job_id = v1.job_id
    and jt.tag_id = v1.tag_id;
end; $$

^ above works 😄 if anyone still takes a look at this I'm curious if there's a "better" way?