Moderators (requests and notices)

One day I will need to remember I just added this:

CREATE OR REPLACE FUNCTION UpdateHuddleCountsForSite(in_site_id integer)
RETURNS VOID AS $$
DECLARE profile RECORD;
BEGIN 
FOR profile IN SELECT profile_id, profile_name FROM profiles WHERE site_id = in_site_id ORDER BY profile_id
LOOP

WITH u AS (
    SELECT COALESCE(COUNT(*) OVER(), 0) AS total
      FROM flags ff
      JOIN (
               SELECT hp.huddle_id
                     ,f.last_modified
                 FROM huddle_profiles hp
                      JOIN flags f ON f.item_type_id = 5
                                  AND f.item_id = hp.huddle_id
                 LEFT JOIN read r ON r.profile_id = profile.profile_id
                                 AND r.item_type_id = 5
                                 AND r.item_id = f.item_id
                 LEFT JOIN read r2 ON r2.profile_id = profile.profile_id
                                  AND r2.item_type_id = 5
                                  AND r2.item_id = 0
                WHERE hp.profile_id = profile.profile_id
                  AND f.last_modified > COALESCE(
                                            COALESCE(
                                                r.read,
                                                r2.read
                                            ),
                                            TIMESTAMP WITH TIME ZONE '1970-01-01 12:00:00'
                                        )
           ) AS h ON ff.parent_item_id = h.huddle_id
                 AND ff.parent_item_type_id = 5
                 AND ff.last_modified >= h.last_modified
      LEFT JOIN ignores i ON i.profile_id = profile.profile_id
                         AND i.item_type_id = 3
                         AND i.item_id = ff.created_by
     WHERE i.profile_id IS NULL
     GROUP BY h.huddle_id
     LIMIT 1
)
UPDATE profiles
   SET unread_huddles = COALESCE((SELECT total FROM u), 0)
 WHERE profile_id = profile.profile_id;

RAISE NOTICE '% %', profile.profile_id, profile.profile_name;
END LOOP;
END;
$$language plpgsql;