Get hierarchical XML structure by query from table in PostgreSQL?

Question

PostgreSQL

In the table, relatively speaking, a tree is stored in the table: there is a record identifier id and a link to the parent record pid (at the root IS NULL), well, and some other data (the name field, for example).
As a result of the request, I need to pull out the entire hierarchical structure in the form of XML with one value. Actually, I do it. The question is, is there any possibility to implement such functionality simply? Say, using recursive queries?
Usually, in a recursive query, you can use information from the parent record when processing child records. And here it is necessary, as it were, on the contrary - to aggregate the result of processing child records when processing the parent one, and at the output we should get something like:

<node name="корень"><node name="узел-1"><node name="узел-1-1"></node>…</node><node name="узел-2"></node>…</node>

Is it possible?

Answer 1

I'm not sure if this is what you need, but maybe you yourself will figure out how to apply it :)
www.postgresql.org/docs/current/static/ltree.html

Answer 2

Well, yes, if you need to pull out the entire structure, and not just some branch, then it's easier to do select * from
and then organize the tree using the language.
A query to the database is still heavier than operations with local data

Answer 3

Another good article about storing trees in Postgres