core: Separate SQL current and upgrade scripts

[quassel.git] / src / core / SQL / PostgreSQL / setup_130_function_lastmsgid.sql

diff --git a/src/core/SQL/PostgreSQL/setup_130_function_lastmsgid.sql b/src/core/SQL/PostgreSQL/setup_130_function_lastmsgid.sql
new file mode 100644 (file)
index 0000000..561422a
--- /dev/null
+++ b/src/core/SQL/PostgreSQL/setup_130_function_lastmsgid.sql
@@ -0,0 +1,18 @@
+CREATE OR REPLACE FUNCTION populate_lastmsgid() RETURNS void AS $$
+DECLARE
+       i buffer%rowtype;
+BEGIN
+       FOR i IN SELECT * FROM buffer
+       LOOP
+               UPDATE buffer
+                       SET lastmsgid = (
+                               SELECT backlog.messageid
+                               FROM backlog
+                               WHERE backlog.bufferid = i.bufferid
+                               ORDER BY messageid DESC LIMIT 1
+                       )
+                       WHERE buffer.bufferid = i.bufferid;
+       END LOOP;
+       RETURN;
+END
+$$ LANGUAGE plpgsql;