Copy SQL

CREATE OR REPLACE FUNCTION f_default_value_with_no_match()
RETURNS TABLE 
(
foreign_ns text,
foreign_table text,
foreign_colname text,
target_ns text,
target_table text,
target_colname text,
target_default text
) AS $$
DECLARE
sql_stmt TEXT;
cnt BIGINT;
rslt TEXT:='';
fks RECORD;
BEGIN
RAISE NOTICE 'Detecting foreign key columns that have a default value that does not have a matching value in the
key of the primary table.';
FOR fks IN (WITH d AS (SELECT A.table_schema::text, A.table_name::text, A.column_name::text, A.column_default::text
FROM information_schema.columns A
INNER JOIN information_schema.tables T
ON A.table_schema = T.table_schema
AND A.table_name = T.table_name
INNER JOIN information_schema.schemata S
ON A.table_schema=S.schema_name
WHERE column_default IS NOT NULL 
AND T.table_type='BASE TABLE' AND domain_name IS NULL
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')
UNION SELECT A.table_schema, A.table_name , A.column_name, D.domain_default
FROM information_schema.columns A
INNER JOIN information_schema.tables T
ON A.table_schema = T.table_schema
AND A.table_name = T.table_name
INNER JOIN information_schema.schemata S
ON A.table_schema=S.schema_name
INNER JOIN information_schema.domains D
ON A.domain_schema = D.domain_schema
AND A.domain_name = D.domain_name
WHERE T.table_type='BASE TABLE'
AND (A.table_schema = 'public'
OR S.schema_owner<>'postgres')),
fk AS (select (select nspname from pg_namespace where oid=f.relnamespace)::text as foreign_ns,
f.relname::text as foreign_table,
(select a.attname from pg_attribute a where a.attrelid = f.oid and a.attnum = o.confkey[1] and a.attisdropped = false)::text as foreign_colname,
(select nspname from pg_namespace where oid=c.relnamespace)::text as target_ns,
c.relname::text as target_table,
(select a.attname from pg_attribute a where a.attrelid = c.oid and a.attnum = o.conkey[1] and a.attisdropped = false)::text as target_colname 
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_class f on f.oid = o.confrelid
where o.contype = 'f')
SELECT fk.*, regexp_replace(regexp_replace(d.column_default,'::.*',''), '^''(.*)''$', E'\\1', 'g') AS target_default
FROM fk, d
WHERE fk.target_ns=d.table_schema AND
fk.target_table=d.table_name AND
fk.target_colname=d.column_name ORDER BY foreign_ns, foreign_table, target_ns, target_table) LOOP
sql_stmt:='SELECT Count(' || quote_ident(fks.foreign_colname) || ') AS c 
FROM ' || quote_ident(fks.foreign_ns) ||'.' || quote_ident(fks.foreign_table) || ' WHERE '||
quote_ident(fks.foreign_colname) || '::text=' || quote_literal(fks.target_default) || '::text';
IF sql_stmt IS NOT NULL THEN
EXECUTE sql_stmt INTO cnt;
IF cnt=0 THEN
foreign_ns:=fks.foreign_ns;
foreign_table:=fks.foreign_table;
foreign_colname:=fks.foreign_colname;
target_ns:=fks.target_ns;
target_table:=fks.target_table;
target_colname:=fks.target_colname;
target_default:=fks.target_default;
RETURN NEXT;
END IF;
END IF;
END LOOP;
RAISE NOTICE 'Detection completed';
RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER
SET search_path = information_schema, pg_temp;
SELECT * FROM f_default_value_with_no_match();

DROP FUNCTION IF EXISTS f_default_value_with_no_match();