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.8.2.1 --- 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 20 Dec 2016 20:28:49 -0000 1.8.2.1 @@ -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,151 @@ $$ 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;