138 lines
5.2 KiB
SQL
138 lines
5.2 KiB
SQL
-- =========================================================
|
||
-- exchange.sql 🏛️
|
||
-- 无物化视图 | 超可视提示 | 可重复执行
|
||
-- PostgreSQL 17.4+ 👍
|
||
-- =========================================================
|
||
|
||
-- 1️⃣ 开始 🚀
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '🚀============ exchange 部署开始 ============🚀';
|
||
END $$;
|
||
|
||
-- 2️⃣ 连接目标库
|
||
\c postgres;
|
||
|
||
-- 3️⃣ 表结构 -----------------------------------
|
||
DO $$
|
||
BEGIN
|
||
-- exchange 主表
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'exchange') THEN
|
||
CREATE TABLE exchange (
|
||
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
||
deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
CREATE TRIGGER update_exchange_updated_at
|
||
BEFORE UPDATE ON exchange
|
||
FOR EACH ROW EXECUTE FUNCTION update_data_modified_column();
|
||
RAISE NOTICE '1️⃣✅ exchange 主表已创建';
|
||
ELSE
|
||
RAISE NOTICE '1️⃣⏩ exchange 主表已存在，跳过';
|
||
END IF;
|
||
|
||
-- exchange_name
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'exchange_name') THEN
|
||
CREATE TABLE exchange_name (
|
||
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
||
exchange_id UUID NOT NULL,
|
||
name VARCHAR NOT NULL,
|
||
deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
CREATE TRIGGER update_exchange_name_updated_at
|
||
BEFORE UPDATE ON exchange_name
|
||
FOR EACH ROW EXECUTE FUNCTION update_data_modified_column();
|
||
RAISE NOTICE '2️⃣✅ exchange_name 子表已创建';
|
||
ELSE
|
||
RAISE NOTICE '2️⃣⏩ exchange_name 子表已存在，跳过';
|
||
END IF;
|
||
|
||
-- exchange_short_name
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'exchange_short_name') THEN
|
||
CREATE TABLE exchange_short_name (
|
||
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
||
exchange_id UUID NOT NULL,
|
||
short_name VARCHAR NOT NULL,
|
||
deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
CREATE TRIGGER update_exchange_short_name_updated_at
|
||
BEFORE UPDATE ON exchange_short_name
|
||
FOR EACH ROW EXECUTE FUNCTION update_data_modified_column();
|
||
RAISE NOTICE '3️⃣✅ exchange_short_name 子表已创建';
|
||
ELSE
|
||
RAISE NOTICE '3️⃣⏩ exchange_short_name 子表已存在，跳过';
|
||
END IF;
|
||
|
||
-- exchange_code
|
||
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'exchange_code') THEN
|
||
CREATE TABLE exchange_code (
|
||
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
||
exchange_id UUID NOT NULL,
|
||
code VARCHAR NOT NULL,
|
||
deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
CREATE TRIGGER update_exchange_code_updated_at
|
||
BEFORE UPDATE ON exchange_code
|
||
FOR EACH ROW EXECUTE FUNCTION update_data_modified_column();
|
||
RAISE NOTICE '4️⃣✅ exchange_code 子表已创建';
|
||
ELSE
|
||
RAISE NOTICE '4️⃣⏩ exchange_code 子表已存在，跳过';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- 4️⃣ 视图 ------------------------------------
|
||
DO $$
|
||
DECLARE
|
||
view_exists BOOLEAN;
|
||
BEGIN
|
||
SELECT EXISTS (
|
||
SELECT 1 FROM information_schema.views
|
||
WHERE table_name = 'exchange_info_view'
|
||
) INTO view_exists;
|
||
|
||
IF view_exists THEN
|
||
DROP VIEW exchange_info_view;
|
||
RAISE NOTICE '4️⃣♻️ 已删除旧视图 exchange_info_view';
|
||
END IF;
|
||
|
||
CREATE OR REPLACE VIEW exchange_info_view AS
|
||
SELECT
|
||
u.id AS exchange_id,
|
||
n.name,
|
||
sn.short_name,
|
||
c.code,
|
||
u.deleted
|
||
FROM exchange u
|
||
JOIN exchange_name n ON u.id = n.exchange_id AND n.deleted = FALSE
|
||
JOIN exchange_short_name sn ON u.id = sn.exchange_id AND sn.deleted = FALSE
|
||
JOIN exchange_code c ON u.id = c.exchange_id AND c.deleted = FALSE
|
||
WHERE u.deleted = FALSE;
|
||
|
||
RAISE NOTICE '4️⃣✅ exchange_info_view 已创建/更新';
|
||
EXCEPTION
|
||
WHEN OTHERS THEN
|
||
RAISE NOTICE '4️⃣❌ 处理视图时发生错误: %', SQLERRM;
|
||
END $$;
|
||
|
||
-- 5️⃣ 性能索引 ------------------------------------------------
|
||
CREATE INDEX IF NOT EXISTS idx_exchange_name_exchange_id_deleted ON exchange_name(exchange_id, deleted);
|
||
CREATE INDEX IF NOT EXISTS idx_exchange_short_name_exchange_id_deleted ON exchange_short_name(exchange_id, deleted);
|
||
CREATE INDEX IF NOT EXISTS idx_exchange_code_exchange_id_deleted ON exchange_code(exchange_id, deleted);
|
||
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '5️⃣✅ 全部索引已确保存在';
|
||
END $$;
|
||
|
||
-- 6️⃣ 完成 🎉
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '🎉============ exchange 部署完成 ============🎉';
|
||
END $$; |