14 ms·

It's not really the only way (PostgreSQL has recursive queries), but it's definitely the most convenient (and in many cases the most efficient) way if you're no

It's not really the only way (PostgreSQL has recursive queries), but it's definitely the most convenient (and in many cases the most efficient) way if you're not worried about referential integrity.

ruslan_talpa 10y ago
Care to explain how you would extract in a single query tree like data (without duplicate data going over the wire)?
- willglynn 10y ago
  I use `WITH RECURSIVE` to traverse trees: https://www.postgresql.org/docs/current/static/queries-with.html https://www.postgresql.org/docs/current/static/queries-with....
  - ruslan_talpa 10y ago
    it's not about traversing trees, it's about "returning" trees, there is a difference :)
    - willglynn 10y ago
      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.
    - deleted 10y ago
      [deleted]

It's not really the only way (PostgreSQL has recursive queries), but it's definitely the most convenient (and in many cases the most efficient) way if you're not worried about referential integrity.

ruslan_talpa 10y ago

Care to explain how you would extract in a single query tree like data (without duplicate data going over the wire)?

willglynn 10y ago
I use `WITH RECURSIVE` to traverse trees: https://www.postgresql.org/docs/current/static/queries-with.html https://www.postgresql.org/docs/current/static/queries-with....
- ruslan_talpa 10y ago
  it's not about traversing trees, it's about "returning" trees, there is a difference :)
  - willglynn 10y ago
    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.
  - deleted 10y ago
    [deleted]