Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-update.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-update.sql,v
diff -u -r1.8 -r1.9
--- openacs-4/packages/acs-content-repository/sql/postgresql/content-update.sql	4 Jun 2006 00:45:23 -0000	1.8
+++ openacs-4/packages/acs-content-repository/sql/postgresql/content-update.sql	7 Jul 2011 10:46:02 -0000	1.9
@@ -14,197 +14,198 @@
 
 --set serveroutput on
 -- FIXME: drop constraint doesn't work on postgresql
-create function inline_0 ()
-returns integer as '
-begin
+CREATE OR REPLACE FUNCTION inline_0 () RETURNS integer AS $$
+BEGIN
   -- altering the constraint on cr_type_template_map
-  raise NOTICE ''Altering constraint on cr_type_template_map...'';
-  execute ''alter table cr_type_template_map drop constraint cr_type_template_map_pk'';
-  execute ''alter table cr_type_template_map add constraint cr_type_template_map_pk primary key (content_type, template_id, use_context)'';
-  execute ''VACUUM ANALYZE cr_type_template_map'';
+  raise NOTICE 'Altering constraint on cr_type_template_map...';
+  execute 'alter table cr_type_template_map drop constraint cr_type_template_map_pk';
+  execute 'alter table cr_type_template_map add constraint cr_type_template_map_pk primary key (content_type, template_id, use_context)';
+  execute 'VACUUM ANALYZE cr_type_template_map';
 
   return 0;
-end;' language 'plpgsql';
+END;
+$$ LANGUAGE plpgsql;
 
 -- select inline_0 ();
 
 drop function inline_0 ();
 
-create function inline_1 () returns integer as '
-begin
+CREATE OR REPLACE FUNCTION inline_1 () RETURNS integer AS $$
+BEGIN
 
   -- Set the workflow permission as child of admin
   update acs_privilege_hierarchy
-    set privilege = ''cm_admin''
+    set privilege = 'cm_admin'
   where
-    privilege = ''cm_write''
+    privilege = 'cm_write'
   and
-    child_privilege = ''cm_item_workflow'';
+    child_privilege = 'cm_item_workflow';
 
-  if not table_exists(''cr_doc_filter'') then
+  if not table_exists('cr_doc_filter') then
 
-    raise NOTICE ''Creating CR_DOC_FILTER table for converting
-                          documents to HTML'';
+    raise NOTICE 'Creating CR_DOC_FILTER table for converting
+                          documents to HTML';
 
-    execute ''create table cr_doc_filter (
+    execute 'create table cr_doc_filter (
       revision_id        integer primary key,
       content            integer
-    )'';
+    )';
 
-   -- execute ''create index cr_doc_filter_index 
+   -- execute 'create index cr_doc_filter_index 
    --   on cr_doc_filter ( content ) indextype is ctxsys.context
-   --   parameters (''''FILTER content_filter_pref'''' )'';
+   --   parameters (''FILTER content_filter_pref'' )';
 
   end if;
 
-  if not table_exists(''cr_content_text'') then
+  if not table_exists('cr_content_text') then
 
-    raise NOTICE ''Creating CR_CONTENT_TEXT table'';
+    raise NOTICE 'Creating CR_CONTENT_TEXT table';
 
-    execute ''create table cr_content_text (
+    execute 'create table cr_content_text (
       revision_id        integer primary key,
       content            text
-    )'';
+    )';
 
   end if;
 
 
-  if not column_exists(''cr_folders'', ''has_child_folders'') then
+  if not column_exists('cr_folders', 'has_child_folders') then
 
-    raise NOTICE ''Adding HAS_CHILD_FOLDERS column to CR_FOLDERS and updating the column based on selection criteria.'';
+    raise NOTICE 'Adding HAS_CHILD_FOLDERS column to CR_FOLDERS and updating the column based on selection criteria.';
 
-    execute ''create view cr_resolved_items as
+    execute 'create view cr_resolved_items as
        select
          i.parent_id, i.item_id, i.name, 
-         case s.target_id is NULL then \\\'\\\'f\\\'\\\' else \\\'\\\'t\\\'\\\'  end as is_symlink,
+         case s.target_id is NULL then ''''f'''' else ''''t''''  end as is_symlink,
          coalesce(s.target_id, i.item_id) resolved_id, s.label
        from
          cr_items i left outer join cr_symlinks s
-       on i.item_id = s.symlink_id'';
+       on i.item_id = s.symlink_id';
 
-    execute ''alter table cr_folders add
+    execute 'alter table cr_folders add
       has_child_folders boolean
-			default \\\'\\\'f\\\'\\\''';
+			default ''''f''''';
 
-    execute ''update cr_folders f set has_child_folders =
-      coalesce((select \\\'\\\'t\\\'\\\' from dual where exists
+    execute 'update cr_folders f set has_child_folders =
+      coalesce((select ''''t'''' from dual where exists
 	    (select 1 from cr_folders f_child, cr_resolved_items r_child
 	       where r_child.parent_id = f.folder_id
-		 and f_child.folder_id = r_child.resolved_id)), \\\'\\\'f\\\'\\\')'';
+		 and f_child.folder_id = r_child.resolved_id)), ''''f'''')';
   end if;
 
 
 
-  if not column_exists(''cr_keywords'', ''parent_id'') then
+  if not column_exists('cr_keywords', 'parent_id') then
 
-    raise NOTICE ''Adding PARENT_ID column to CR_KEYWORDS and updating the parent id from the context id'';
+    raise NOTICE 'Adding PARENT_ID column to CR_KEYWORDS and updating the parent id from the context id';
 
-    execute ''alter table cr_keywords add 
+    execute 'alter table cr_keywords add 
        parent_id      integer 
                       constraint cr_keywords_hier
-                      references cr_keywords'';
+                      references cr_keywords';
 
-    execute ''update cr_keywords set parent_id = (
+    execute 'update cr_keywords set parent_id = (
                          select context_id from acs_objects 
-                         where object_id = keyword_id)'';
+                         where object_id = keyword_id)';
 
   end if;
 
