summaryrefslogtreecommitdiff
path: root/util/dump.sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-12-05 16:28:43 +0100
committerYorhel <git@yorhel.nl>2009-12-05 17:20:34 +0100
commit1e541e2643929b792089671a60bd40a7bdfee541 (patch)
tree75770ab753e413eae38b6ad7536795a10d014b14 /util/dump.sql
parentc67155961f1903a5e7c6c99b84cae08b7046a1a3 (diff)
SQL: Don't allow argument of update_vncache() to be 0
To batch update, simply do a SELECT update_vncache(id) FROM vn; The function is now more readable as well.
Diffstat (limited to 'util/dump.sql')
-rw-r--r--util/dump.sql29
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