Fix flow numeric in text ('1.00' -> '1')

flow_numeric.sql

DROP FUNCTION IF EXISTS public.flow_business_key(table_name TEXT);
DROP FUNCTION IF EXISTS public.flows();
DROP FUNCTION IF EXISTS public.get_flows_numeric_in_text_data();
DROP FUNCTION IF EXISTS public.fix_flow_numeric_in_text_data(table_access text, fix boolean);
DROP FUNCTION IF EXISTS public.fix_flow_column_numeric_in_text_data(table_access TEXT, col text, fix boolean );

-- ----------------------------------------------------------------------
-- FLOWS
-- ----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION public.flow_business_key(table_name TEXT)
    RETURNS TABLE(bkey text[]) AS
$body$
SELECT ('{'||(regexp_matches(indexdef, '\((.*), mt, mvf\)'))[1]||'}')::text[]
FROM pg_indexes indexes
WHERE indexes.tablename = $1  and indexname like '%_bkey'
$body$
    LANGUAGE sql;

CREATE OR REPLACE FUNCTION public.flows()
    RETURNS TABLE(table_access text, bkey text[]) AS
$body$
SELECT table_schema || '.' || table_name as table_access, flow_business_key(table_name) as bkey
FROM information_schema.tables
WHERE table_name in (select distinct('flow_' || REPLACE(flow_id, '-', '')) from injection where status = 'DONE')
$body$
    LANGUAGE sql;


-- ----------------------------------------------------------------------
-- FIND NUMERIC IN TEXT
-- ----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION public.get_flows_numeric_in_text_data()
    RETURNS TABLE(table_access text, mid varchar(10000), col text) AS
$func$
DECLARE
    rec record;
    counter INTEGER := 0 ;
    total INTEGER := 0 ;
BEGIN
    total := (SELECT count(*)::INTEGER FROM flows());

    RAISE NOTICE 'Checking numeric in text on all tables [%]', total;
    FOR rec IN SELECT * FROM flows()
        LOOP
            counter := counter + 1 ;
            RAISE NOTICE '  [%/%] ...', counter, total;

            RETURN QUERY EXECUTE format(
                    'SELECT %s, id, col FROM public.get_flow_numeric_in_text_data(%s)',
                    quote_literal(rec.table_access), quote_literal(rec.table_access));
        END LOOP;
END
$func$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.get_flow_numeric_in_text_data(table_access TEXT)
    RETURNS TABLE(id varchar(10000), col text) AS
$func$
DECLARE
    col text;
    not_numeric_values bigint;
BEGIN
    FOR col in EXECUTE format('SELECT col FROM public.flow_text_column(%s)', quote_literal(table_access))
        LOOP
            EXECUTE format('SELECT count(distinct "%s") FROM %s where "%s" !~ ''^-?[0-9]+\.?[0-9]{0,5}$'' ', col, table_access, col)
                INTO not_numeric_values;

            IF not_numeric_values = 0::bigint THEN
                RAISE NOTICE '  Checking numeric in text on table [%] for column [%]', table_access, col;
                RETURN QUERY EXECUTE format('SELECT mid, %s FROM %s WHERE "%s" like ''%%.0''', quote_literal(col), table_access, col);
            ELSE
            END IF;
        END LOOP;
END
$func$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.flow_text_column(TEXT)
    RETURNS TABLE(col TEXT) AS
$body$
SELECT column_name::text
FROM information_schema.columns
WHERE table_schema = 'flows'
  AND table_name   = REPLACE($1, 'flows.', '')
  AND data_type = 'character varying'
$body$
    LANGUAGE sql;

-- ----------------------------------------------------------------------
-- FIX NUMERIC IN TEXT
-- ----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION public.fix_flows_numeric_in_text_data(fix boolean DEFAULT false)
    RETURNS TABLE(table_access text, mid varchar(10000), col text, old_value varchar(10000), new_value varchar(10000)) AS
$func$
DECLARE
    rec record;
    counter INTEGER := 0 ;
    total INTEGER := 0 ;
BEGIN
    total := (SELECT count(*)::INTEGER FROM flows());

    RAISE NOTICE 'Fixing numeric in text on all tables [%]', total;
    FOR rec IN SELECT * FROM flows()
        LOOP
            counter := counter + 1 ;
            RAISE NOTICE '  [%/%] ...', counter, total;

            RETURN QUERY EXECUTE format(
                    'SELECT %s, mid, col, old_value, new_value FROM public.fix_flow_numeric_in_text_data(%s, %s)',
                    quote_literal(rec.table_access), quote_literal(rec.table_access), quote_literal(fix));
        END LOOP;
END
$func$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.fix_flow_numeric_in_text_data(table_access text, fix boolean DEFAULT false)
    RETURNS TABLE(mid varchar(10000), col text, old_value varchar(10000), new_value varchar(10000)) AS
$func$
DECLARE
    col text;
BEGIN
    FOR col IN EXECUTE format('SELECT distinct col FROM public.get_flow_numeric_in_text_data(%s)', quote_literal(table_access))
        LOOP
            RETURN QUERY EXECUTE format(
                    'SELECT id, %s, old_value, new_value FROM public.fix_flow_column_numeric_in_text_data(%s, %s, %s)',
                    quote_literal(col), quote_literal(table_access), quote_literal(col), quote_literal(fix));
        END LOOP;
END
$func$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION public.fix_flow_column_numeric_in_text_data(table_access TEXT, col text, fix boolean DEFAULT false)
    RETURNS TABLE(id varchar(10000), old_value varchar(10000), new_value varchar(10000)) AS
$func$
DECLARE
BEGIN
    RAISE NOTICE '  Fixing numeric in text on table [%] for col [%]', table_access, col;
    IF fix THEN
        RETURN QUERY EXECUTE format(
                'UPDATE %s x '
                    ' SET %s = trim(trailing ''.'' FROM trim(trailing ''00'' FROM y."%s"::numeric(19, 5)::varchar(10000)))::varchar(10000)'
                    ' FROM %s y'
                    ' WHERE x.mid = y.mid'
                    ' RETURNING x.mid AS id, y."%s" AS old_value, x."%s" AS new_value',
                table_access, col, col, table_access, col, col);
    ELSE
        RETURN QUERY EXECUTE format(
                ' SELECT y.mid, y."%s", trim(trailing ''.'' FROM trim(trailing ''00'' FROM y."%s"::numeric(19, 5)::varchar(10000)))::varchar(10000)'
                    ' FROM %s y',
                col, col, table_access);
    END IF;
END
$func$ LANGUAGE plpgsql;