Postgres Trigger to apply a default theme to new Mattermost users

trigger.sql

CREATE OR REPLACE FUNCTION apply_default_theme_for_new_user() RETURNS TRIGGER AS $defaultTheme$
BEGIN
IF (SELECT COUNT(*) FROM preferences WHERE userid=NEW.id AND category='theme') = 0 THEN
  INSERT INTO preferences (userid, category, name, value)
  VALUES (NEW.id, 'theme', '', '{"awayIndicator":"#b8b884","buttonBg":"#004818","buttonColor":"#ffffff","centerChannelBg":"#ffffff","centerChannelColor":"#444444","codeTheme":"monokai","linkColor":"#004818","mentionBg":"#7E9949","mentionColor":"#ffffff","mentionHighlightBg":"#cceecc","mentionHighlightLink":"#444444","newMessageSeparator":"#90ad58","onlineIndicator":"#99cb3f","sidebarBg":"#262626","sidebarHeaderBg":"#363636","sidebarHeaderTextColor":"#ffffff","sidebarText":"#ffffff","sidebarTextActiveBorder":"#7e9949","sidebarTextActiveColor":"#ffffff","sidebarTextHoverBg":"#525252","sidebarUnreadText":"#0aff44","type":"custom"}');
END IF;
RETURN NEW;
END;
$defaultTheme$ LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS apply_default_theme_for_new_user ON users;

CREATE TRIGGER apply_default_theme_for_new_user AFTER INSERT ON users
FOR EACH ROW EXECUTE PROCEDURE apply_default_theme_for_new_user();

nice!! this was very helpful. thank you, @mmuth!

and if you already have some existing users, here is a solution to set a theme for them:

CREATE OR REPLACE FUNCTION theme_setter_func()
  RETURNS void AS
$func$
DECLARE
  temprow RECORD;
BEGIN
  FOR temprow IN 
    SELECT id,username FROM users 
  LOOP
    IF (SELECT COUNT(*) FROM preferences WHERE userid=temprow.id AND category='theme') = 0 THEN
      INSERT INTO preferences (userid, category, name, value)
        VALUES (temprow.id, 'theme', '', '{"awayIndicator":"#b8b884","buttonBg":"#004818","buttonColor":"#ffffff","centerChannelBg":"#ffffff","centerChannelColor":"#444444","codeTheme":"monokai","linkColor":"#004818","mentionBg":"#7E9949","mentionColor":"#ffffff","mentionHighlightBg":"#cceecc","mentionHighlightLink":"#444444","newMessageSeparator":"#90ad58","onlineIndicator":"#99cb3f","sidebarBg":"#262626","sidebarHeaderBg":"#363636","sidebarHeaderTextColor":"#ffffff","sidebarText":"#ffffff","sidebarTextActiveBorder":"#7e9949","sidebarTextActiveColor":"#ffffff","sidebarTextHoverBg":"#525252","sidebarUnreadText":"#0aff44","type":"custom"}');
    END IF;
  END LOOP;
END
$func$ LANGUAGE plpgsql;

SELECT theme_setter_func();

DROP FUNCTION theme_setter_func();

note that you may have a few users in there you might not want to set a theme for.
in that case, first inspect the full user list:

SELECT id,username FROM users;

locate some usernames you don't like.

and then instead of the above, use this modified code:

CREATE OR REPLACE FUNCTION theme_setter_func()
  RETURNS void AS
$func$
DECLARE
  temprow RECORD;
BEGIN
  FOR temprow IN 
    SELECT id,username FROM users WHERE username NOT IN ('admin', 'foo', 'surveybot')
  LOOP
    IF (SELECT COUNT(*) FROM preferences WHERE userid=temprow.id AND category='theme') = 0 THEN
      INSERT INTO preferences (userid, category, name, value)
        VALUES (temprow.id, 'theme', '', '{"awayIndicator":"#b8b884","buttonBg":"#004818","buttonColor":"#ffffff","centerChannelBg":"#ffffff","centerChannelColor":"#444444","codeTheme":"monokai","linkColor":"#004818","mentionBg":"#7E9949","mentionColor":"#ffffff","mentionHighlightBg":"#cceecc","mentionHighlightLink":"#444444","newMessageSeparator":"#90ad58","onlineIndicator":"#99cb3f","sidebarBg":"#262626","sidebarHeaderBg":"#363636","sidebarHeaderTextColor":"#ffffff","sidebarText":"#ffffff","sidebarTextActiveBorder":"#7e9949","sidebarTextActiveColor":"#ffffff","sidebarTextHoverBg":"#525252","sidebarUnreadText":"#0aff44","type":"custom"}');
    END IF;
  END LOOP;
END
$func$ LANGUAGE plpgsql;

SELECT theme_setter_func();

DROP FUNCTION theme_setter_func();

in this example, the users admin, foo, and surveybot are excluded.

I would like to see this feature coming back to the CE as well..!