Skip to content

Instantly share code, notes, and snippets.

@jirutka
Created April 2, 2021 21:14
Show Gist options
  • Save jirutka/f10f1cc9a98e22ec66642566aadba4d4 to your computer and use it in GitHub Desktop.
Save jirutka/f10f1cc9a98e22ec66642566aadba4d4 to your computer and use it in GitHub Desktop.
Example of PostgreSQL trigger function to sort and deduplicate an array
create function array_uniq (a anyarray) returns anyarray
immutable
language sql
as $$
select array_agg(distinct x) from unnest(a) x;
$$;
comment on function array_uniq is
'Sorts and deduplicates elements of an array.';
create function trg_person_roles_normalize () returns trigger
language plpgsql
as $$
begin
NEW.roles = array_uniq(NEW.roles);
return NEW;
end;
$$;
comment on function trg_person_roles_normalize is
'A trigger function for table person that sorts and deduplicates elements of '
'the roles array.';
create trigger roles_normalize
before insert or update of roles on person
for each row
when (cardinality(NEW.roles) > 0)
execute function trg_person_roles_normalize();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment