From 5abbade0e992c4b6fc9be545692040fc4450c8e6 Mon Sep 17 00:00:00 2001 From: Matthias Andreas Benkard 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(-) 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