Last active
September 2, 2021 08:06
-
-
Save Nhoutain/d6a07611f7e5a32dde566eeb35a4270d to your computer and use it in GitHub Desktop.
Update flow schema based on public schema
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 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(); | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment