54 lines
1.3 KiB
PL/PgSQL
54 lines
1.3 KiB
PL/PgSQL
select create_newtype_text('public.grp_tag');
|
|
|
|
create table public.grp
|
|
( id int not null primary key generated always as identity
|
|
, uid human_uuid.huid not null unique default human_uuid.huid()
|
|
, deleted boolean not null default false
|
|
, tag public.grp_tag not null unique
|
|
);
|
|
|
|
create function public.grp_admins()
|
|
returns public.grp
|
|
stable
|
|
language sql
|
|
as $$select * from public.grp where tag = public.grp_tag_of_string('admins')$$;
|
|
|
|
create function public.do_grp_add_admins()
|
|
returns trigger
|
|
volatile
|
|
language plpgsql
|
|
as $$
|
|
declare
|
|
admins int[];
|
|
begin
|
|
admins := ( select array_agg(usr.id)
|
|
from public.grp_members_admins() as usr
|
|
);
|
|
|
|
perform public.grp_add_members( to_grp => NEW.id
|
|
, add_usrs => admins
|
|
);
|
|
|
|
return null;
|
|
end;
|
|
$$;
|
|
|
|
create trigger grp_add_admins
|
|
after insert on public.grp
|
|
for each row
|
|
execute function public.do_grp_add_admins();
|
|
|
|
select audit( 'public'
|
|
, 'grp'
|
|
, array[] :: audited_column[]
|
|
, soft_delete => true
|
|
);
|
|
|
|
select immutable( 'public'
|
|
, 'grp'
|
|
, array[ 'id'
|
|
, 'uid'
|
|
, 'tag'
|
|
]
|
|
);
|