107 lines
5.8 KiB
SQL
107 lines
5.8 KiB
SQL
--if the data is already cleansed is it necessary to even have master data tables? -> yes for adding new scenarios
|
|
--it is possible some parts not longer exist in the item master becuase they have since been deleted, so it is not possible to cleanse the data
|
|
do
|
|
$$
|
|
DECLARE
|
|
f record;
|
|
_sql text;
|
|
_target_table text;
|
|
|
|
BEGIN
|
|
SELECT format('%I',max(schema))||'.'||format('%I',max(tname)) INTO _target_table FROM fc.target_meta;
|
|
FOR f IN
|
|
SELECT
|
|
tname,
|
|
func,
|
|
-------------------------------------------create table---------------------------------------------------------------------------------------------------------
|
|
'DROP TABLE IF EXISTS fc.'||func||' CASCADE; CREATE TABLE IF NOT EXISTS fc.'||func||' (' ||
|
|
string_agg(format('%I',cname) || ' ' || dtype,', ' ORDER BY CASE WHEN fkey IS NOT NULL THEN 0 ELSE opos END ASC) ||
|
|
', PRIMARY KEY ('||string_agg(format('%I',cname),', ') FILTER (WHERE COALESCE(fkey,'') <> '')||'));' AS ddl,
|
|
-------------------------------------------populate table-------------------------------------------------------------------------------------------------------
|
|
---need to add a clause to exclude where the key is null
|
|
'INSERT INTO fc.'||func||' SELECT DISTINCT ' || string_agg(format('%I',cname),', ' ORDER BY CASE WHEN fkey IS NOT NULL THEN 0 ELSE opos END ASC) || ' FROM '||schema||'.'||tname||' WHERE '||
|
|
string_agg(format('%I',cname)||' IS NOT NULL ',' AND ') FILTER (WHERE COALESCE(fkey,'') <> '')||' ON CONFLICT DO NOTHING' AS pop,
|
|
-------------------------------------------setup foreign keys---------------------------------------------------------------------------------------------------
|
|
'ALTER TABLE '||tm.schema||'.'||tm.tname||' ADD CONSTRAINT fk_'||func||' FOREIGN KEY ('||string_agg(format('%I',cname),', ') FILTER (WHERE COALESCE(fkey,'') <> '')||') REFERENCES fc.'||func||' ('||
|
|
string_agg(format('%I',cname),', ') FILTER (WHERE COALESCE(fkey,'') <>'')||')' AS fk
|
|
FROM
|
|
fc.target_meta tm
|
|
WHERE
|
|
--only create master tables that have a foreign key (that isn't a date)
|
|
func IN (SELECT DISTINCT func FROM fc.target_meta WHERE fkey IS NOT NULL AND func IS NOT NULL AND dtype <> 'date')
|
|
GROUP BY
|
|
schema
|
|
,tname
|
|
,func
|
|
--HAVING
|
|
-- string_agg(cname,', ') FILTER (WHERE fkey = func) <> ''
|
|
loop
|
|
INSERT INTO fc.sql SELECT f.func, f.ddl ON CONFLICT ON CONSTRAINT sql_pkey DO UPDATE SET t = EXCLUDED.t;
|
|
EXECUTE format('%s',f.ddl);
|
|
EXECUTE format('%s',f.pop);
|
|
EXECUTE format('%s',f.fk);
|
|
END LOOP;
|
|
-------add a units column if one doesn't exist--------------------------------
|
|
IF (SELECT COUNT(*) FROM fc.target_meta WHERE appcol = 'units') = 0 THEN
|
|
SELECT 'ALTER TABLE '||_target_table||' ADD COLUMN app_units numeric DEFAULT 0' INTO _sql;
|
|
EXECUTE format('%s',_sql);
|
|
--insert the newly created row meta into target_meta
|
|
INSERT INTO
|
|
fc.target_meta
|
|
SELECT
|
|
--schema tname cname opos func fkey fcol dtype appcol pretty
|
|
table_schema, table_name, column_name, ordinal_position, null::text, null::text, null::text, 'numeric' , 'units', null::text
|
|
FROM
|
|
information_schema.columns
|
|
WHERE
|
|
format('%I',table_schema)||'.'||format('%I',table_name) = _target_table
|
|
AND column_name = 'app_units';
|
|
END IF;
|
|
-------add a version column if one doesn't exist--------------------------------
|
|
IF (SELECT COUNT(*) FROM fc.target_meta WHERE appcol = 'version') = 0 THEN
|
|
SELECT 'ALTER TABLE '||_target_table||' ADD COLUMN app_version text' INTO _sql;
|
|
EXECUTE format('%s',_sql);
|
|
--insert the newly created row meta into target_meta
|
|
INSERT INTO
|
|
fc.target_meta
|
|
SELECT
|
|
table_schema, table_name, column_name, ordinal_position, null::text, null::text, null::text, 'text', 'version', null::text
|
|
FROM
|
|
information_schema.columns
|
|
WHERE
|
|
format('%I',table_schema)||'.'||format('%I',table_name) = _target_table
|
|
AND column_name = 'app_version';
|
|
END IF;
|
|
-------add a iter column if one doesn't exist--------------------------------
|
|
IF (SELECT COUNT(*) FROM fc.target_meta WHERE appcol = 'iter') = 0 THEN
|
|
SELECT 'ALTER TABLE '||_target_table||' ADD COLUMN app_iter text' INTO _sql;
|
|
EXECUTE format('%s',_sql);
|
|
--insert the newly created row meta into target_meta
|
|
INSERT INTO
|
|
fc.target_meta
|
|
SELECT
|
|
table_schema, table_name, column_name, ordinal_position, null::text, null::text, null::text, 'text' , 'iter', null::text
|
|
FROM
|
|
information_schema.columns
|
|
WHERE
|
|
format('%I',table_schema)||'.'||format('%I',table_name) = _target_table
|
|
AND column_name = 'app_iter';
|
|
END IF;
|
|
-------add a logid column if one doesn't exist--------------------------------
|
|
IF (SELECT COUNT(*) FROM fc.target_meta WHERE appcol = 'logid') = 0 THEN
|
|
SELECT 'ALTER TABLE '||_target_table||' ADD COLUMN app_logid text' INTO _sql;
|
|
EXECUTE format('%s',_sql);
|
|
--insert the newly created row meta into target_meta
|
|
INSERT INTO
|
|
fc.target_meta
|
|
SELECT
|
|
table_schema, table_name, column_name, ordinal_position, null::text, null::text, null::text, 'bigint' , 'logid', null::text
|
|
FROM
|
|
information_schema.columns
|
|
WHERE
|
|
format('%I',table_schema)||'.'||format('%I',table_name) = _target_table
|
|
AND column_name = 'app_logid';
|
|
END IF;
|
|
END;
|
|
$$
|