79 lines
2.5 KiB
SQL
79 lines
2.5 KiB
SQL
-- ===================================================================
|
|
-- 数据库清理和重建脚本
|
|
-- 用于解决索引和表已存在的问题
|
|
-- 在执行主数据库脚本前运行此脚本
|
|
-- ===================================================================
|
|
|
|
-- 设置客户端编码
|
|
SET client_encoding = 'UTF8';
|
|
|
|
-- 删除可能已存在的索引
|
|
DO $$
|
|
DECLARE
|
|
r RECORD;
|
|
BEGIN
|
|
-- 删除所有以 idx_ 开头的索引
|
|
FOR r IN (SELECT indexname FROM pg_indexes WHERE indexname LIKE 'idx_%' AND schemaname = 'public')
|
|
LOOP
|
|
EXECUTE 'DROP INDEX IF EXISTS public.' || quote_ident(r.indexname);
|
|
RAISE NOTICE '删除索引: %', r.indexname;
|
|
END LOOP;
|
|
|
|
RAISE NOTICE '所有相关索引已清理完成！';
|
|
END $$;
|
|
|
|
-- 删除可能已存在的触发器
|
|
DO $$
|
|
DECLARE
|
|
r RECORD;
|
|
BEGIN
|
|
FOR r IN (SELECT trigger_name, event_object_table
|
|
FROM information_schema.triggers
|
|
WHERE trigger_schema = 'public'
|
|
AND trigger_name LIKE 'trigger_%')
|
|
LOOP
|
|
EXECUTE 'DROP TRIGGER IF EXISTS ' || quote_ident(r.trigger_name) || ' ON public.' || quote_ident(r.event_object_table);
|
|
RAISE NOTICE '删除触发器: % on %', r.trigger_name, r.event_object_table;
|
|
END LOOP;
|
|
|
|
RAISE NOTICE '所有相关触发器已清理完成！';
|
|
END $$;
|
|
|
|
-- 删除可能已存在的函数
|
|
DO $$
|
|
DECLARE
|
|
r RECORD;
|
|
BEGIN
|
|
FOR r IN (SELECT proname, oidvectortypes(proargtypes) as args
|
|
FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid
|
|
WHERE n.nspname = 'public'
|
|
AND proname IN ('update_favorite_folder_count', 'update_content_favorite_stats',
|
|
'update_content_share_stats', 'generate_share_code', 'update_share_analytics'))
|
|
LOOP
|
|
EXECUTE 'DROP FUNCTION IF EXISTS public.' || quote_ident(r.proname) || '(' || r.args || ')';
|
|
RAISE NOTICE '删除函数: %', r.proname;
|
|
END LOOP;
|
|
|
|
RAISE NOTICE '所有相关函数已清理完成！';
|
|
END $$;
|
|
|
|
-- 可选：删除可能已存在的表（谨慎使用！）
|
|
/*
|
|
DO $$
|
|
DECLARE
|
|
r RECORD;
|
|
BEGIN
|
|
FOR r IN (SELECT tablename FROM pg_tables
|
|
WHERE schemaname = 'public'
|
|
AND tablename LIKE 'ak_%')
|
|
LOOP
|
|
EXECUTE 'DROP TABLE IF EXISTS public.' || quote_ident(r.tablename) || ' CASCADE';
|
|
RAISE NOTICE '删除表: %', r.tablename;
|
|
END LOOP;
|
|
|
|
RAISE NOTICE '所有相关表已删除！';
|
|
END $$;
|
|
*/
|
|
|
|
RAISE NOTICE '数据库清理完成！现在可以运行主数据库脚本了。';
|