diff options
Diffstat (limited to 'util/dump.sql')
| -rw-r--r-- | util/dump.sql | 29 |
1 files changed, 11 insertions, 18 deletions
diff --git a/util/dump.sql b/util/dump.sql index 6376857a..70f79b2d 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -424,21 +424,15 @@ ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn -- update_vncache(id) - updates the c_* columns in the vn table -CREATE FUNCTION update_vncache(id integer) RETURNS void AS $$ -DECLARE - w text := ''; -BEGIN - IF id > 0 THEN - w := ' WHERE id = '||id; - END IF; - EXECUTE 'UPDATE vn SET +CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$ + UPDATE vn SET c_released = COALESCE((SELECT MIN(rr1.released) FROM releases_rev rr1 JOIN releases r1 ON rr1.id = r1.latest JOIN releases_vn rv1 ON rr1.id = rv1.rid WHERE rv1.vid = vn.id - AND rr1.type <> ''trial'' + AND rr1.type <> 'trial' AND r1.hidden = FALSE AND rr1.released <> 0 GROUP BY rv1.vid @@ -450,12 +444,12 @@ BEGIN JOIN releases r2 ON rr2.id = r2.latest JOIN releases_vn rv2 ON rr2.id = rv2.rid WHERE rv2.vid = vn.id - AND rr2.type <> ''trial'' - AND rr2.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer + AND rr2.type <> 'trial' + AND rr2.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer AND r2.hidden = FALSE GROUP BY rl2.lang ORDER BY rl2.lang - ), ''/''), ''''), + ), '/'), ''), c_platforms = COALESCE(ARRAY_TO_STRING(ARRAY( SELECT rp3.platform FROM releases_platforms rp3 @@ -463,15 +457,14 @@ BEGIN JOIN releases r3 ON rp3.rid = r3.latest JOIN releases_vn rv3 ON rp3.rid = rv3.rid WHERE rv3.vid = vn.id - AND rr3.type <> ''trial'' - AND rr3.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer + AND rr3.type <> 'trial' + AND rr3.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer AND r3.hidden = FALSE GROUP BY rp3.platform ORDER BY rp3.platform - ), ''/''), '''') - '||w; -END; -$$ LANGUAGE plpgsql; + ), '/'), '') + WHERE id = $1; +$$ LANGUAGE sql; -- recalculate vn.c_popularity |