PostgreSQL trigger to add field to jsonb on insert #postgres #postgis #jsonb #json

trigger.sql

drop function if exists add_puuid_fuuid_fields;
CREATE FUNCTION add_puuid_fuuid_fields()
RETURNS trigger AS $$
BEGIN
  IF NEW.json -> 'properties' ->> 'puuid' IS NULL OR  NEW.json -> 'properties' ->> 'puuid' = '' THEN
    NEW.json = jsonb_insert(NEW.json, '{properties,puuid}', ('"'|| (md5(random()::text || clock_timestamp()::text)::uuid) || '"' :: text)::jsonb);
    NEW.json = jsonb_insert(NEW.json, '{properties,fuuid}', ('"' || 'bgtterugmeldingen.' || (new.json -> 'properties' ->> 'puuid') || '"' :: text)::jsonb);
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql

DROP trigger if exists add_puuid_fuuid_fields_trigger ON bgtterugmeldingen.bgtterugmeldingen_json;
CREATE TRIGGER add_puuid_fuuid_fields_trigger
BEFORE INSERT ON bgtterugmeldingen.bgtterugmeldingen_json
FOR EACH ROW
EXECUTE PROCEDURE add_puuid_fuuid_fields();