ANTON KADACH

Generation

text to sql syntaxWed, 18 Jan 2023

Таблицы geo_objects(id, parent_id) geo_object_attributes(geo_objects_id, name) запрос, который рекурсивно находит все гео-объекты по дереву и выводит наименование всех гео-объектов дерева в одну строку postgresql

WITH RECURSIVE geo_objects_tree (id, parent_id, name) AS ( SELECT id, parent_id, name FROM geo_objects UNION SELECT geo_objects.id, geo_objects.parent_id, geo_object_attributes.name FROM geo_objects_tree JOIN geo_objects ON geo_objects.id = geo_objects_tree.parent_id JOIN geo_object_attributes ON geo_object_attributes.geo_objects_id = geo_objects.id ) SELECT string_agg(name, ', ') FROM geo_objects_tree WHERE parent_id IS NULL;

Questions about programming?Chat with your personal AI assistant