— 1. 生成表结构文档
SELECT
c.table_name,
c.column_name,
c.data_type ||
CASE
WHEN c.character_maximum_length IS NOT NULL
THEN ‘(‘ || c.character_maximum_length || ‘)’
WHEN c.numeric_precision IS NOT NULL
THEN ‘(‘ || c.numeric_precision || ‘,’ || COALESCE(c.numeric_scale, 0) || ‘)’
ELSE ”
END as full_data_type,
CASE WHEN c.is_nullable = ‘NO’ THEN ‘NOT NULL’ ELSE ” END as nullable,
COALESCE(c.column_default, ”) as default_value,
COALESCE(
(SELECT ‘PK’ FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = ‘PRIMARY KEY’
AND kcu.table_name = c.table_name
AND kcu.column_name = c.column_name
LIMIT 1),
”
) as is_primary_key,
COALESCE(
(SELECT ‘FK’ FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = ‘FOREIGN KEY’
AND kcu.table_name = c.table_name
AND kcu.column_name = c.column_name
LIMIT 1),
”
) as is_foreign_key
FROM information_schema.columns c
WHERE c.table_schema = ‘public’
ORDER BY c.table_name, c.ordinal_position;

— 2. 查询外键关系图
SELECT
tc.table_name as from_table,
kcu.column_name as from_column,
ccu.table_name as to_table,
ccu.column_name as to_column,
tc.constraint_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = ‘FOREIGN KEY’
AND tc.table_schema = ‘public’
ORDER BY tc.table_name;

— 3. 查询数据库对象统计
SELECT
‘Tables’ as object_type,
COUNT(*) as object_count
FROM information_schema.tables
WHERE table_schema = ‘public’
AND table_type = ‘BASE TABLE’
UNION ALL
SELECT
‘Views’,
COUNT(*)
FROM information_schema.tables
WHERE table_schema = ‘public’
AND table_type = ‘VIEW’
UNION ALL
SELECT
‘Indexes’,
COUNT(*)
FROM pg_indexes
WHERE schemaname = ‘public’
UNION ALL
SELECT
‘Functions’,
COUNT(*)
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = ‘public’;

— 1. 创建动态查询函数
CREATE OR REPLACE FUNCTION get_table_columns(p_table_name text)
RETURNS TABLE (
column_name text,
data_type text,
is_nullable text
) AS $$
BEGIN
RETURN QUERY
SELECT
c.column_name::text,
c.data_type::text,
c.is_nullable::text
FROM information_schema.columns c
WHERE c.table_name = p_table_name
AND c.table_schema = ‘public’
ORDER BY c.ordinal_position;
END;
$$ LANGUAGE plpgsql;

— 使用函数
SELECT * FROM get_table_columns(‘fgedu_employees’);

— 2. 创建通用元数据查询函数
CREATE OR REPLACE FUNCTION get_object_metadata(p_object_type text)
RETURNS TABLE (
object_name text,
object_size text,
object_owner text
) AS $$
BEGIN
IF p_object_type = ‘table’ THEN
RETURN QUERY
SELECT
c.relname::text,
pg_size_pretty(pg_total_relation_size(c.oid))::text,
pg_get_fgedubyid(c.relowner)::text
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = ‘r’ AND n.nspname = ‘public’;
ELSIF p_object_type = ‘index’ THEN
RETURN QUERY
SELECT
indexname::text,
pg_size_pretty(pg_relation_size(indexrelid))::text,
tableowner::text
FROM pg_indexes pi
JOIN pg_stat_fgedu_indexes psi ON pi.indexname = psi.indexrelname
WHERE pi.schemaname = ‘public’;
END IF;
END;
$$ LANGUAGE plpgsql;

— 使用函数
SELECT * FROM get_object_metadata(‘table’);

— 3. 动态生成SQL
CREATE OR REPLACE FUNCTION generate_select_sql(p_table_name text)
RETURNS text AS $$
DECLARE
v_sql text;
v_columns text;
BEGIN
SELECT STRING_AGG(column_name, ‘, ‘)
INTO v_columns
FROM information_schema.columns
WHERE table_name = p_table_name
AND table_schema = ‘public’;

v_sql := ‘SELECT ‘ || v_columns || ‘ FROM ‘ || p_table_name || ‘;’;
RETURN v_sql;
END;
$$ LANGUAGE plpgsql;

— 使用函数
SELECT generate_select_sql(‘fgedu_employees’);

— 1. 使用缓存
CREATE TABLE fgedu_metadata_cache (
cache_key text PRIMARY KEY,
cache_value jsonb,
cached_at timestamp DEFAULT CURRENT_TIMESTAMP
);

— 2. 创建缓存刷新函数
CREATE OR REPLACE FUNCTION refresh_table_metadata_cache()
RETURNS void AS $$
BEGIN
INSERT INTO fgedu_metadata_cache (cache_key, cache_value)
SELECT ‘tables’, jsonb_agg(jsonb_build_object(
‘table_name’, table_name,
‘table_type’, table_type,
‘column_count’, (SELECT COUNT(*) FROM information_schema.columns c
WHERE c.table_name = t.table_name)
))
FROM information_schema.tables t
WHERE table_schema = ‘public’
ON CONFLICT (cache_key) DO UPDATE SET
cache_value = EXCLUDED.cache_value,
cached_at = CURRENT_TIMESTAMP;
END;
$$ LANGUAGE plpgsql;

— 3. 使用物化视图
CREATE MATERIALIZED VIEW fgedu_table_metadata AS
SELECT
t.table_name,
t.table_type,
COUNT(c.column_name) as column_count,
COUNT(CASE WHEN c.is_nullable = ‘NO’ THEN 1 END) as not_null_count
FROM information_schema.tables t
LEFT JOIN information_schema.columns c
ON t.table_name = c.table_name AND t.table_schema = c.table_schema
WHERE t.table_schema = ‘public’
GROUP BY t.table_name, t.table_type;

— 刷新物化视图
REFRESH MATERIALIZED VIEW fgedu_table_metadata;

— 4. 创建索引
CREATE INDEX idx_fgedu_table_metadata_name ON fgedu_table_metadata(table_name);