Deleting a domain through postgres function

[agree]

I would like to share how you can simply remove a domain from the system with this simple function.

CREATE OR REPLACE FUNCTION delete_domain(id UUID)
RETURNS void AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT table_schema, table_name
        FROM information_schema.columns
        JOIN information_schema.tables using(table_schema, table_name)
        WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
        AND table_schema = 'public' AND table_name like 'v_%' AND column_name = 'domain_uuid' AND table_name <> 'v_domains'
    LOOP
        RAISE NOTICE 'deleting % for %', quote_ident(r.table_schema) || '.' || quote_ident(r.table_name), id;
        EXECUTE format('DELETE FROM %I.%I WHERE domain_uuid = $1', r.table_schema, r.table_name) USING id;
    END LOOP;
    
    EXECUTE 'DELETE FROM public.v_domains WHERE domain_uuid = $1' USING id;
END;
$$ LANGUAGE plpgsql;

You'll still need to remove the recordings and voicemails.

 

[agree]

If fusion had used domain_uuid with a foreign key reference to v_domains with ON DELETE CASCADE, removing domains would require deleting just a single row.

 

[pbxgeek]

CASCADE requires special handling when the database is restored. For an easier restoration process, developers of FusionPBX opted not to have any CASCADE records. At least this is my best guess as to why they did it.

Very good script by the way. Thank you for sharing.

 

[whut]

That is nice PL SQL agree. But pressing the delete domain(s) button in the fusionpbx GUI will perform all of the deletes for you.O do not remember if it also deletes the recordings, messages, and others. It has been a long time since I have needed to remove a domain.

 

[agree]

That is nice PL SQL agree. But pressing the delete domain(s) button in the fusionpbx GUI will perform all of the deletes for you.O do not remember if it also deletes the recordings, messages, and others. It has been a long time since I have needed to remove a domain.

Of course, you can do it from the GUI. I just demonstrated how to do it using a PL/SQL function. In fact, it's actually faster than using the UI (at least in the version I'm running)

 

[whut]

nicely done. This could easily be adapted for other purposes too.

 

[whut]

I removed the duplicate `AND table_schema = 'public'` in your WHERE clause in the below.

CREATE OR REPLACE FUNCTION delete_domain(id UUID)
RETURNS void AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT table_schema, table_name
        FROM information_schema.columns
        JOIN information_schema.tables using(table_schema, table_name)
        WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
        AND table_name like 'v_%' AND column_name = 'domain_uuid' AND table_name <> 'v_domains'
    LOOP
        RAISE NOTICE 'deleting % for %', quote_ident(r.table_schema) || '.' || quote_ident(r.table_name), id;
        EXECUTE format('DELETE FROM %I.%I WHERE domain_uuid = $1', r.table_schema, r.table_name) USING id;
    END LOOP;
    
    EXECUTE 'DELETE FROM public.v_domains WHERE domain_uuid = $1' USING id;
END;
$$ LANGUAGE plpgsql;