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.13.2.3 -r1.13.2.4
--- openacs-4/packages/acs-kernel/sql/postgresql/utilities-create.sql	11 May 2020 19:10:38 -0000	1.13.2.3
+++ openacs-4/packages/acs-kernel/sql/postgresql/utilities-create.sql	23 Feb 2022 18:24:53 -0000	1.13.2.4
@@ -25,11 +25,11 @@
   v_rec                  record;
 BEGIN
     for counter in 1..v_count loop
-	for v_rec in EXECUTE 'select ' || quote_ident(v_sequence_name) || '.nextval as a_seq_val'
-	LOOP
-	   a_sequence_values := a_sequence_values || '','' || v_rec.a_seq_val;
-	  exit;
-	end loop;
+        for v_rec in EXECUTE 'select ' || quote_ident(v_sequence_name) || '.nextval as a_seq_val'
+        LOOP
+           a_sequence_values := a_sequence_values || '','' || v_rec.a_seq_val;
+          exit;
+        end loop;
     end loop;
 
     return substr(a_sequence_values, 2);
@@ -47,15 +47,15 @@
 --
 CREATE OR REPLACE FUNCTION util__logical_negation(
    true_or_false boolean
-) RETURNS boolean AS $$
-DECLARE
+) RETURNS boolean
+AS $$
 BEGIN
       IF true_or_false is null THEN
-	return null;
+        return null;
       ELSE IF true_or_false = 'f' THEN
-	return 't';
+        return 't';
       ELSE
-	return 'f';
+        return 'f';
       END IF; END IF;
 END;
 $$ LANGUAGE plpgsql immutable strict;
@@ -69,68 +69,68 @@
 --
 CREATE OR REPLACE FUNCTION util__table_exists(
    name text
-) RETURNS boolean AS $$
+) RETURNS boolean
+AS $$
 DECLARE
  v_schema    varchar;
  v_tablename varchar;
 BEGIN
     IF (position('.' in name) = 0) THEN
-	--
-	-- table without a schema name
-	--
-	return exists (
-	    select 1 from pg_class
-		where relname = name
-	    and pg_table_is_visible(oid));
+        --
+        -- table without a schema name
+        --
+        return exists (
+            select 1 from pg_class
+                where relname = name
+            and pg_table_is_visible(oid));
     ELSE
-	--
-	-- table with schema name
-	--
-	SELECT split_part(name, '.', 1) into v_schema;
-	SELECT split_part(name, '.', 2) into v_tablename;
-	return exists (
-	    select 1 from information_schema.tables
-	    where table_schema = v_schema
-	    and   table_name = v_tablename);
+        --
+        -- table with schema name
+        --
+        SELECT split_part(name, '.', 1) into v_schema;
+        SELECT split_part(name, '.', 2) into v_tablename;
+        return exists (
+            select 1 from information_schema.tables
+            where table_schema = v_schema
+            and   table_name = v_tablename);
    END IF;
 END;
 $$ LANGUAGE plpgsql;
 
 
-
-
 --
--- procedure util__table_column_exists/1
+-- procedure util__table_column_exists/2
 --
-select define_function_args('util__table_column_exists','p_table,p_column');
+select define_function_args('util__table_column_exists','table_name,column');
 
 CREATE OR REPLACE FUNCTION util__table_column_exists(
-   p_table  text,
+   p_table_name text,
    p_column text
-) RETURNS boolean AS $$
+) RETURNS boolean
+AS $$
 DECLARE
  v_schema    varchar;
  v_tablename varchar;
 BEGIN
-    IF (position('.' in p_table) = 0) THEN
-	--
-	-- table without a schema name
-	--
-	return exists (
-	    select 1 from information_schema.columns c
-	    where table_name  = lower(p_table)
-	    and column_name = lower(p_column));
+    IF (position('.' in p_table_name) = 0) THEN
+        --
+        -- table without a schema name
+        --
+        return exists (
+            select 1 from information_schema.columns c
+            where table_name  = lower(p_table_name)
+            and column_name = lower(p_column));
     ELSE
-	--
-	-- table with schema name
-	--
-	SELECT split_part(p_table, '.', 1) into v_schema;
-	SELECT split_part(p_table, '.', 2) into v_tablename;
-	return exists (
-	    select 1 from information_schema.columns
-	    where table_name  = lower(v_tablename)
-	    and column_name = lower(p_column)
-	    and table_schema = v_schema);
+        --
+        -- table with schema name
+        --
+        SELECT split_part(p_table_name, '.', 1) into v_schema;
+        SELECT split_part(p_table_name, '.', 2) into v_tablename;
+        return exists (
+            select 1 from information_schema.columns
+            where p_table_name  = lower(v_tablename)
+            and column_name = lower(p_column)
+            and table_schema = v_schema);
     END IF;
 END;
 $$ LANGUAGE plpgsql;
@@ -144,27 +144,28 @@
 --
 CREATE OR REPLACE FUNCTION util__view_exists(
    name text
-) RETURNS boolean AS $$
+) RETURNS boolean
+AS $$
 DECLARE
  v_schema    varchar;
  v_tablename varchar;
 BEGIN
     IF (position('.' in name) = 0) THEN
