aggreg

gistfile1.txt

CREATE OR REPLACE VIEW kkkdb._overview_links_and_url_parts("Link Id", "Link Key", "Comment", "Dynamic URL") AS
SELECT 
    link.id AS "Link Id",
    link.link_key AS "Link Key",
    link.comment AS "Comment",
    string_agg(concat('[', url_part.id, '] ', link_2_url_rel."order", ' - ', '[', part_type.id, ' ', part_type.source, ' -> ', part_type.purpose, ']', ' ', url_part.source_name, ' -> ', url_part.target_name, ' = ', url_part.value, ' ', value_converters_agg, '[required = ', link_2_url_rel.required, ']'), chr(10) ORDER BY link_2_url_rel."order") AS "Dynamic URL"
FROM 
    kkkdb.kkk_t_link link
    JOIN kkkdb.kkk_t_link_url_part_relation link_2_url_rel ON link.id = link_2_url_rel.link_id
    JOIN kkkdb.kkk_t_url_part url_part ON url_part.id = link_2_url_rel.url_part_id
    LEFT JOIN kkkdb.kkk_t_value_accumulator value_accumulator ON url_part.value_accumulator_id = value_accumulator.id
    LEFT JOIN (
        SELECT 
            url_part_id, 
            string_agg(concat('[', value_converter.name, '][', value_converter.type, ']'), ', ') AS value_converters_agg
        FROM 
            kkkdb.kkk_t_url_part_value_converter_relation url_2_value_converter_rel
            JOIN kkkdb.kkk_t_value_converter value_converter ON value_converter.id = url_2_value_converter_rel.value_converter_id
        GROUP BY 
            url_part_id
    ) AS value_converters ON value_converters.url_part_id = url_part.id
    LEFT JOIN kkkdb.kkk_t_part_type part_type ON part_type.id = url_part.part_type_id
GROUP BY 
    link.id;

ALTER TABLE kkkdb._overview_links_and_url_parts
OWNER TO postgres;