summaryrefslogtreecommitdiff
path: root/util/updates/update_1.18.sql
blob: b2d37d5a3158054489f20a89a19d73833ea4de94 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- prev -> rev
ALTER TABLE changes ADD COLUMN rev integer NOT NULL DEFAULT 1;
ALTER TABLE changes DROP COLUMN prev;

DROP FUNCTION update_prev(text, text);

CREATE OR REPLACE FUNCTION update_rev(tbl text, ids text) RETURNS void AS $$
DECLARE
  r RECORD;
  r2 RECORD;
  i integer;
  t text;
  e text;
BEGIN
  SELECT INTO t SUBSTRING(tbl, 1, 1);
  e := '';
  IF ids <> '' THEN
    e := ' WHERE id IN('||ids||')';
  END IF;
  FOR r IN EXECUTE 'SELECT id FROM '||tbl||e LOOP
    i := 1;
    FOR r2 IN EXECUTE 'SELECT id FROM '||tbl||'_rev WHERE '||t||'id = '||r.id||' ORDER BY id ASC' LOOP
      UPDATE changes SET rev = i WHERE id = r2.id;
      i := i+1;
    END LOOP;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
SELECT update_rev('vn', ''), update_rev('releases', ''), update_rev('producers', '');