Figures for 'syslog-ng with triggers and custom enum types'

create-get-host-enum-function.sql

create or replace function get_host_enum(hostname text)
  returns host_enum as
$$
declare
   _value host_enum;
begin
   select hosts1.key from hosts1 where hosts1.hostname = $1 into _value;

   if not found then
      insert into hosts1(hostname) values ($1) on conflict do nothing;
      select hosts1.key from hosts1 where hosts1.hostname = $1 into _value;
   end if;

   return _value;
end
$$
language plpgsql;

create-host-insert-trigger.sql

--
-- function that creates a 'host_enum' as a new host is added to the table.
--
create or replace function host_insert() returns trigger as
$$
begin
   -- verify that no record already exists
   perform * from hosts1 where hosts1.hostname = new.hostname;
   if found then
      raise 'attempt to insert duplicate value %', quote_literal(new.hostname);
   end if;

   -- assign new host_enum with record.
   new.key = gen_host_enum(new.hostname);

   return new;
end
$$
language plpgsql;

--
-- add trigger to table
--
create trigger before_host_insert before insert on hosts1 for each row execute function host_insert();

create-new-host-enum-function.sql

--
-- create new host_enum
--
create or replace function new_host_enum(value text)
   returns host_enum as
$$
declare
   _enum host_enum;
   _uuid uuid;
begin
   _uuid = gen_random_uuid();

   -- find enum value reserved for this purpose
   with s as (select unnest(enum_range(null::host_enum)))
      select * from s where s.unnest::text like 'reserved-%' limit 1 into _enum;

   if not found then
      execute format('alter type host_enum add value if not exists %L', 'reserved-' || _uuid);
      raise 'Cached ''host_enum'' value did not exist - now fixed. Please retry operation.';
   end if;

   -- rename reserved enum value. This will fail if the value already exists.
   execute format('alter type host_enum rename value %L to %L', _enum, $1);

   -- create new enum value
   execute format('alter type host_enum add value if not exists %L', 'reserved-' || _uuid);

   return _enum;
end
$$
language plpgsql;

create-syslog-enums.sql

--
-- Create 'enum' data types for syslog 'level' and 'facility' values. These are static lists.
--
-- Values are defined in https://www.syslog-ng.com/technical-documents/doc/syslog-ng-open-source-edition/3.22/administration-guide/8#TOPIC-1209091
-- The order matters - we want the 'position' to match syslog-ng numeric value.
--
-- The '${TAG}' value is equal to `(facility-id << 3) + (level)`
--
create type facility as enum (
    'kern',
    'user',
    'email',
    'daemon',
    'auth',
    'syslog',
    'lpr',
    'news',
    'uucp',
    'cron'
    'authpriv',
    'ftp',
    'ntp',
    'security',
    'console',
    'solaris-cron',
    'local0',
    'local1',
    'local2',
    'local3',
    'local4',
    'local5',
    'local6',
    'local7'
);

create type level as enum (
    'emerg',
    'alert',
    'crit',
    'err',
    'warning',
    'notice',
    'info',
    'debug'
);