3 ms·

Yes, and I use it to do things like "starting from this point in the tree, recursively return all of its children". Usually I ask for `parent_id`, other times I

Yes, and I use it to do things like "starting from this point in the tree, recursively return all of its children". Usually I ask for `parent_id`, other times I ask PostgreSQL to accumulate an array describing the path the database took to get from here to there. In no case have I found `WITH RECURSIVE` to be insufficiently powerful for working with trees.

ruslan_talpa 10y ago
I don't think you understand what i am referring to when i say "returning data". How do you return this http://pastebin.com/9cqzQETj http://pastebin.com/9cqzQETj information from the database, in a single query, without duplicate data. consider there are 3 tables clients/projects/tasks with FKs between them. Please paste the query that returns all the information above using "with recursive" This is how the communication between the client an PG goes https://www.pgcon.org/2014/schedule/attachments/330_postgres-for-the-wire.pdf https://www.pgcon.org/2014/schedule/attachments/330_postgres... - client sends an SQL command - server replies with RowDescription detailing the structure - after that a DataRow message is sent for every row - finally, the server sends CommandComplete and ReadyForQuery So inherently the protocol can only represent a 2D array.
- jklehm 10y ago
  You’d use these [0] functions to build a json string and return your structure, one row per root of each hierarchy (you might only return one row if you’re after a single hierarchical json object). [0] https://www.postgresql.org/docs/9.5/static/functions-json.html https://www.postgresql.org/docs/9.5/static/functions-json.ht...
  - ruslan_talpa 10y ago
    Please read the whole thread, this is the exact point i was making, json is the only way to return tree like data. willglynn was saying you can do it with "with recursive" and i think he was confusing "traversing/inspecting" tree like data with "returning" tree like data.

ruslan_talpa 10y ago

I don't think you understand what i am referring to when i say "returning data". How do you return this http://pastebin.com/9cqzQETj http://pastebin.com/9cqzQETj information from the database, in a single query, without duplicate data. consider there are 3 tables clients/projects/tasks with FKs between them. Please paste the query that returns all the information above using "with recursive" This is how the communication between the client an PG goes https://www.pgcon.org/2014/schedule/attachments/330_postgres-for-the-wire.pdf https://www.pgcon.org/2014/schedule/attachments/330_postgres... - client sends an SQL command - server replies with RowDescription detailing the structure - after that a DataRow message is sent for every row - finally, the server sends CommandComplete and ReadyForQuery So inherently the protocol can only represent a 2D array.

jklehm 10y ago
You’d use these [0] functions to build a json string and return your structure, one row per root of each hierarchy (you might only return one row if you’re after a single hierarchical json object). [0] https://www.postgresql.org/docs/9.5/static/functions-json.html https://www.postgresql.org/docs/9.5/static/functions-json.ht...
- ruslan_talpa 10y ago
  Please read the whole thread, this is the exact point i was making, json is the only way to return tree like data. willglynn was saying you can do it with "with recursive" and i think he was confusing "traversing/inspecting" tree like data with "returning" tree like data.