Skip to content

Instantly share code, notes, and snippets.

@cattaka
Created March 24, 2020 08:10
Show Gist options
  • Save cattaka/7d03117614faa1f4564bedf6fcf2d1ef to your computer and use it in GitHub Desktop.
Save cattaka/7d03117614faa1f4564bedf6fcf2d1ef to your computer and use it in GitHub Desktop.
The snippet for delete rows with temporally enable delete cascade for all tables
DROP FUNCTION IF EXISTS shrink_db;
CREATE FUNCTION shrink_db() RETURNS VOID AS $$
DECLARE
r RECORD;
BEGIN
DROP TABLE IF EXISTS work1;
CREATE TEMP TABLE work1 AS SELECT
CONCAT('ALTER TABLE ', nsp.nspname, '.', rel.relname, ' DROP CONSTRAINT "', con.conname, '";') delete_query,
CONCAT('ALTER TABLE ', nsp.nspname, '.', rel.relname, ' ADD CONSTRAINT ', con.conname, ' ', pg_get_constraintdef(con.oid), ' ON DELETE CASCADE;') create_cascade,
CONCAT('ALTER TABLE ', nsp.nspname, '.', rel.relname, ' ADD CONSTRAINT ', con.conname, ' ', pg_get_constraintdef(con.oid)) create_original,
pg_get_constraintdef(con.oid),
con.conname,
con.confdeltype,
concat(nsp.nspname, ',', rel.relname) table_name,
concat(fnsp.nspname, '.', frel.relname) f_table_name
FROM pg_catalog.pg_constraint con
INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
LEFT JOIN pg_catalog.pg_namespace nsp ON nsp.oid = rel.relnamespace
INNER JOIN pg_catalog.pg_class frel ON frel.oid = con.confrelid
LEFT JOIN pg_catalog.pg_namespace fnsp ON fnsp.oid = frel.relnamespace
WHERE contype = 'f' AND confdeltype = 'a'
ORDER BY nsp.nspname, rel.relname;
RAISE INFO 'Alter all foreign relation constraints with "ON DELETE CASCADE"';
FOR r IN SELECT delete_query, create_cascade, create_original, table_name, f_table_name FROM work1
LOOP
RAISE INFO '% -> %', r.table_name, r.f_table_name;
EXECUTE r.delete_query;
EXECUTE r.create_cascade;
END LOOP;
RAISE INFO 'Done';
RAISE INFO 'Deleting non develop account records';
-- Put codes about DELETE FROM XXXXXXXXXXXXXXXX;
RAISE INFO 'Restoring original foreign relation constraints"';
FOR r IN SELECT delete_query, create_cascade, create_original, table_name, f_table_name FROM work1
LOOP
RAISE INFO '% -> %', r.table_name, r.f_table_name;
EXECUTE r.delete_query;
EXECUTE r.create_original;
END LOOP;
RAISE INFO 'Done';
END
$$
LANGUAGE plpgsql;
SELECT * FROM shrink_db();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment