Dave Jarvis' Repositories

SELECT sql FROM (
/**
 * Data Dictionary Update Script (1 of 3)
 * @author Dave Jarvis
 *
 * Generate SQL statements to update data dictionary tables and views.
 * The generated SQL also contains SQL to delete columns from the data
 * dictionary that have been dropped.
 *
 * FILTERS
 * You can filter results isv.table_schema and isv.table_name.
 */
SELECT
  er.entity_relation_id,
  er.object_schema,
  er.object_name,
  er.object_type,
  'delete'::text AS crud,
  -- Delete rows (in reverse-dependency order) that no longer exist.
  'DELETE FROM as_dict.column_cluster_column_relation WHERE object_column_entity_relation_id IN (SELECT object_column_entity_relation_id FROM as_dict.object_column_entity_relation ocer WHERE ocer.entity_relation_id = ' || er.entity_relation_id || ');\r\n' ||
  'DELETE FROM as_dict.object_column_entity_relation WHERE entity_relation_id = ' || er.entity_relation_id || ';\r\n' ||
  'DELETE FROM as_dict.entity_relation WHERE entity_relation_id = ' || er.entity_relation_id || ';'::text AS sql
FROM
  as_dict.entity_relation er
WHERE
  er.object_name NOT IN (
    SELECT
      isv.table_name AS object_name
    FROM
      information_schema.tables isv
    WHERE
      isv.table_schema IN ('public', 'as_ben', 'as_core', 'as_hr', 'as_pay') AND
      isv.table_type IN ('BASE TABLE', 'VIEW') AND
      isv.table_name NOT ILIKE 'ips%' AND
      isv.table_name NOT ILIKE 'eps%' AND
      isv.table_name NOT ILIKE 'iskr%'
  )
UNION ALL
-- Create rows in the data dictionary for new tables and views.
SELECT
  0 AS entity_relation_id,
  isv.table_schema AS object_schema,
  isv.table_name AS object_name,
  CASE isv.table_type WHEN 'BASE TABLE' THEN 'TABLE' ELSE isv.table_type END AS object_type,
  'insert'::text AS crud,
  'INSERT INTO as_dict.entity_relation (object_schema, object_name, object_type, business_name) VALUES (''' || isv.table_schema || ''',''' || isv.table_name || ''',''' || CASE isv.table_type WHEN 'BASE TABLE' THEN 'TABLE' ELSE isv.table_type END || ''','''');\r\n'::text AS sql
FROM
  information_schema.tables isv
WHERE
  isv.table_schema IN ('public', 'as_ben', 'as_core', 'as_hr', 'as_pay') AND
  isv.table_type IN ('BASE TABLE', 'VIEW') AND
  isv.table_name NOT ILIKE 'ips%' AND
  isv.table_name NOT ILIKE 'eps%' AND
  isv.table_name NOT ILIKE 'iskr%' AND
  isv.table_name NOT IN (
    SELECT
      er.object_name
    FROM
      as_dict.entity_relation er
  )
ORDER BY
  object_type, object_name
);