From dde08aa5cf12c06dfe7e1298a5367b1a20686aea Mon Sep 17 00:00:00 2001
From: Tom Hughes <tom@compton.nu>
Date: Tue, 4 Jun 2013 13:55:31 +0100
Subject: [PATCH] Create postgres databases from template0 and set the
 collation and ctype

---
 cookbooks/postgresql/libraries/postgresql.rb | 6 ++++--
 cookbooks/postgresql/providers/database.rb   | 4 +++-
 cookbooks/postgresql/resources/database.rb   | 2 ++
 3 files changed, 9 insertions(+), 3 deletions(-)

diff --git a/cookbooks/postgresql/libraries/postgresql.rb b/cookbooks/postgresql/libraries/postgresql.rb
index bd10a0233..2f672960c 100644
--- a/cookbooks/postgresql/libraries/postgresql.rb
+++ b/cookbooks/postgresql/libraries/postgresql.rb
@@ -75,10 +75,12 @@ class Chef
     end
 
     def databases
-      @databases ||= query("SELECT d.datname, u.usename, d.encoding FROM pg_database AS d INNER JOIN pg_user AS u ON d.datdba = u.usesysid").inject({}) do |databases,database|
+      @databases ||= query("SELECT d.datname, u.usename, d.encoding, d.datcollate, d.datctype FROM pg_database AS d INNER JOIN pg_user AS u ON d.datdba = u.usesysid").inject({}) do |databases,database|
         databases[database[:datname]] = {
           :owner => database[:usename],
-          :encoding => database[:encoding]
+          :encoding => database[:encoding],
+          :collate => database[:datcollate],
+          :ctype => database[:datctype]
         }
         databases
       end
diff --git a/cookbooks/postgresql/providers/database.rb b/cookbooks/postgresql/providers/database.rb
index eee79031d..9cf9dd442 100644
--- a/cookbooks/postgresql/providers/database.rb
+++ b/cookbooks/postgresql/providers/database.rb
@@ -26,13 +26,15 @@ def load_current_resource
   if pg_database = @pg.databases[@current_resource.database]
     @current_resource.owner(pg_database[:owner])
     @current_resource.encoding(pg_database[:encoding])
+    @current_resource.encoding(pg_database[:collate])
+    @current_resource.encoding(pg_database[:ctype])
   end
   @current_resource
 end
 
 action :create do
   unless @pg.databases.include?(new_resource.database)
-    @pg.execute(:command => "CREATE DATABASE #{new_resource.database} OWNER #{new_resource.owner} ENCODING '#{new_resource.encoding}'")
+    @pg.execute(:command => "CREATE DATABASE #{new_resource.database} OWNER #{new_resource.owner} TEMPLATE template0 ENCODING '#{new_resource.encoding}' LC_COLLATE '#{new_resource.collation}' LC_CTYPE '#{new_resource.ctype}'")
     new_resource.updated_by_last_action(true)
   else
     if new_resource.owner != @current_resource.owner
diff --git a/cookbooks/postgresql/resources/database.rb b/cookbooks/postgresql/resources/database.rb
index 039c12467..1a63f3ac8 100644
--- a/cookbooks/postgresql/resources/database.rb
+++ b/cookbooks/postgresql/resources/database.rb
@@ -23,6 +23,8 @@ attribute :database, :kind_of => String, :name_attribute => true
 attribute :cluster, :kind_of => String, :required => true
 attribute :owner, :kind_of => String, :required => true
 attribute :encoding, :kind_of => String, :default => "UTF8"
+attribute :collation, :kind_of => String, :default => "en_GB.UTF8"
+attribute :ctype, :kind_of => String, :default => "en_GB.UTF8"
 
 def initialize(*args)
   super
-- 
2.39.5