223 lines
8.2 KiB
PL/PgSQL
223 lines
8.2 KiB
PL/PgSQL
-- ===================================================================
|
||
-- 基于现有 ak_global_config 表的优化查询函数
|
||
-- 保持现有表结构，添加便捷的多语言查询支持
|
||
-- ===================================================================
|
||
|
||
-- 1. 为现有表添加分类字段（可选）
|
||
-- ALTER TABLE public.ak_global_config ADD COLUMN IF NOT EXISTS config_category VARCHAR(50) DEFAULT 'general';
|
||
-- CREATE INDEX IF NOT EXISTS idx_ak_global_config_category ON public.ak_global_config(config_category);
|
||
|
||
-- 2. 创建便捷的多语言配置查询函数
|
||
CREATE OR REPLACE FUNCTION get_global_config_by_language(
|
||
p_language_code VARCHAR(10) DEFAULT 'zh'
|
||
) RETURNS TABLE (
|
||
config_key VARCHAR(100),
|
||
config_value TEXT,
|
||
config_type VARCHAR(50),
|
||
description TEXT,
|
||
base_key VARCHAR(100)
|
||
) AS $$
|
||
DECLARE
|
||
lang_suffix TEXT;
|
||
BEGIN
|
||
-- 根据语言代码确定后缀
|
||
CASE p_language_code
|
||
WHEN 'zh' THEN lang_suffix := '_zh';
|
||
WHEN 'en' THEN lang_suffix := '_en';
|
||
WHEN 'tw' THEN lang_suffix := '_tw';
|
||
WHEN 'ja' THEN lang_suffix := '_ja';
|
||
ELSE lang_suffix := '_zh'; -- 默认中文
|
||
END CASE;
|
||
|
||
RETURN QUERY
|
||
SELECT
|
||
gc.config_key,
|
||
gc.config_value,
|
||
gc.config_type,
|
||
gc.description,
|
||
-- 提取基础key（去掉语言后缀）
|
||
CASE
|
||
WHEN gc.config_key LIKE '%_zh' THEN LEFT(gc.config_key, LENGTH(gc.config_key) - 3)
|
||
WHEN gc.config_key LIKE '%_en' THEN LEFT(gc.config_key, LENGTH(gc.config_key) - 3)
|
||
WHEN gc.config_key LIKE '%_tw' THEN LEFT(gc.config_key, LENGTH(gc.config_key) - 3)
|
||
WHEN gc.config_key LIKE '%_ja' THEN LEFT(gc.config_key, LENGTH(gc.config_key) - 3)
|
||
ELSE gc.config_key
|
||
END AS base_key
|
||
FROM public.ak_global_config gc
|
||
WHERE gc.is_active = true
|
||
AND (
|
||
gc.config_key LIKE '%' || lang_suffix
|
||
OR gc.config_key NOT LIKE '%_zh'
|
||
AND gc.config_key NOT LIKE '%_en'
|
||
AND gc.config_key NOT LIKE '%_tw'
|
||
AND gc.config_key NOT LIKE '%_ja'
|
||
)
|
||
ORDER BY gc.config_key;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 3. 获取单个配置值的函数
|
||
CREATE OR REPLACE FUNCTION get_global_config_value(
|
||
p_base_key VARCHAR(100),
|
||
p_language_code VARCHAR(10) DEFAULT 'zh'
|
||
) RETURNS TEXT AS $$
|
||
DECLARE
|
||
lang_suffix TEXT;
|
||
config_value TEXT;
|
||
BEGIN
|
||
-- 根据语言代码确定后缀
|
||
CASE p_language_code
|
||
WHEN 'zh' THEN lang_suffix := '_zh';
|
||
WHEN 'en' THEN lang_suffix := '_en';
|
||
WHEN 'tw' THEN lang_suffix := '_tw';
|
||
WHEN 'ja' THEN lang_suffix := '_ja';
|
||
ELSE lang_suffix := '_zh';
|
||
END CASE;
|
||
|
||
-- 先尝试获取带语言后缀的配置
|
||
SELECT gc.config_value INTO config_value
|
||
FROM public.ak_global_config gc
|
||
WHERE gc.config_key = p_base_key || lang_suffix
|
||
AND gc.is_active = true;
|
||
|
||
-- 如果没找到，尝试获取不带后缀的配置（通用配置）
|
||
IF config_value IS NULL THEN
|
||
SELECT gc.config_value INTO config_value
|
||
FROM public.ak_global_config gc
|
||
WHERE gc.config_key = p_base_key
|
||
AND gc.is_active = true;
|
||
END IF;
|
||
|
||
-- 如果还是没找到，尝试获取中文版本作为默认
|
||
IF config_value IS NULL AND lang_suffix != '_zh' THEN
|
||
SELECT gc.config_value INTO config_value
|
||
FROM public.ak_global_config gc
|
||
WHERE gc.config_key = p_base_key || '_zh'
|
||
AND gc.is_active = true;
|
||
END IF;
|
||
|
||
RETURN config_value;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 4. 获取公司信息的便捷函数
|
||
CREATE OR REPLACE FUNCTION get_company_config(
|
||
p_language_code VARCHAR(10) DEFAULT 'zh'
|
||
) RETURNS TABLE (
|
||
company_name TEXT,
|
||
company_slogan TEXT,
|
||
company_address TEXT,
|
||
company_phone TEXT,
|
||
company_email TEXT,
|
||
company_icp TEXT,
|
||
company_logo_url TEXT
|
||
) AS $$
|
||
BEGIN
|
||
RETURN QUERY
|
||
SELECT
|
||
get_global_config_value('company_name', p_language_code),
|
||
get_global_config_value('company_slogan', p_language_code),
|
||
get_global_config_value('company_address', p_language_code),
|
||
get_global_config_value('company_phone', p_language_code),
|
||
get_global_config_value('company_email', p_language_code),
|
||
get_global_config_value('company_icp', p_language_code),
|
||
get_global_config_value('company_logo_url', p_language_code);
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 5. 获取热门搜索关键词的函数
|
||
CREATE OR REPLACE FUNCTION get_hot_search_keywords(
|
||
p_language_code VARCHAR(10) DEFAULT 'zh'
|
||
) RETURNS TABLE (
|
||
search_term TEXT,
|
||
sort_order INTEGER
|
||
) AS $$
|
||
BEGIN
|
||
RETURN QUERY
|
||
SELECT
|
||
get_global_config_value('hot_search_' || generate_series, p_language_code) AS search_term,
|
||
generate_series AS sort_order
|
||
FROM generate_series(1, 5)
|
||
WHERE get_global_config_value('hot_search_' || generate_series, p_language_code) IS NOT NULL;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 6. 获取社交媒体链接的函数
|
||
CREATE OR REPLACE FUNCTION get_social_media_config(
|
||
p_language_code VARCHAR(10) DEFAULT 'zh'
|
||
) RETURNS TABLE (
|
||
wechat_url TEXT,
|
||
weibo_url TEXT,
|
||
qq_url TEXT,
|
||
linkedin_url TEXT,
|
||
twitter_url TEXT
|
||
) AS $$
|
||
BEGIN
|
||
RETURN QUERY
|
||
SELECT
|
||
get_global_config_value('social_wechat_url', p_language_code),
|
||
get_global_config_value('social_weibo_url', p_language_code),
|
||
get_global_config_value('social_qq_url', p_language_code),
|
||
get_global_config_value('social_linkedin_url', p_language_code),
|
||
get_global_config_value('social_twitter_url', p_language_code);
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 7. 创建优化的配置视图
|
||
CREATE OR REPLACE VIEW public.vw_global_config_by_language AS
|
||
SELECT
|
||
gc.config_key,
|
||
gc.config_value,
|
||
gc.config_type,
|
||
gc.description,
|
||
gc.is_active,
|
||
-- 提取语言代码
|
||
CASE
|
||
WHEN gc.config_key LIKE '%_zh' THEN 'zh'
|
||
WHEN gc.config_key LIKE '%_en' THEN 'en'
|
||
WHEN gc.config_key LIKE '%_tw' THEN 'tw'
|
||
WHEN gc.config_key LIKE '%_ja' THEN 'ja'
|
||
ELSE 'common' -- 无语言后缀的通用配置
|
||
END AS language_code,
|
||
-- 提取基础配置键
|
||
CASE
|
||
WHEN gc.config_key LIKE '%_zh' THEN LEFT(gc.config_key, LENGTH(gc.config_key) - 3)
|
||
WHEN gc.config_key LIKE '%_en' THEN LEFT(gc.config_key, LENGTH(gc.config_key) - 3)
|
||
WHEN gc.config_key LIKE '%_tw' THEN LEFT(gc.config_key, LENGTH(gc.config_key) - 3)
|
||
WHEN gc.config_key LIKE '%_ja' THEN LEFT(gc.config_key, LENGTH(gc.config_key) - 3)
|
||
ELSE gc.config_key
|
||
END AS base_key,
|
||
-- 配置分类（基于键名推断）
|
||
CASE
|
||
WHEN gc.config_key LIKE 'company_%' THEN 'company'
|
||
WHEN gc.config_key LIKE 'social_%' THEN 'social'
|
||
WHEN gc.config_key LIKE 'hot_search_%' THEN 'search'
|
||
ELSE 'general'
|
||
END AS config_category
|
||
FROM public.ak_global_config gc
|
||
WHERE gc.is_active = true;
|
||
|
||
-- 8. 添加函数注释
|
||
COMMENT ON FUNCTION get_global_config_by_language IS '获取指定语言的全局配置（基于现有表结构）';
|
||
COMMENT ON FUNCTION get_global_config_value IS '获取单个配置值，支持语言回退';
|
||
COMMENT ON FUNCTION get_company_config IS '获取公司信息配置';
|
||
COMMENT ON FUNCTION get_hot_search_keywords IS '获取热门搜索关键词';
|
||
COMMENT ON FUNCTION get_social_media_config IS '获取社交媒体配置';
|
||
COMMENT ON VIEW vw_global_config_by_language IS '按语言分组的全局配置视图';
|
||
|
||
-- 9. 测试查询示例
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '========================================';
|
||
RAISE NOTICE '基于现有表结构的优化查询函数已创建！';
|
||
RAISE NOTICE '========================================';
|
||
RAISE NOTICE '使用示例：';
|
||
RAISE NOTICE '-- 获取中文配置：SELECT * FROM get_global_config_by_language(''zh'');';
|
||
RAISE NOTICE '-- 获取公司名称：SELECT get_global_config_value(''company_name'', ''zh'');';
|
||
RAISE NOTICE '-- 获取公司信息：SELECT * FROM get_company_config(''zh'');';
|
||
RAISE NOTICE '-- 获取热门搜索：SELECT * FROM get_hot_search_keywords(''zh'');';
|
||
RAISE NOTICE '-- 查看配置视图：SELECT * FROM vw_global_config_by_language WHERE language_code = ''zh'';';
|
||
RAISE NOTICE '========================================';
|
||
END
|
||
$$;
|