-	--
-	-- view without a schema name
-	--
-	return exists (
-	   select 1 from pg_views where viewname = name);
+        --
+        -- view without a schema name
+        --
+        return exists (
+           select 1 from pg_views where viewname = name);
     ELSE
-	--
-	-- table with schema name
-	--
-	SELECT split_part(name, '.', 1) into v_schema;
-	SELECT split_part(name, '.', 2) into v_tablename;
-	return exists (
-	    select 1 from information_schema.views
-	    where table_name  = lower(v_tablename)
-	    and table_schema = v_schema);
+        --
+        -- table with schema name
+        --
+        SELECT split_part(name, '.', 1) into v_schema;
+        SELECT split_part(name, '.', 2) into v_tablename;
+        return exists (
+            select 1 from information_schema.views
+            where table_name  = lower(v_tablename)
+            and table_schema = v_schema);
     END IF;
 END;
 $$ LANGUAGE plpgsql;
@@ -179,48 +180,47 @@
 CREATE OR REPLACE FUNCTION util__index_exists(
    name text
 ) RETURNS boolean AS $$
-DECLARE
 BEGIN
       return exists (
        select 1 from pg_indexes where indexname = name);
 END;
 $$ LANGUAGE plpgsql;
 
 
--- added
-select define_function_args('util__foreign_key_exists','table,column,reftable,refcolumn');
 
 --
 -- procedure util__foreign_key_exists/4
 --
+select define_function_args('util__foreign_key_exists','table_name,column,reftable,refcolumn');
+
 CREATE OR REPLACE FUNCTION util__foreign_key_exists(
-   p_table text,
+   p_table_name text,
    p_column text,
    p_reftable text,
    p_refcolumn text
-) RETURNS boolean AS $$
-DECLARE
+) RETURNS boolean
+AS $$
 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
+         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   = lower(p_table)
-	and kcu.column_name = lower(p_column)
-	and ccu.table_name  = lower(p_reftable)
-	and ccu.column_name = lower(p_refcolumn));
+        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   = lower(p_table_name)
+        and kcu.column_name = lower(p_column)
+        and ccu.table_name  = lower(p_reftable)
+        and ccu.column_name = lower(p_refcolumn));
 END;
 $$ LANGUAGE plpgsql;
 
@@ -234,119 +234,118 @@
    p_table    text,
    p_column   text,
    p_single_p boolean default true
-) RETURNS boolean AS $$
-DECLARE
+) RETURNS boolean
+AS $$
 BEGIN
       return exists (select 1
        from
-	 information_schema.table_constraints AS tc,
-	 information_schema.key_column_usage  AS kcu
+         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   = lower(p_table)
-	and kcu.column_name = lower(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));
+        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   = lower(p_table)
+        and kcu.column_name = lower(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
+-- procedure primary_key_exists/3
 --
+select define_function_args('util__primary_key_exists','table_name,column,single_p;true');
+
 CREATE OR REPLACE FUNCTION util__primary_key_exists(
-   p_table    text,
-   p_column   text,
-   p_single_p boolean default true
-) RETURNS boolean AS $$
-DECLARE
+   p_table_name text,
+   p_column     text,
+   p_single_p   boolean default true
+) RETURNS boolean
+AS $$
 BEGIN
       return exists (select 1
        from
-	 information_schema.table_constraints AS tc,
-	 information_schema.key_column_usage  AS kcu
+         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   = lower(p_table)
-	and kcu.column_name = lower(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));
+        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   = lower(p_table_name)
+        and kcu.column_name = lower(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
 --
+select define_function_args('util__not_null_exists','table_name,column');
+
 CREATE OR REPLACE FUNCTION util__not_null_exists(
-   p_table    text,
+   p_table_name text,
    p_column   text
-) RETURNS boolean AS $$
-DECLARE
+) RETURNS boolean
+AS $$
 BEGIN
       return (
-	coalesce((
-	select is_nullable = 'NO'
-	  from information_schema.columns
-	 where table_name  = lower(p_table)
-	   and column_name = lower(p_column)), false));
+        coalesce((
+        select is_nullable = 'NO'
+          from information_schema.columns
+         where table_name  = lower(p_table_name)
+           and column_name = lower(p_column)), false));
 END;
 $$ LANGUAGE plpgsql;
 
--- added
-select define_function_args('util__get_default','table,column');
-
 --
 -- procedure util__get_default/2
 --
+select define_function_args('util__get_default','table_name,column');
+
 CREATE OR REPLACE FUNCTION util__get_default(
-   p_table    text,
+   p_table_name text,
    p_column   text
-) RETURNS information_schema.columns.column_default%TYPE AS $$
-DECLARE
+) RETURNS information_schema.columns.column_default%TYPE
+AS $$
 BEGIN
       return (
-	select column_default
-	  from information_schema.columns
-	 where table_name  = lower(p_table)
-	   and column_name = lower(p_column));
+        select column_default
+          from information_schema.columns
+         where table_name  = lower(p_table_name)
+           and column_name = lower(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(text) RETURNS SETOF pg_attribute.attname%TYPE AS $$
+select define_function_args('util__get_primary_keys','table_name');
+
+CREATE OR REPLACE FUNCTION util__get_primary_keys(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)
+                       AND a.attnum = ANY(i.indkey)
   WHERE i.indrelid = $1::regclass
     AND i.indisprimary;
 $$ LANGUAGE sql;