Index: openacs-4/packages/acs-kernel/acs-kernel.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/acs-kernel.info,v diff -u -N -r1.150.2.43 -r1.150.2.44 --- openacs-4/packages/acs-kernel/acs-kernel.info 21 Feb 2022 17:57:38 -0000 1.150.2.43 +++ openacs-4/packages/acs-kernel/acs-kernel.info 23 Feb 2022 18:24:53 -0000 1.150.2.44 @@ -9,15 +9,15 @@ f t - + OpenACS Core Team Routines and data models providing the foundation for OpenACS-based Web services. 2021-09-15 OpenACS The OpenACS kernel contains the core datamodel create and drop scripts for such things as objects, groups, parties and the supporting PL/SQL and PL/pgSQL procedures. 3 - + Index: openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql,v diff -u -N -r1.4.2.6 -r1.4.2.7 --- openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql 17 Feb 2022 10:36:30 -0000 1.4.2.6 +++ openacs-4/packages/acs-kernel/sql/oracle/utilities-create.sql 23 Feb 2022 18:24:53 -0000 1.4.2.7 @@ -26,8 +26,8 @@ return char; function table_column_exists ( - t_name in varchar2, - c_name in varchar2) + table_name in varchar2, + column in varchar2) return char; function view_exists ( @@ -130,8 +130,8 @@ END table_exists; function table_column_exists ( - t_name IN varchar2, - c_name IN varchar2) + table_name IN varchar2, + column IN varchar2) return char as v_exists char; @@ -140,8 +140,8 @@ select decode(count(*),0,'f','t') into v_exists from user_tab_columns - where table_name = upper(table_column_exists.t_name) - and column_name = upper(table_column_exists.c_name); + where table_name = upper(table_column_exists.table_name) + and column_name = upper(table_column_exists.column); return v_exists; Index: openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.10.1d6-5.10.1d7.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/upgrade/Attic/upgrade-5.10.1d6-5.10.1d7.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.10.1d6-5.10.1d7.sql 23 Feb 2022 18:24:53 -0000 1.1.2.1 @@ -0,0 +1,312 @@ +PROMPT starting utilities-create.sql.... +-- +-- PL/SQL utility routines for accessing schema information +-- +-- @author Jon Salz (jsalz@mit.edu), Antonio Pisano, Gustaf Neumann +-- + +create or replace TYPE t_util_primary_keys IS TABLE OF varchar2(100); +/ +show errors + + +create or replace package util +as + function multiple_nextval( + v_sequence_name in varchar2, + v_count in integer) + return varchar2; + + function logical_negation ( + true_or_false IN varchar2) + return varchar2; + + function table_exists ( + name in varchar2) + return char; + + function table_column_exists ( + table_name in varchar2, + column in varchar2) + return char; + + function view_exists ( + name in varchar2) + return char; + + function index_exists ( + name in varchar2) + return char; + + function foreign_key_exists ( + table_name IN varchar2, + column IN varchar2, + reftable IN varchar2, + refcolumn IN varchar2) + return char; + + function unique_exists ( + table_name IN varchar2, + column IN varchar2, + single_p IN boolean default true) + return char; + + function primary_key_exists ( + table_name IN varchar2, + column IN varchar2, + single_p IN boolean default true) + return char; + + function not_null_exists ( + table_name IN varchar2, + column IN varchar2) + return char; + + function get_default ( + table_name IN varchar2, + column IN varchar2) + return LONG; + + function get_primary_keys ( + table_name IN varchar2) + return t_util_primary_keys; + +end util; +/ +show errors + +create or replace package body util +as + -- Retrieves v_count (not necessarily consecutive) nextval values from the + -- sequence named v_sequence_name. + function multiple_nextval( + v_sequence_name in varchar2, + v_count in integer + ) + return varchar2 + is + a_sequence_values varchar2(4000); + begin + execute immediate ' + declare + a_nextval integer; + begin + for counter in 1..:v_count loop + select ' || v_sequence_name || '.nextval into a_nextval from dual; + :a_sequence_values := :a_sequence_values || '','' || a_nextval; + end loop; + end; + ' using in v_count, in out a_sequence_values; + return substr(a_sequence_values, 2); + end; + + function logical_negation ( + true_or_false IN varchar2) + return varchar2 + as + begin + IF true_or_false is null THEN + return null; + ELSIF true_or_false = 'f' THEN + return 't'; + ELSE + return 'f'; + END IF; + END logical_negation; + + function table_exists ( + name IN varchar2) + return char + as + v_exists char; + + begin + + select decode(count(*),0,'f','t') into v_exists + from user_tables where table_name = upper(table_exists.name); + + return v_exists; + + END table_exists; + + function table_column_exists ( + table_name IN varchar2, + column IN varchar2) + return char + as + v_exists char; + + begin + + select decode(count(*),0,'f','t') into v_exists + from user_tab_columns + where table_name = upper(table_column_exists.table_name) + and column_name = upper(table_column_exists.column); + + return v_exists; + + END table_column_exists; + + function view_exists ( + name IN varchar2) + return char + as + v_exists char; + + begin + + select decode(count(*),0,'f','t') into v_exists + from user_views where view_name = upper(view_exists.name); + + return v_exists; + + END view_exists; + + function index_exists ( + name IN varchar2) + return char + as + v_exists char; + + begin + select decode(count(*),0,'f','t') into v_exists + from user_indexes where index_name = upper(index_exists.name); + + return v_exists; + END index_exists; + + function foreign_key_exists ( + table_name IN varchar2, + column IN varchar2, + reftable IN varchar2, + refcolumn IN varchar2) + return char + as + v_exists char; + + begin + select decode(count(*),0,'f','t') into v_exists + from user_constraints cons + left join user_cons_columns cols on cols.constraint_name = cons.constraint_name + left join user_constraints cons_r on cons_r.constraint_name = cons.r_constraint_name + left join user_cons_columns cols_r on cols_r.constraint_name = cons.r_constraint_name + where cons.constraint_type = 'R' + and cons.table_name = upper(foreign_key_exists.table_name) + and cols.column_name = upper(foreign_key_exists.column) + and cons_r.table_name = upper(foreign_key_exists.reftable) + and cols_r.column_name = upper(foreign_key_exists.refcolumn); + + return v_exists; + end foreign_key_exists; + + function unique_exists ( + table_name IN varchar2, + column IN varchar2, + single_p IN boolean default true) + return char + as + v_exists char; + v_single integer; + + begin + v_single := case when unique_exists.single_p then 1 else 0 end; + + select decode(count(*),0,'f','t') into v_exists + from all_constraints c + join all_cons_columns cc on (c.owner = cc.owner + and c.constraint_name = cc.constraint_name) + where c.constraint_type = 'U' + and c.table_name = upper(unique_exists.table_name) + and cc.column_name = upper(unique_exists.column) + and ((v_single = 0) or ( + select count(*) from all_cons_columns + where constraint_name = c.constraint_name) = 1); + + return v_exists; + END unique_exists; + + function primary_key_exists ( + table_name IN varchar2, + column IN varchar2, + single_p IN boolean default true) + return char + as + v_exists char; + v_single integer; + + begin + v_single := case when primary_key_exists.single_p then 1 else 0 end; + + select decode(count(*),0,'f','t') into v_exists + from all_constraints c + join all_cons_columns cc on (c.owner = cc.owner + and c.constraint_name = cc.constraint_name) + where c.constraint_type = 'P' + and c.table_name = upper(primary_key_exists.table_name) + and cc.column_name = upper(primary_key_exists.column) + and ((v_single = 0) or ( + select count(*) from all_cons_columns + where constraint_name = c.constraint_name + and owner = c.owner) = 1); + + return v_exists; + END primary_key_exists; + + function not_null_exists ( + table_name IN varchar2, + column IN varchar2) + return char + as + v_exists char; + + begin + select decode(count(*),0,'f','t') into v_exists + from all_tab_columns + where table_name = upper(not_null_exists.table_name) + and column_name = upper(not_null_exists.column) + and nullable = 'N'; + + return v_exists; + END not_null_exists; + + function get_default ( + table_name in varchar2, + column in varchar2) + return long + as + v_value long; + + begin + select data_default into v_value + from all_tab_columns + where table_name = upper(get_default.table_name) + and column_name = upper(get_default.column); + + return v_value; + + exception when no_data_found then + return null; + end get_default; + + function get_primary_keys( + table_name in varchar2) + return t_util_primary_keys + as + v_ret t_util_primary_keys; + + begin + select cols.column_name + bulk collect into v_ret + from all_constraints cons, all_cons_columns cols + where cols.table_name = upper(get_primary_keys.table_name) + and cons.constraint_type = 'P' + and cons.constraint_name = cols.constraint_name + and cons.owner = cols.owner + order by cols.table_name, cols.position; + + return v_ret; + end get_primary_keys; + +end util; +/ +show errors 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 -N -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; Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.10.1d6-5.10.1d7.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/Attic/upgrade-5.10.1d6-5.10.1d7.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.10.1d6-5.10.1d7.sql 23 Feb 2022 18:24:53 -0000 1.1.2.1 @@ -0,0 +1,167 @@ +-- +-- since argument names change, we have to drop + recreate +-- +DROP FUNCTION util__table_column_exists(text, text); + +-- +-- procedure util__table_column_exists/2 +-- +select define_function_args('util__table_column_exists','table_name,column'); + +CREATE OR REPLACE FUNCTION util__table_column_exists( + p_table_name text, + p_column text +) RETURNS boolean +AS $$ +DECLARE + v_schema varchar; + v_tablename varchar; +BEGIN + 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_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; + + +DROP FUNCTION util__get_default(text, text); +select define_function_args('util__get_default','table_name,column'); + +CREATE OR REPLACE FUNCTION util__get_default( + p_table_name text, + p_column text +) RETURNS information_schema.columns.column_default%TYPE +AS $$ +BEGIN + return ( + select column_default + from information_schema.columns + where table_name = lower(p_table_name) + and column_name = lower(p_column)); +END; +$$ LANGUAGE plpgsql; + +-- +-- procedure util__get_primary_keys/1 +-- +select define_function_args('util__get_primary_keys','table_name'); + + + +-- +-- procedure util__foreign_key_exists/4 +-- +DROP FUNCTION util__foreign_key_exists(text,text,text,text); +select define_function_args('util__foreign_key_exists','table_name,column,reftable,refcolumn'); + +CREATE OR REPLACE FUNCTION util__foreign_key_exists( + p_table_name text, + p_column text, + p_reftable text, + p_refcolumn text +) 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 + 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_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; + + + + +-- +-- procedure util__not_null_exists/2 +-- +DROP FUNCTION util__not_null_exists(text,text); + +select define_function_args('util__not_null_exists','table_name,column'); + +CREATE OR REPLACE FUNCTION util__not_null_exists( + p_table_name text, + p_column text +) RETURNS boolean +AS $$ +DECLARE +BEGIN + return ( + 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; + + + +-- +-- procedure primary_key_exists/3 +-- +DROP FUNCTION util__primary_key_exists(text,text,boolean); +select define_function_args('util__primary_key_exists','table_name,column,single_p;true'); + +CREATE OR REPLACE FUNCTION util__primary_key_exists( + p_table_name 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 = 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;