stibbs
10/28/2021, 10:57 PMstibbs
10/28/2021, 10:58 PMstibbs
10/28/2021, 11:00 PMtags = ['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 recordsstibbs
10/28/2021, 11:03 PMsql
jobs
- id uuid
tags
- id bigint
- tag text
jobs_tags
- job_id references jobs
- tag_id references tags
- primary key (job_id, tag_id)
stibbs
10/28/2021, 11:05 PMsql
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; $$
stibbs
10/29/2021, 1:24 AMjason-lynx
11/02/2021, 1:55 AMDELETE 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)
)?