Dave Jarvis' Repositories

SELECT sql FROM (
/**
 * Data Dictionary Update Script (2 of 3)
 * @author Dave Jarvis
 *
 * Generates SQL statements to insert missing database columns
 * into the data dictionary. The column translations are also
 * populated using the name of the column. If the database column
 * names have underscores, then this code can be modified to
 * replace the underscores with spaces, and call initcap to
 * capitalize the first word prior to insertion into the data
 * dictionary.
 *
 * FILTERS
 * You can filter results isv.table_schema and isv.table_name.
 */
SELECT
  DISTINCT isc.column_name,
  'INSERT INTO as_dict.object_column (column_name, description, business_name, report_width_min, report_width_max) VALUES (''' || isc.column_name || ''','''','''',0,0);\r\n' ||
  'INSERT INTO as_dict.column_translation (object_column_id, translated_text, language_code, country_code) VALUES ((SELECT oc.object_column_id FROM as_dict.object_column oc WHERE oc.column_name = ''' || isc.column_name || '''),''' || isc.column_name || ''',''en'',''US'');\r\n'::text AS sql
FROM
  as_dict.entity_relation er,
  information_schema.columns isc
WHERE
  isc.table_schema IN ('public', 'as_ben', 'as_core', 'as_hr', 'as_pay') and
  isc.table_name = er.object_name AND
  isc.table_schema = er.object_schema AND
  isc.column_name NOT IN (
    SELECT
      oc.column_name
    FROM
      as_dict.object_column oc
  )
ORDER BY
  column_name
);