Update flow schema based on public schema

flow_update_status.sql

DROP FUNCTION public.useful_flows();
DROP FUNCTION public.used_tenants();
DROP FUNCTION public.clean_useless_flows_data();

-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- Clean all useless tables flows
CREATE OR REPLACE FUNCTION public.useless_flows()
    RETURNS TABLE(table_access text) AS
$body$
SELECT table_schema || '.' || table_name as table_access
FROM information_schema.tables
WHERE
        table_name like 'flow_%'
  and
        replace(replace(table_name, 'dynamic_', ''), 'static_', '') not in (select distinct('flow_' || REPLACE(id, '-', '')) from flow_def)
  and table_schema = 'flows'


$body$
    LANGUAGE sql;

CREATE OR REPLACE FUNCTION public.clean_useless_flows()
    RETURNS void AS
$func$
DECLARE
    uselessFlow text;
BEGIN
    RAISE NOTICE 'Cleaning useless flows';
    FOR uselessFlow IN (SELECT table_access FROM public.useless_flows())
        LOOP
            RAISE NOTICE '  Cleaning useless flow table: %', uselessFlow;
            EXECUTE format('DROP TABLE %s', uselessFlow);
        END LOOP;
END
$func$ LANGUAGE plpgsql;

DROP FUNCTION public.useless_flows();
DROP FUNCTION public.clean_useless_flows();

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

CREATE OR REPLACE FUNCTION public.used_tenants()
    RETURNS TABLE(tenant text) AS
$body$
select unnest(regexp_split_to_array(tenants_entries_owner, ',')) from injection
UNION DISTINCT
SELECT tenant from injection;
$body$
    LANGUAGE sql;

CREATE OR REPLACE FUNCTION public.clean_useless_flows_data()
    RETURNS void AS
$func$
DECLARE
    table_access text;
BEGIN
    RAISE NOTICE 'Cleaning useless flows data';
    FOR table_access IN SELECT * FROM public.useful_flows()
        LOOP
            RAISE NOTICE '  Cleaning useless flow data table: %', table_access;

            EXECUTE format('DELETE FROM %s where mt not in (select tenant from public.used_tenants())', table_access);
        END LOOP;
END
$func$ LANGUAGE plpgsql;

select * from clean_useless_flows_data();