summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorMatthias Andreas Benkard <code@mail.matthias.benkard.de>2011-03-13 14:15:16 +0100
committerMatthias Andreas Benkard <code@mail.matthias.benkard.de>2011-03-13 14:15:16 +0100
commit5abbade0e992c4b6fc9be545692040fc4450c8e6 (patch)
treeff6279cded0d5de5fe1ef8fc6285aa2630bdeb92
parentefce515bf8d5e44b23cb047165912ce817500533 (diff)
Make the database schema slightly more portable.
-rw-r--r--schema.sql45
1 files changed, 25 insertions, 20 deletions
diff --git a/schema.sql b/schema.sql
index 5f636fe..52a6ff2 100644
--- a/schema.sql
+++ b/schema.sql
@@ -1,3 +1,5 @@
+--*- mode: sql; product: postgres -*--
+
BEGIN TRANSACTION;
-- For PostgreSQL 9.1 upward, we should consider using enum types
@@ -236,16 +238,17 @@ $$ LANGUAGE plpgsql;
-- SELECT article_revisions_for_characteristics(70, ARRAY[ROW(ARRAY[ROW('language', 'de')::characteristic])]::characteristic_list[]);
-CREATE FUNCTION older_revision(a article_revisions, b article_revisions)
- RETURNS article_revisions AS $$
-BEGIN
- IF a.date < b.date THEN
- RETURN a;
- ELSE
- RETURN b;
- END IF;
-END
-$$ LANGUAGE plpgsql;
+CREATE OR REPLACE FUNCTION older_revision(
+ IN article_revisions,
+ IN article_revisions,
+ OUT article_revisions
+) AS $$
+ SELECT $1 WHERE $1.date < $2.date OR $2.date IS NULL
+ UNION
+ SELECT $2 WHERE $1.date >= $2.date OR $1.date IS NULL
+ UNION
+ SELECT $1 WHERE $1.date IS NULL AND $2.date IS NULL
+$$ LANGUAGE SQL;
CREATE AGGREGATE oldest_revision (article_revisions) (
SFUNC = older_revision,
@@ -253,16 +256,18 @@ CREATE AGGREGATE oldest_revision (article_revisions) (
);
-CREATE FUNCTION more_recent_revision(a article_revisions, b article_revisions)
- RETURNS article_revisions AS $$
-BEGIN
- IF a.date > b.date THEN
- RETURN a;
- ELSE
- RETURN b;
- END IF;
-END
-$$ LANGUAGE plpgsql;
+CREATE OR REPLACE FUNCTION more_recent_revision(
+ IN article_revisions,
+ IN article_revisions,
+ OUT article_revisions
+) AS $$
+ SELECT $1 WHERE $1.date > $2.date OR $2.date IS NULL
+ UNION
+ SELECT $2 WHERE $1.date <= $2.date OR $1.date IS NULL
+ UNION
+ SELECT $1 WHERE $1.date IS NULL AND $2.date IS NULL
+$$ LANGUAGE SQL;
+
CREATE AGGREGATE most_recent_revision (article_revisions) (
SFUNC = more_recent_revision,