-  if not table_exists(''cr_text'') then
+  if not table_exists('cr_text') then
 
-    raise NOTICE ''Creating CR_TEXT table for incoming text submissions...'';
+    raise NOTICE 'Creating CR_TEXT table for incoming text submissions...';
 
-    execute ''create table cr_text ( text text default \\\'\\\' not null )'';
+    execute 'create table cr_text ( text text default '''' not null )';
 
     -- For some reason a simple insert statement throws an error but this works
-    execute ''insert into cr_text values (NULL)'';
+    execute 'insert into cr_text values (NULL)';
 
   end if;
 
-  if not column_exists(''cr_items'', ''publish_status'') then
+  if not column_exists('cr_items', 'publish_status') then
 
-    raise NOTICE ''Adding PUBLISH_STATUS column to CR_ITEMS for tracking deployment status...'';
+    raise NOTICE 'Adding PUBLISH_STATUS column to CR_ITEMS for tracking deployment status...';
 
-    execute ''alter table cr_items add 
+    execute 'alter table cr_items add 
       publish_status varchar(40) 
                      constraint cr_items_pub_status_chk
                      check (publish_status in 
-                       (\\\'\\\'production\\\'\\\', \\\'\\\'ready\\\'\\\', \\\'\\\'live\\\'\\\', \\\'\\\'expired\\\'\\\'))'';
+                       (''''production'''', ''''ready'''', ''''live'''', ''''expired''''))';
 
-    execute ''update cr_items set publish_status = \\\'\\\'live\\\'\\\'
-                         where live_revision is not null'';
+    execute 'update cr_items set publish_status = ''''live''''
+                         where live_revision is not null';
 
-    execute ''alter table cr_item_publish_audit add column 
-      old_status varchar(40)'';
-    execute ''alter table cr_item_publish_audit add column
-      new_status varchar(40)'';
+    execute 'alter table cr_item_publish_audit add column 
+      old_status varchar(40)';
+    execute 'alter table cr_item_publish_audit add column
+      new_status varchar(40)';
 
   end if;
 
-  if not column_exists(''cr_items'', ''latest_revision'') then
+  if not column_exists('cr_items', 'latest_revision') then
 
-    raise NOTICE ''Adding LATEST_REVISION column to CR_ITEMS for tracking revision status...'';
+    raise NOTICE 'Adding LATEST_REVISION column to CR_ITEMS for tracking revision status...';
 
-    execute ''alter table cr_items add 
+    execute 'alter table cr_items add 
       latest_revision integer
                       constraint cr_items_latest_fk
-                      references cr_revisions'';
+                      references cr_revisions';
 
-    execute ''update cr_items 
+    execute 'update cr_items 
                        set latest_revision = 
-                         content_item__get_latest_revision(item_id)'';
+                         content_item__get_latest_revision(item_id)';
 
   end if;
 
-  if not table_exists(''cr_release_periods'') then
+  if not table_exists('cr_release_periods') then
 
-    raise NOTICE ''Creating CR_RELEASE_PERIODS table for scheduled publishing...'';
+    raise NOTICE 'Creating CR_RELEASE_PERIODS table for scheduled publishing...';
 
-    execute ''
+    execute '
       create table cr_release_periods (
 	item_id          integer
 			 constraint cr_release_periods_fk
 			 references cr_items
 			 constraint cr_release_periods_pk
 			 primary key,
 	start_when	 timestamptz default current_timestamp,
-	end_when	 timestamptz default current_timestamp + interval ''''20 years''''
-      )'';
+	end_when	 timestamptz default current_timestamp + interval ''20 years''
+      )';
 
   end if;
 
-  if not table_exists(''cr_scheduled_release_log'') then
+  if not table_exists('cr_scheduled_release_log') then
 
-    raise NOTICE ''Creating CR_SCHEDULED_RELEASE_LOG table for auditing of scheduled publishing...'';
+    raise NOTICE 'Creating CR_SCHEDULED_RELEASE_LOG table for auditing of scheduled publishing...';
 
-    execute ''
+    execute '
       create table cr_scheduled_release_log (
 	exec_date        timestamptz default current_timestamp not null,
 	items_released   integer not null,
 	items_expired    integer not null,
 	err_num          integer,
-	err_msg          varchar(500) default \\\'\\\' not null
-      )'';
+	err_msg          varchar(500) default '''' not null
+      )';
 
   end if;
 
-  if not table_exists(''cr_scheduled_release_job'') then
+  if not table_exists('cr_scheduled_release_job') then
 
-    raise NOTICE ''Creating CR_SCHEDULED_RELEASE_JOB table for tracking database job for scheduled publishing...'';
+    raise NOTICE 'Creating CR_SCHEDULED_RELEASE_JOB table for tracking database job for scheduled publishing...';
 
-    execute ''
+    execute '
       create table cr_scheduled_release_job (
         job_id     integer,
         last_exec  timestamptz
-      )'';
+      )';
 
-    execute ''
-      insert into cr_scheduled_release_job values (NULL, now())'';
+    execute '
+      insert into cr_scheduled_release_job values (NULL, now())';
 
   end if;
 
   return null;
-end;' language 'plpgsql';
+END;
+$$ LANGUAGE plpgsql;
 
 -- select inline_1 ();
 
