动态列头创建

CREATE OR REPLACE FUNCTION create_adaptive_view()
RETURNS void AS
’
DECLARE
max_level INT;
sql_text TEXT;
level_sql TEXT := ‘’’’;
l INT;
BEGIN
– 获取最大 level
SELECT MAX(level) INTO max_level FROM data;

-- 单层情况
IF max_level = 1 THEN
    SELECT
        ''CREATE OR REPLACE VIEW v_data_dynamic_merged AS SELECT id, code, level, '' ||
        string_agg(
            format(
                ''attribute%s AS "'' || replace(name, ''"'', '''') || ''"'',
                seq
            ),
            '', ''
            ORDER BY seq
        ) ||
        '' FROM data WHERE level = 1;''
    INTO sql_text
    FROM extend
    WHERE seq < 200;

    EXECUTE sql_text;
    RAISE NOTICE ''视图 v_data_dynamic_merged（单层）已创建'';

ELSE
    -- 多层情况，构造多层子查询并LEFT JOIN
    FOR l IN 1..max_level LOOP
        -- 拼接该层字段
        level_sql := level_sql || format(
            CASE WHEN l = 1 THEN
                ''(SELECT code, %s FROM data WHERE level = %s) t%s''
            ELSE
                ''LEFT JOIN (SELECT code, %s FROM data WHERE level = %s) t%s ON t1.code = t%s.code''
            END,
            (
                SELECT string_agg(
                    format(
                        ''attribute%s AS "'' || replace(name, ''"'', '''') || ''_level%s"'',
                        seq, l
                    ),
                    '', ''
                    ORDER BY seq
                )
                FROM extend
                WHERE seq >= (l - 1) * 200 AND seq < l * 200
            ),
            l, l,  -- level, alias
            l, l   -- for JOIN ON
        );
        IF l < max_level THEN
            level_sql := level_sql || ' ';
        END IF;
    END LOOP;

    -- 拼接最终SQL
    sql_text := 'CREATE OR REPLACE VIEW v_data_dynamic_merged AS SELECT t1.code';

    -- 拼接所有字段列名
    FOR l IN 1..max_level LOOP
        sql_text := sql_text || ', ' || (
            SELECT string_agg(
                '"' || replace(name, '"', '') || format('_level%s"', l),
                ', '
                ORDER BY seq
            )
            FROM extend
            WHERE seq >= (l - 1) * 200 AND seq < l * 200
        );
    END LOOP;

    sql_text := sql_text || ' FROM ' || level_sql || ';';

    EXECUTE sql_text;
    RAISE NOTICE '视图 v_data_dynamic_merged（多层）已创建，共 % 层', max_level;
END IF;

END;
’ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION create_dynamic_view()
RETURNS void AS
$$
DECLARE
max_level INT;
total_attributes INT;
sql_text TEXT;
BEGIN
SELECT COALESCE(MAX(level), 1) INTO max_level FROM data;
total_attributes := max_level * 200;

SELECT
    'CREATE OR REPLACE VIEW v_data_dynamic AS SELECT id, level, ' ||
    string_agg(
        'attribute' || seq || ' AS "' || replace(name, '"', '') || '"',
        ', ' ORDER BY seq
    ) ||
    ' FROM data;'
INTO sql_text
FROM extend
WHERE seq < total_attributes;

EXECUTE sql_text;

RAISE NOTICE 'View v_data_dynamic created with % attributes (max level = %)', total_attributes, max_level;

END;
$$ LANGUAGE plpgsql;