From 5abbade0e992c4b6fc9be545692040fc4450c8e6 Mon Sep 17 00:00:00 2001
From: Matthias Andreas Benkard <code@mail.matthias.benkard.de>
Date: Sun, 13 Mar 2011 14:15:16 +0100
Subject: Make the database schema slightly more portable.

---
 schema.sql | 45 +++++++++++++++++++++++++--------------------
 1 file changed, 25 insertions(+), 20 deletions(-)

(limited to 'schema.sql')

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,
-- 
cgit v1.2.3