I'm struggling with a plpgsql function to create/r...
# sql
s
I'm struggling with a plpgsql function to create/remove 'tags'. Hoping someone can point me in the right direction.
**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**:
Copy code
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**:
Copy code
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?
j
little late here but this looks like you want to completely overwrite the old tags - it might be simpler if you simply delete all old tags associated with that job first (i.e.
DELETE FROM jobs_tags WHERE job_id = x
), then just unnest the new tags and insert it into the same table (
INSERT INTO jobs_tags SELECT x, UNNEST(tags)
)?