Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d18-5.9.1d19.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d18-5.9.1d19.sql,v
diff -u -N
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d18-5.9.1d19.sql	20 Dec 2016 20:28:49 -0000	1.1.2.1
@@ -0,0 +1,155 @@
+begin;
+
+-- added
+select define_function_args('util__foreign_key_exists','table,column,reftable,refcolumn');
+
+--
+-- procedure util__foreign_key_exists/4
+--
+CREATE OR REPLACE FUNCTION util__foreign_key_exists(
+   p_table text,
+   p_column text,
+   p_reftable text,
+   p_refcolumn text
+) RETURNS boolean AS $$
+DECLARE
+BEGIN
+      return exists (
+      select 1 from
+         information_schema.table_constraints AS tc,
+         information_schema.key_column_usage AS kcu,
+         information_schema.constraint_column_usage AS ccu
+      where tc.constraint_name = kcu.constraint_name
+        and tc.constraint_catalog = kcu.constraint_catalog
+        and tc.constraint_schema = kcu.constraint_schema
+        and tc.table_catalog = kcu.table_catalog
+        and tc.table_schema = kcu.table_schema
+        and ccu.constraint_name = tc.constraint_name
+        and ccu.constraint_catalog = kcu.constraint_catalog
+        and ccu.constraint_schema = kcu.constraint_schema
+        and ccu.table_catalog = kcu.table_catalog
+        and ccu.table_schema = kcu.table_schema
+        and tc.constraint_type = 'FOREIGN KEY'
+        and tc.table_name   = p_table
+        and kcu.column_name = p_column
+        and ccu.table_name  = p_reftable
+        and ccu.column_name = p_refcolumn);
+END;
+$$ LANGUAGE plpgsql;
+
+-- added
+select define_function_args('util__unique_exists','table,column,single_p;true');
+
+--
+-- procedure util__unique_exists/3
+--
+CREATE OR REPLACE FUNCTION util__unique_exists(
+   p_table    text,
+   p_column   text,
+   p_single_p boolean default true
+) RETURNS boolean AS $$
+DECLARE
+BEGIN
+      return exists (select 1
+       from
+         information_schema.table_constraints AS tc,
+         information_schema.key_column_usage  AS kcu
+      where tc.constraint_name    = kcu.constraint_name
+        and tc.constraint_catalog = kcu.constraint_catalog
+        and tc.constraint_schema  = kcu.constraint_schema
+        and tc.table_catalog      = kcu.table_catalog
+        and tc.table_schema       = kcu.table_schema
+        and tc.constraint_type    = 'UNIQUE'
+        and tc.table_name   = p_table
+        and kcu.column_name = p_column
+	and (not p_single_p or (
+	   -- this to ensure the constraint involves only one
+	   -- column
+	   select count(*) from information_schema.key_column_usage
+	    where constraint_name    = kcu.constraint_name
+	      and constraint_catalog = kcu.constraint_catalog
+	      and constraint_schema  = kcu.constraint_schema) = 1));
+END;
+$$ LANGUAGE plpgsql;
+
+-- added
+select define_function_args('util__primary_key_exists','table,column,single_p;true');
+
+--
+-- procedure util__unique_exists/3
+--
+CREATE OR REPLACE FUNCTION util__primary_key_exists(
+   p_table    text,
+   p_column   text,
+   p_single_p boolean default true
+) RETURNS boolean AS $$
+DECLARE
+BEGIN
+      return exists (select 1
+       from
+         information_schema.table_constraints AS tc,
+         information_schema.key_column_usage  AS kcu
+      where tc.constraint_name    = kcu.constraint_name
+        and tc.constraint_catalog = kcu.constraint_catalog
+        and tc.constraint_schema  = kcu.constraint_schema
+        and tc.table_catalog      = kcu.table_catalog
+        and tc.table_schema       = kcu.table_schema
+        and tc.constraint_type    = 'PRIMARY KEY'
+        and tc.table_name   = p_table
+        and kcu.column_name = p_column
+	and (not p_single_p or (
+	   -- this to ensure the constraint involves only one
+	   -- column
+	   select count(*) from information_schema.key_column_usage
+	    where constraint_name    = kcu.constraint_name
+	      and constraint_catalog = kcu.constraint_catalog
+	      and constraint_schema  = kcu.constraint_schema) = 1));
+END;
+$$ LANGUAGE plpgsql;
+
+
+-- added
+select define_function_args('util__not_null_exists','table,column');
+
+--
+-- procedure util__not_null_exists/2
+--
+--
+-- procedure util__not_null_exists/2
+--
+CREATE OR REPLACE FUNCTION util__not_null_exists(
+   p_table    text,
+   p_column   text
+) RETURNS boolean AS $$
+DECLARE
+BEGIN
+      return (
+        coalesce((
+	select is_nullable = 'NO'
+	  from information_schema.columns
+	 where table_name  = p_table
+	   and column_name = p_column), false));
+END;
+$$ LANGUAGE plpgsql;
+
+-- added
+select define_function_args('util__get_default','table,column');
+
+--
+-- procedure util__get_default/2
+--
+CREATE OR REPLACE FUNCTION util__get_default(
+   p_table    text,
+   p_column   text
+) RETURNS information_schema.columns.column_default%TYPE AS $$
+DECLARE
+BEGIN
+      return (
+	select column_default
+	  from information_schema.columns
+	 where table_name  = p_table
+	   and column_name = p_column);
+END;
+$$ LANGUAGE plpgsql;
+
+end;