src/core/SQL/PostgreSQL/version/20/upgrade_001_add_function_populate_lastmsgid.sql

   1 CREATE OR REPLACE FUNCTION populate_lastmsgid() RETURNS void AS $$
   2 DECLARE
   3         i buffer%rowtype;
   4 BEGIN
   5         FOR i IN SELECT * FROM buffer
   6         LOOP
   7                 UPDATE buffer
   8                         SET lastmsgid = COALESCE((
   9                                 SELECT backlog.messageid
  10                                 FROM backlog
  11                                 WHERE backlog.bufferid = i.bufferid
  12                                 ORDER BY messageid DESC LIMIT 1
  13                         ), 0)
  14                         WHERE buffer.bufferid = i.bufferid;
  15         END LOOP;
  16         RETURN;
  17 END
  18 $$ LANGUAGE plpgsql;