Index: openacs-4/packages/acs-kernel/sql/postgresql/utilities-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/utilities-create.sql,v
diff -u -r1.8 -r1.9
--- openacs-4/packages/acs-kernel/sql/postgresql/utilities-create.sql	27 Oct 2014 16:39:36 -0000	1.8
+++ openacs-4/packages/acs-kernel/sql/postgresql/utilities-create.sql	7 Aug 2017 23:47:56 -0000	1.9
@@ -21,7 +21,7 @@
    v_count integer
 ) RETURNS varchar AS $$
 DECLARE
-  a_sequence_values      text default ''; 
+  a_sequence_values      text default '';
   v_rec                  record;
 BEGIN
     for counter in 1..v_count loop
@@ -33,7 +33,7 @@
     end loop;
 
     return substr(a_sequence_values, 2);
- 
+
 END;
 $$ LANGUAGE plpgsql;
 
@@ -53,9 +53,9 @@
       IF true_or_false is null THEN
         return null;
       ELSE IF true_or_false = 'f' THEN
-        return 't';   
-      ELSE 
-        return 'f';   
+        return 't';
+      ELSE
+        return 'f';
       END IF; END IF;
 END;
 $$ LANGUAGE plpgsql immutable strict;
@@ -73,8 +73,8 @@
 DECLARE
 BEGIN
       return exists (
-       select 1 from pg_class 
-          where relname = name 
+       select 1 from pg_class
+          where relname = name
             and pg_table_is_visible(oid));
 END;
 $$ LANGUAGE plpgsql;
@@ -94,7 +94,7 @@
 BEGIN
       return exists (
        select 1 from information_schema.columns c
-         where c.table_name  = t_name 
+         where c.table_name  = t_name
            and c.column_name = c_name);
 END;
 $$ LANGUAGE plpgsql;
@@ -134,5 +134,168 @@
 $$ LANGUAGE plpgsql;
 
 
+-- 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
+--
+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;
+
+-- added
+select define_function_args('util__get_primary_keys','table');
+
+--
+-- procedure util__get_primary_keys/1
+--
+CREATE OR REPLACE FUNCTION util__get_primary_keys(
+   p_table text
+) RETURNS SETOF pg_attribute.attname%TYPE AS $$
+  SELECT a.attname
+    FROM pg_index i
+    JOIN pg_attribute a ON a.attrelid = i.indrelid
+                       AND a.attnum = ANY(i.indkey)
+  WHERE i.indrelid = p_table::regclass
+    AND i.indisprimary;
+$$ LANGUAGE sql;
