BUG #17061: Impossible to query the fields of the tuple created by SEARCH BREADTH FIRST BY .. SET ..
От | PG Bug reporting form |
---|---|
Тема | BUG #17061: Impossible to query the fields of the tuple created by SEARCH BREADTH FIRST BY .. SET .. |
Дата | |
Msg-id | 17061-dd7f4825b7da3a9d@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #17061: Impossible to query the fields of the tuple created by SEARCH BREADTH FIRST BY .. SET ..
Re: BUG #17061: Impossible to query the fields of the tuple created by SEARCH BREADTH FIRST BY .. SET .. |
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 17061 Logged by: Benoit Lobréau Email address: blo.talkto@gmail.com PostgreSQL version: 14beta1 Operating system: Fedora 33 Description: Hi, I try to access the field inside the tuple generated by SEARCH BREADTH FIRST. A use case would be to get all the records with depth 3. I fail to access it and I don't understand the error. I wonder if it works as intended ? Here is a test case : DROP TABLE tree; CREATE TABLE tree(id int, parent_id int, name text); ALTER TABLE tree ADD PRIMARY KEY (id); INSERT INTO tree(id, parent_id, name) VALUES (1, NULL, 'Albert'), (2, 1, 'Bob'), (3, 1, 'Barbara'), (4, 1, 'Britney'), (5, 3, 'Clara'), (6, 3, 'Clement'), (7, 2, 'Craig'), (8, 5, 'Debby'), (9, 5, 'Dave'), (10, 9, 'Edwin'); -- The following query shows that the fields of the tuple are named: -- -- row_to_json -- ----------------------- -- {"*DEPTH*":0,"id":1} -- {"*DEPTH*":1,"id":2} -- {"*DEPTH*":1,"id":3} -- {"*DEPTH*":1,"id":4} -- {"*DEPTH*":2,"id":5} -- {"*DEPTH*":2,"id":6} -- {"*DEPTH*":2,"id":7} -- {"*DEPTH*":3,"id":8} -- {"*DEPTH*":3,"id":9} -- {"*DEPTH*":4,"id":10} -- (10 rows) WITH RECURSIVE mtree(id, name) AS ( SELECT id, name FROM tree WHERE id = 1 UNION ALL SELECT t.id, t.name FROM tree AS t INNER JOIN mtree AS m ON t.parent_id = m.id ) SEARCH BREADTH FIRST BY id SET breadth SELECT row_to_json(breadth) FROM mtree m; -- So I try to get the "*DEPTH*" column but it fails with (I get the same error with (breadth).id) : -- -- ERROR: CTE m does not have attribute 3 WITH RECURSIVE mtree(id, name) AS ( SELECT id, name FROM tree WHERE id = 1 UNION ALL SELECT t.id, t.name FROM tree AS t INNER JOIN mtree AS m ON t.parent_id = m.id ) SEARCH BREADTH FIRST BY id SET breadth SELECT (breadth)."*DEPTH*" FROM mtree m; -- The following works but feels a little hacky WITH RECURSIVE mtree(id, name) AS ( SELECT id, name FROM tree WHERE id = 1 UNION ALL SELECT t.id, t.name FROM tree AS t INNER JOIN mtree AS m ON t.parent_id = m.id ) SEARCH BREADTH FIRST BY id SET breadth SELECT row_to_json(breadth) -> '*DEPTH*' FROM mtree m;
В списке pgsql-bugs по дате отправления: