-- Database FK validation
SET client_min_messages=ERROR;
DROP TYPE IF EXISTS fk_info_rs CASCADE;
CREATE TYPE fk_info_rs AS
    (table_name varchar, table_col varchar, fk_table_name varchar, fk_col varchar, fk_violation varchar, fk_status integer);
CREATE OR REPLACE FUNCTION fn_db_validate_fks(v_fix_it boolean)
returns SETOF fk_info_rs
AS $procedure$
DECLARE
    v_sql text;
    v_msg text;
    v_output text;
    v_rowcount integer;
    v_record fk_info_rs%ROWTYPE;
    v_cur CURSOR FOR
    SELECT
        c.relname as table_name,
        substring(substring ((select pg_get_constraintdef(r.oid)) from '[a-zA-Z0-9_\-][(][a-zA-Z0-9_\-]+[)]') from 2) as table_col,
        c2.relname AS fk_table_name,
        substring ((select pg_get_constraintdef(r.oid)) from ' [(][a-zA-Z0-9_\-]+[)] ') as fk_col
    FROM pg_class c, pg_class c2, pg_constraint r
    WHERE c.relname in (select table_name from information_schema.tables
    where table_schema not in ('pg_catalog','information_schema') and table_type = 'BASE TABLE') AND
        r.confrelid = c.oid AND
        r.contype = 'f' AND
        c2.oid = r.conrelid AND
        pg_get_constraintdef(r.oid) not ilike '%ON DELETE SET %'
    ORDER BY  table_name;

BEGIN
    OPEN v_cur;
    LOOP
        FETCH v_cur INTO v_record;
        EXIT WHEN NOT FOUND;
        v_record.fk_violation := '';
        v_record.fk_status := 0;
        IF (v_fix_it) THEN
            v_sql := 'delete from ' || v_record.fk_table_name ||
                      ' where ' || v_record.fk_col || 'IS NOT NULL and ' || v_record.fk_col || ' not in (select ' ||
                      v_record.table_col || ' from ' || v_record.table_name || ');';
            v_msg := 'Fixing ' ||  v_record.fk_table_name || v_record.fk_col;
        ELSE
            v_sql := 'select ' || v_record.fk_col || ' from ' || v_record.fk_table_name ||
                      ' where ' || v_record.fk_col || 'IS NOT NULL and ' || v_record.fk_col || ' not in (select ' ||
                      v_record.table_col || ' from ' || v_record.table_name || ');';
            v_msg := 'Constraint violation found in  ' ||  v_record.fk_table_name || v_record.fk_col;
        END IF;
        EXECUTE v_sql;
        GET DIAGNOSTICS v_rowcount = ROW_COUNT;
        IF (v_rowcount > 0) THEN
            v_record.fk_violation := v_msg;
            v_record.fk_status := 1;
        END IF;
        RETURN NEXT v_record;

    END LOOP;
    CLOSE v_cur;
END; $procedure$
LANGUAGE plpgsql;
