Last active
September 2, 2021 08:10
-
-
Save Nhoutain/326bc03bd7c1f2b067b221631cf51e31 to your computer and use it in GitHub Desktop.
Fix flow numeric in text ('1.00' -> '1')
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment